PHP PDO Multiple Table join

Question

I have multiple tables and I want to access the fields in a specific table. Here are the table schemas:

Each tbl_acct has one tbl_unit. A tbl_unit has many tbl_groups and a tbl_groupcontact consist of the contacts and the related group. Every time I log in, I set a session where $_SESSION['unitid'] = $row['unitid'];.

What I wanted to do is to access the fields in the tbl_contacts. For now my code is here:

$data = $conn->prepare("SELECT * FROM tbl_groups LEFT JOIN tbl_groupcontact ON tbl_groups.id=tbl_groupcontact.group_id WHERE tbl_groups.unitid = ?");
$data->execute(array($_SESSION['unitid']));
foreach ($data as $row) {
    echo $row['fname'] . " " . $row['lname']. "<br />"; 
}

As you can see, I can related the tbl_groups and tbl_groupcontact in my code however, I can't get the fields in the tbl_contacts. Am I missing something here? Any help would be much appreciated.

shudder · Accepted Answer · 2015-10-06 04:47:23Z

4

You need to join another table.

SELECT tbl_contacts.*
FROM tbl_groups
INNER JOIN tbl_groupcontact ON tbl_groupcontact.group_id = tbl_groups.id 
INNER JOIN tbl_contacts ON tbl_contacts.id = tbl_groupcontact.contact_id
WHERE tbl_groups.unitid = ?

No need for (slower) LEFT JOIN btw - use it when you want to retrieve records from (left-side) table even when there's no match found in joined (right-side) table (it's columns will be filled with nulls in this case).

answered Oct 6, 2015 at 4:47

shudder

2,1012 gold badges20 silver badges21 bronze badges

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

1 Comment

wobsoriano Over a year ago

Works very well. Thank you for the tip!

Collectives™ on Stack Overflow

PHP PDO Multiple Table join

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related