Querying multiple tables, grouping results

tandjnl

I've a feeling the answer to this will be blindingly obvious, but hope for sympathy for a newbie to php and MySql.

I have a database to keep details of people, their skills, subjects, and media. I have four tables (members, skills, subjects, media) and three linking tables (members_skills, members_subjects, members_media). In the linking tables are just MemberID and the SkillID (or SubjectID or MediumID). Members can have multiple skills, subjects and media.

I want to display a list of members on a web page, and with each member their skills, subjects and media. It is this grouping that is defeating me.

I can do the select for, say, members and their media
$sql = 'SELECT members.FirstName, members.LastName, media.MediumName FROM members LEFT JOIN members_media USING (MemberID)' . ' LEFT JOIN media USING (MediumID)';

and display each name once with their media using a kind of loop

$result = mysql_query($sql,$db);
$lastLastName = '';
while (list ($FirstName, $LastName, $MediumName) = mysql_fetch_row($result))
{
if ($lastLastName !=$LastName){
$display_block .= " NAME $FirstName $LastName, MEDIA: ";
$firstMedium = 1;
}
if (!$firstMedium) $display_block .= ", ";
$display_block .= $MediumName;
$firstMedium =0;
$lastLastName = $LastName;
}

(and then start the html and <? echo "$display_block"; ?>)

But I get bogged down when trying to add their subjects and skills as well. Do I need to do multiple queries? A different kind of query? A different kind of loop? Nested loop? Any hints on the right approach would be much appreciated, I have a feeling I am coming at it the wrong way.

olaf

Hi. I use the left join a little different maybe its something to try:

SELECT table1.* FROM table1
LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;

I used this kind of statement with three tables together it should work...

tandjnl

Thanks. Need to look at that a bit closer to see if I understand how it might help, but all tips welcome.

tandjnl

I've managed to puzzle out something that works, it may not be a thing of beauty but I'm happy it works! Now I am beginning to understand it better, I hope I will be able to improve on it. In case anyone interested:

$sql = 'SELECT members.MemberID, members.FirstName, members.LastName FROM members';
$result = mysql_query($sql,$db);
$lastMemberID = '';
while (list ($MemberID, $FirstName, $LastName) = mysql_fetch_row($result))
{
$display_block .=" $FirstName $LastName";
$sql1 = "SELECT members.MemberID, media.MediumName FROM members LEFT JOIN members_media USING (MemberID) LEFT JOIN media USING (MediumID) WHERE members_media.MemberID=$MemberID";
$result1 = mysql_query($sql1,$db);
$var1 = '';
while (list ($meMemberID, $MediumName) = mysql_fetch_row($result1))
{
if ($var1 != $meMemberID)
{
$display_block .= " MEDIA ";
$firstMedium = 1;
}
if (!$firstMedium) $display_block .= ", ";
$display_block .= $MediumName;
$firstMedium =0;
$var1 = $meMemberID;
}
$sql2 = "SELECT members.MemberID, subjects.SubjectName FROM members LEFT JOIN members_subjects USING (MemberID) LEFT JOIN subjects USING (SubjectID) WHERE members_subjects.MemberID=$MemberID";
$result2 = mysql_query($sql2,$db);
$var2 = '';
while (list ($suMemberID, $SubjectName) = mysql_fetch_row($result2))
{
if ($var2 !=$suMemberID)
{
$display_block .= " SUBJECTS ";
$firstSubject = 1;
}
if (!$firstSubject) $display_block .= ", ";
$display_block .= $SubjectName;
$firstSubject =0;
$var2 = $suMemberID;
}
$sql3 = "SELECT members.MemberID, skills.SkillName FROM members LEFT JOIN members_skills USING (MemberID) LEFT JOIN skills USING (SkillID) WHERE members_skills.MemberID=$MemberID";
$result3 = mysql_query($sql3,$db);
$var3 = '';
while (list ($skMemberID, $SkillName) = mysql_fetch_row($result3))
{
if ($var3 !=$skMemberID)
{
$display_block .= " SKILLS ";
$firstSkill = 1;
}
if (!$firstSkill) $display_block .= ", ";
$display_block .= $SkillName;
$firstSkill =0;
$var3 = $skMemberID;

}
$lastMemberID = $MemberID;
}

james_lamie_net

You need to do two loops. You need to loop through the programmers displaying their data in the outer loop and loop through and display skills in the inner loop. I know because I have just recently worked through the exact same problem. The trick is to give your resource id's different names so that the outer loop works on the first query and the inner loop works on the second query.

Pseudocode:

select programmers
while(more programmers){
print programmer details

select programmer's skills //with different resource id
while(more skills){
print skills
}
}

Hope this helps.

tandjnl

Yes, I think that explains the logic behind what I have clumsily worked out by trial and error, it makes sense. Thanks!