CODEWITHSUNDEEP
phpmysql
George Grover
George Grover

Reputation: 1134

Following mySQL table search result

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.

Upvotes: 0

Views: 45

Answers (1)

Rahul
Rahul

Reputation: 77876

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;

Upvotes: 1

Related Questions