Roger RV Roger RV -4 years ago 130
MySQL Question

Clear way to check friends in mysql query


  • First of all I have an id that corresponds to the users table identifier and I pass by get to php.

  • I want to check: nickname.users, friends.id_friends, friends.id_adder, friends.id_added and friends.id_tipus but in a best way checking friends.id_adder, friends.id_added and getting the nickname correctly. The code I make works.



My query:

SELECT users.nickname,
friends.id_friends, friends.id_adder, friends.id_added, friends.id_tipus
FROM friends
LEFT JOIN users ON users.id=friends.id_added OR users.id=friends.id_adder
WHERE
(friends.id_adder='".$id."' AND (SELECT nickname FROM users WHERE id='".$id."')<>users.nickname)
OR
(friends.id_added='".$id."' AND (SELECT nickname FROM users WHERE id='".$id."')<>users.nickname);

Answer Source

I would transform that query into this:

SELECT    u1.nickname,
          friends.id_friends, 
          friends.id_adder, 
          friends.id_added, 
          friends.id_tipus 
FROM      users u1
LEFT JOIN friends  ON u1.id IN (friends.id_added, friends.id_adder)
LEFT JOIN users u2 ON u2.id IN (friends.id_added, friends.id_adder)
                   AND u2.nickname <> u1.nickname
WHERE     u1.id = ?

You should not inject the $id value into your SQL string, as this makes your code vulnerable to SQL injection. Instead use prepared statements.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download