CODEWITHSUNDEEP
mysql
Roger RV
Roger RV

Reputation: 134

Clear way to check friends in mysql query

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);

Upvotes: 0

Views: 50

Answers (1)

trincot
trincot

Reputation: 350300

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.

Upvotes: 2

Related Questions