CODEWITHSUNDEEP
sql
Johnson
Johnson

Reputation: 818

SQL: Only showing friends

I wish so the users can only send to their friends. Right now I have this:

$qur = mysql_query("
 SELECT users.id, users.firstname, users.lastname, 
 (users.firstname = '$firstname' AND users.lastname = '$lastname') AS full FROM users 
WHERE
(users.firstname = '$firstname' AND users.lastname='$lastname') 
 OR (users.firstname LIKE '$firstname%' AND users.lastname LIKE '$lastname%')
 OR users.firstname LIKE '$firstname%' OR users.lastname LIKE '$firstname%'
 ORDER BY (users.firstname = '$firstname' AND users.lastname='$lastname') DESC") or die(mysql_error());

This shows everyone in users that are like $firstname , $lastname

Now I have tried to make only select the user´s friends $firstname , $lastname, by adding this:

  INNER JOIN users_friends ON users.id=users_friends.uID
WHERE users_friends.bID='$USER' AND users_friends.type = '$typeUsers' AND

So it got like this:

$qur = mysql_query("
 SELECT users.id, users.firstname, users.lastname, 
 (users.firstname = '$firstname' AND users.lastname = '$lastname') AS full FROM users 
  INNER JOIN users_friends ON users.id=users_friends.uID
WHERE users_friends.bID='$USER' AND users_friends.type = '$typeUsers' AND
(users.firstname = '$firstname' AND users.lastname='$lastname') 
 OR (users.firstname LIKE '$firstname%' AND users.lastname LIKE '$lastname%')
 OR users.firstname LIKE '$firstname%' OR users.lastname LIKE '$firstname%'
 ORDER BY (users.firstname = '$firstname' AND users.lastname='$lastname') DESC") or die(mysql_error());

If there exists total 4 with lastname "fox", and I am only friends with 1 of them with firstname "megan", it spits out this:

Megan Fox
Megan Fox
Mami Fox
Mimi Fox
Mumu Fox

As you can see here, it spits out "Megan Fox(who i am friends with)" twice. And i think that's because somewhere in the SQL it spits out everyone, AND THEN it spits out the friend. But how can i not spit out everyone, but only my friend?

Upvotes: 0

Views: 80

Answers (1)

Guffa
Guffa

Reputation: 700362

It's because the operator precedence. The expression x and y or z does't evaluate as x and (y or z) but as (x and y) or z. therefore, the friend limitation only applies to the first way of matching the names.

You need some parentheses around the name conditions:

WHERE users_friends.bID='$USER' AND users_friends.type = '$typeUsers' AND (
 (users.firstname = '$firstname' AND users.lastname='$lastname') OR
 (users.firstname LIKE '$firstname%' AND users.lastname LIKE '$lastname%') OR
 users.firstname LIKE '$firstname%' OR
 users.lastname LIKE '$firstname%'
)

As part of the condition for joining in the friends table is only applied for some of the records, you will be joining in records from other users also. The reason that Megan Fox appears twice is most likely because she is friend with someone else also, so she appears twice in the friends table.

Upvotes: 2

Related Questions