CODEWITHSUNDEEP
mysqljoin
user1022585
user1022585

Reputation: 13661

php mysql join 3 tables

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?

Upvotes: 0

Views: 2683

Answers (2)

Morten Brendefur
Morten Brendefur

Reputation: 84

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

Upvotes: 1

anorakgirl
anorakgirl

Reputation: 788

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.

Upvotes: 5

Related Questions