How to Set table creation fields in mysql

Source: Internet
Author: User
I want to create a skill table with the id field and skill the other with the id, sid, in info, sid is used to link the field sid in two tables. The problem is that each user may not have a skill. each user's sid may have several numbers. How can I read each table... I want to create a skill table with the field id and skill
The other is the id and sid Fields in the human information table. In info, the sid is the field sid connecting two tables = the id in the skill table.
The problem is that not everyone has a skill. Each person's sid may have several numbers. How can I read the skill information of each person?

Reply content:

I want to create a skill table with the field id and skill
The other is the id and sid Fields in the human information table. In info, the sid is the field sid connecting two tables = the id in the skill table.
The problem is that not everyone has a skill. Each person's sid may have several numbers. How can I read the skill information of each person?

Skill Table k and Character Table p
Select ta. *, tb. skill from p as ta left join k as tb on ta. sid = tb. id;

You still have fewer tables.

We recommend that you create multiple Relational Tables, such as relation.
The field can be set to sid and user ID, which is assumed to be uid.
So

select uid from relation where sid='xxx' 

A person with a certain skill

select sid from relation where uid='xxx'

Skills for a person
Of course, you must read the appropriate table information.

Add a relational table and remove the sid from the user table.

This is a one-to-many problem.

Human table (t_user)
Id, username

Human skill Association Table (user_skill)
Id user_id skill_id

Skill table (skill)
Id, skill

SELECT
T_user.id,
T_user.username,
Skill. id,
Skill. skill
FROM
T_user
Left join user_skill ON t_user.id = user_skill.user_id
Left join skill ON skill. id = user_skill.skill_id

Where t_user.id = '1' -- skills list with ID 1 found

User info table: info
User skill table: jineng

Idea: Use PHP to first retrieve the sid of a user, then use the string to cut into an array to get the sid corresponding to each skill of the Multi-skill user, and finally splice the SQL statement.

Requirements: Separate multiple sid IDs in the info table with the specified symbols, for example, 1; 2; 3; 4.

PHP :( split the sid with semicolons)

Function user_skill ($ id ){

$sid=mysql_fetch_assoc(mysql_query("select * from info where id=$id"))['sid'];

$ Arr = explode (';', $ sid );
$ SQL = "select skill from jineng where id in (";
Foreach ($ arr as $ v)
{

$sql.=$v.',';

}
$ SQL = substr ($ SQL, 0, strlen ($ SQL)-1 );
$ Query = mysql_query ($ SQL );
// The while loop can output all the skills of all users whose id is $ id.
}

1. Skill table
2. User information table

One user can have multiple skills, but one skill can be desired by multiple users! Therefore, the relationship between the skill table and the user information table is many-to-many.
Since the relationship is many-to-many, an intermediate table (user skill table) is required)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.