Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 708
  • Last Modified:

PHP Join and filter from multiple tables

Hi Experts

Would appreciate some wise words, can't get my head around this one.

I have 3 tables:
students
groups
group_assignments

Basically the student table contains all the info, the groups table lists a simple list of available groups and the group_assignments table lists details of which student belongs to which group.

I am currently returning a complete list of students with no problem but I would like to be able to include the student group details in the recordset and filter on it.

Simple enough if each student only belongs to one group but most of them belong to many. What would be the best approach?
0
sirnutty1
Asked:
sirnutty1
  • 2
  • 2
1 Solution
 
aconradCommented:
below query will return a result set with 3 columns :
name,group_ids,group_name

And the data
A, "33,11" , "g3,g1"
B, "33,22,11" , "g3,g2,g1"
C, "33,22" , "g3,g2"

Then you can then use php's explode to get each student's group
I attached a mysql dump of the tables i used.

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
You need mysql5 to use group concat.

The mysql4 query would be without group_by and group_concat

SELECT
 s.name,
 group_ids,
 group_name
 
FROM students AS s
INNER JOIN group_assignments AS ga ON (s.student_id=ga.student_id)
INNER JOIN groups AS g ON (ga.group_id=g.group_id)

Then in the php code use array keys to add each group where it belongs
Something like this:


$out = array();
while ($row = mysql_fetch_assoc($result)) {
  $out[$row['name']][] = $row['group_name']
}
print_r($out);

best approach in my opinion is mysql5




SELECT 
 s.name,
 GROUP_CONCAT(ga.group_id) AS group_ids,
 GROUP_CONCAT(g.group_name) AS group_name
 
FROM students AS s 
INNER JOIN group_assignments AS ga ON (s.student_id=ga.student_id)
INNER JOIN groups AS g ON (ga.group_id=g.group_id)
 
GROUP BY ga.student_id

Open in new window

0
 
aconradCommented:
I cant upload .sql  files, you'll need to rename exp-export.txt to exp-export.sql ....
exp-export.txt
0
 
sirnutty1Author Commented:
Many thanks for your answer aconrad, works perfectly!!

Can you just clarify how I would then perform a filter on the group_concat columns?

Would I use a WHERE clause in the mySQL query or something else?

0
 
sirnutty1Author Commented:
Just figured out a simple addition to the where clause will do it!!

Many thanks again
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now