Solved

PHP Join and filter from multiple tables

Posted on 2009-05-06
4
698 Views
Last Modified: 2012-05-06
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
Comment
Question by:sirnutty1
  • 2
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
aconrad earned 500 total points
ID: 24319520
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
 
LVL 4

Expert Comment

by:aconrad
ID: 24319552
I cant upload .sql  files, you'll need to rename exp-export.txt to exp-export.sql ....
exp-export.txt
0
 

Author Comment

by:sirnutty1
ID: 24326378
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
 

Author Comment

by:sirnutty1
ID: 24326817
Just figured out a simple addition to the where clause will do it!!

Many thanks again
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Access - need to reduce row size 25 52
Showing random records from database 10 36
Display images from mysql blob type (Not working) 9 26
PHP Syntax Error 4 25
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question