MySQL select statement that excludes fields from two other tables

Question

I am trying to execute a query to find all users that are not 'friends' of the current user. For example, if the user is logged in as 'alex', I want to display all users in the users table that are not friends of alex (i.e. david).

I'm using PHP Codeigniter if that helps. This is the query I came up with (but it returns nothing):

public function getUser($username) {
    $this->db->query("SELECT username FROM users WHERE username NOT IN 
    (SELECT user1 FROM friendships WHERE user2 = '$username' 
    UNION SELECT user2 FROM friendships WHERE user1 = '$username')");
    return $query->result_array();
}

MySQL Tables

Users

+----+----------+
| id | username |
+----+----------+
| 1  | alex     |
+----+----------+
| 2  | james    |
+----+----------+
| 3  | david    |
+----+----------+

Friendships

+----+-------+-------+
| id | user1 | user2 |
+----+-------+-------+
| 1  | alex  | james |
+----+-------+-------+
| 2  | james | david |
+----+-------+-------+

As a starting point, before going further, you should redesign your database and restructure your code. Don't use names in the friendships table; use the user IDs. That's the whole reason for creating a unique ID for each row. Second, you are wide open to [**SQL injection**](https://www.owasp.org/index.php/SQL_Injection). You need to use prepared statements, rather than concatenating variables into your query. See [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1). — elixenide, Mar 19 '17 at 04:57
@EdCottrell Thank you for your suggestions. I have provided this as a quick example to find the appropriate query, this is not the actual structure of my DB. — user7724540, Mar 19 '17 at 05:00
Well, what is the actual structure, then? And is that your actual code? If so, it's vulnerable. If not, why are you asking about code that doesn't resemble the code you're trying to fix? — elixenide, Mar 19 '17 at 05:02

score 1 · Answer 1 · answered Mar 19 '17 at 04:58

You should've stored the user id in friendship table instead of names.

Try this:

select username
from users
where username <> '$username'
    and username not in (
        select case when user1 = '$username' then user2 else user1 end
        from friendships
        where '$username' in (user1, user2)
        )

MySQL select statement that excludes fields from two other tables

1 Answers1