遵循mySQL表搜索结果

Question

I have a following system where users can follow each other and search for users to follow. Here are examples of the users and following tables:

Users Table:
+-------+-----------+
| id    | userName  |
+-------+-----------+
| 1     | John      |
| 2     | Harriet   |
| 3     | Chris     | 
| 4     | Lisa      | 
| 5     | Joe       | 
+-------+-----------+

Following Table:
+-------+-------+-------+
| id    | id_1  | id_2  |
+-------+-------+-------+
| 1     | 5     | 3     |
| 2     | 1     | 2     |
| 3     | 1     | 5     |
| 4     | 5     | 4     |
+-------+-------+-------+

I want to know how to structure a mySQL statement to join the following table with the users table where the username is like '%$search%' to return all results of this occurrence (i.e. searching 'jo' returns John and Joe) and when there are multiple results for each person (i.e. Joe will occur twice in Following) return only results where id_1 is the current logged in users id to show that the logged in user is already following this person.

$search = $_POST['search'];
$userid = $_POST['userid'];

$qry = "
SELECT u.id
     , u.userName
     , f.id_1
     , f.id_2 
  FROM users u
  LEFT 
  JOIN following f
    ON IF('$userid' = f.id_1, u.id = f.id_2, u.id = f.id_1)
 WHERE u.userName LIKE '%$search%';
 ";

This query returns every occurrence of the username in the following table but it should only return once.

Any help would be great. Thanks.

Answer 1

Not absolutely sure but give it a try

SELECT u.id
     , u.userName
     , f.id_1
     , f.id_2 
  FROM users u
  LEFT JOIN following f
  ON u.id in (f.id_1,f.id_2)
  AND f.id_1 = '$userid'
 WHERE u.userName LIKE '%$search%'
 ORDER BY u.id
 LIMIT 1;