php mysql join 3 tables

Question

Im looping through a feedback type comment system on a users page, finding the latest 6 posts.

To display the post how I want, I have to grab data from 3 tables.

I figure it'd be less db intensive if it was all pulled in one query, but I'm not sure how to write it, being suck at JOINs and things.

This is my existing code

$sql_result = mysql_query("SELECT * FROM feedback WHERE user='1' ORDER BY date desc LIMIT 6", $db);
while($rs = mysql_fetch_array($sql_result)) {

   $sql_result2 = mysql_query("SELECT * FROM members WHERE id= '$rs[author]'", $db); 
   $rs2 = mysql_fetch_array($sql_result2);

   if ($rs2[clan] != 0) { 
      $sql_result3 = mysql_query("SELECT * FROM clans WHERE id= '$rs2[clan]' LIMIT 1", $db);
      $rs3 = mysql_fetch_array($sql_result3);

     // write comment

Can someone give me a clue?

Is there a relationship between the data in those tables? If so, what is it? — Dan J
– Dan J, Commented Nov 8, 2011 at 19:34
It's bad practice to have so many queries within so many loops. — Steve Robbins
– Steve Robbins, Commented Nov 8, 2011 at 19:40
Just a note, using unquoted array keys like $rs2[clan] is relying on PHP to misinterpret them as strings instead of constants. You should really reference those variables as {$rs['clan']}, quoted and enclosed in {} inside the double-quoted string. — Michael Berkowski
– Michael Berkowski, Commented Nov 8, 2011 at 19:43

anorakgirl · Accepted Answer · 2011-11-08 19:53:26Z

5

This should do it:

select * from feedback 
left join members on feedback.author = members.id 
left join clans on members.clan = clans.id
where feedback.user = 1

left join means if the table on the right has no matching row, a record with all null values is returned, so you still get the data from the table on the left.

edited Nov 8, 2011 at 19:53

answered Nov 8, 2011 at 19:39

anorakgirl

8061 gold badge9 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Morten Brendefur · Accepted Answer · 2011-11-08 20:04:12Z

I am no expert in Sql myself, but I have picked up a few tricks here and there :-)

A typical LEFT JOIN that works in Firebird is :

select A.*,B.*,C.*
from FEEDBACK A left join MEMBERS B
on A.USER = B.ID left join CLANS C
ON C.ID = A.USER
where A.USER=1

The logic behind the join is that All rows that now share the same value, A.USER = B.ID = C.ID will now be visible. The letters A B and C is just used for simplicity. F, M and C will work the same way.

This Left Join will pick out all and every column in tables. This is done with A.*,B.*,C.* Maybe you want only a few columns in each table. That can be accomplished by naming the columns in the same manner. Example:

A.USER,A.FIRSTNAME,A.SURNAME,B.COLNAME1,B.COLNAME2,C.COLNAME1,C.COLNAME2

When you need to adress the columns later, remember the Prefix of A. or B. or C. before the actual column-name you address.

Good luck and best regards. Morten, Norway

Collectives™ on Stack Overflow

php mysql join 3 tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related