How to join three tables and add a "AND" operator?

Question

Being a Rookie i am a bit lost as to how to nail this. I am trying to join three tables. This is the table structure:

Accounts Table: 

1) id
2) User_id (Id given to user upon sign up)
3) Full_name 
4) Username
5) Email
6) Password

Contacts Table: 

1) My_id (This is the user who added friend_id)
2) Contact_id (this is the contact who was added by my_id)
3) Status (status of relationship)

Posts Table: 

1) Post_id
2) User_posting (this is the user who posted it)
3) Post_name
4) User_allowed (this is where its specified who can see it)

Here is the code structure i have:

<?php

$everybody = "everybody";
$sid = "user who is logged in.";

$sql = <<<SQL
SELECT DISTINCT contacts.contact_id, accounts.full_name, posts.post_id, posts.post_name
FROM contacts, accounts, posts
WHERE
    (contacts.my_id = '$sid'
      AND contacts.contact_id = accounts.user_id)
    OR (contacts.my_id = '$sid'
      AND contacts.contact_id = accounts.user_id)
    OR (posts.user_posting = '$sid'
      AND contacts.contact_id = accounts.user_id
      AND posts.user_allowed = '$everybody')
    OR (posts.user_id = '$sid'
    AND contacts.user_id = accounts.user_id
    AND posts.user_allowed = '$everybody')

LIMIT $startrow, 20;


$query = mysql_query($sql) or die ("Error: ".mysql_error());

$result = mysql_query($sql);

if ($result == "")
{
echo "";
}
echo "";


$rows = mysql_num_rows($result);

if($rows == 0)
{
print("");

}
elseif($rows > 0)
{
while($row = mysql_fetch_array($query))
{

$contactid = htmlspecialchars($row['contact_id']);
$name = htmlspecialchars($row['full_name']);
$postid = htmlspecialchars($row['post_id']);
$postname = htmlspecialchars($row['post_name']);

// If $dob is empty
if (empty($contactid)) {

$contactid = "You haven't added any friends yet.";
}


print("$contactid <br>$postname by $name <br />");
}

}

The problem is that the query shows me my posts plus all posts from friends.

What am i doing wrong?

You should be using proper `JOIN` syntax. What is your expected output? — Matthew, May 08 '12 at 16:28
why are checking same condition twice? `(contacts.my_id = '$sid' AND contacts.contact_id = accounts.user_id) OR (contacts.my_id = '$sid' AND contacts.contact_id = accounts.user_id)` — rs., May 08 '12 at 16:30
Since commenting was locked, I really suggest you go and read more about PHP and MySQL. You seem to lack some basic understanding of both. — Paul, May 09 '12 at 13:36
Thanks Paul. Yes i am 19 and sort of learning PHP as i am working on it. — ariel, May 09 '12 at 13:58

score 1 · Answer 1 · edited May 08 '12 at 16:48

1

Should be like this (Not thoroughly checked):

SELECT DISTINCT contacts.contact_id, 
                accounts.full_name,
                posts.post_id, 
                posts.post_name 
FROM contacts
INNER JOIN accounts ON accounts.id = contacts.id
INNER JOIN posts ON posts.User_posting = accounts.id /* (where'd get posts.user_id btw ) */
WHERE contacts.my_id = $sid
      AND posts.user_allowed = $everybody
LIMIT $startrow, 20

edited May 08 '12 at 16:48

Travesty3

14,351
6
61
98

answered May 08 '12 at 16:34

J A

1,776
1
12
13

Hey, i tried yours and it says Error: Unknown column 'accounts.id' in 'on clause' – ariel May 08 '12 at 18:41
Don't you have id field on accounts table ? Could you provid your db structure ? – J A May 08 '12 at 19:45
yes i do... but its beyond me why its not detecting it. the db structure is as what i posted above.. – ariel May 08 '12 at 19:46
Please add mysql table definition. – J A May 09 '12 at 12:46

Paul · Accepted Answer · 2012-05-09T12:39:43.400

Assuming you only want to show your friends' posts (and not your own):

$everybody = "everybody";
$sid = user who is logged in.  

$sql = "SELECT a.User_id, a.Full_name, p.Post_id, p.Post_name
FROM posts p
INNER JOIN accounts a ON a.User_id = p.User_posting
INNER JOIN contacts c ON c.My_id = '$sid' AND c.Contact_id = a.User_id
WHERE p.User_allowed = '$everybody'
LIMIT $startrow, 20";

EDIT: Explaining the query.

The query SELECTs certain fields used for output
FROM the posts table since the result should contain a row for each post.
That base table is JOINed with accounts table to get the data of the posting user and
additionally JOINed with contacts table so we can filter to only have posting users that are also friends added by the user in question.
An additional filter is on the allowed value and
for pagination purposes we have an offset defined by $startrow.

how do i view the previous comments? I need them to help my query. — ariel, May 11 '12 at 23:04

How to join three tables and add a "AND" operator?

2 Answers2