Best Way For The Following Scenario...

[Anon]

Using PHP and MySQL to display a person - and their skills from a many-to-many relationship.

Three Tables:

Person(PersonID,Name)
Skills(SkillID,SkillName)
Person_Skills(ID,PersonID,SkillID)

I've thought of two ways that might work.

1. Two separate queries, one for the person, and one for their skills, and displaying them separately"

"SELECT Name FROM Person WHERE PersionID=1"
"SELECT SkillName FROM Skills,Person_Skills WHERE Person_Skills.PersonID = 1 AND Person_Skills.SkillID = Skills.SkillID"

display firstQuery
display secondQuery

2. One query to get the name and the skills. From the first row of the result, display the name and skill, and just display the skill name from the rest of the result rows:

"SELECT Name, SkillName FROM Person,Skills,Person_Skills WHERE Person.PersonID = 1 AND Person_Skills.PersonID = 1 AND Person_Skills.SkillID = Skills.SkillID"

display Name and SkillName from first row of result
display only SkillName from the rest of the result

I'm fairly new to RDBMS design, and I want to get off on the right step, so which of these would be best, or is their even another better way?

Thanks.

[mikeq]

Hi,

I would probably go with the second example.

As an aside do you really need the field ID in the person_skills table, by its nature PersonID, SkillID make a primary key.

Mike