Geek Answers Handbook

First, search for users and return friends (order)

I use the following query to select users from my "users" table with a login session id that uses $ uid:

SELECT first_name, last_name, uid, hometown from users where concat(first_name,' ',last_name) like '%$q%' and uid <> $uid LIMIT 6

I also use the following query to select all the user's friends from the friends table:

 SELECT a.first_name, a.uid, a.last_name FROM users a, friends b WHERE a.uid = b.friend_two AND b.friend_one = $uid

My question is, how would I order such a request with friends at the top? For example, if my user searches for “Dave” and I return 5 people with the first name, Dave, how can I assign his friend Dave on top of all the other Daves and order the request this way?

Thanks for any help.

+4
source share
2 answers

Try the following:

 SELECT a.first_name, a.last_name, a.uid, a.hometown, IF(b.friend_two IS NULL, 0, 1) AS isFriend FROM users a LEFT JOIN friends b ON a.uid = b.friend_two AND b.friend_one = $uid WHERE CONCAT(a.first_name, ' ', a.last_name) LIKE '%q%' AND a.uid <> $uid ORDER BY isFriend DESC
+4
source

My best idea would be to set the "rank" in your database and give them a value of 1 for the people you want to order first.

 SELECT `fields` FROM `table` ORDER BY `rank` DESC
-2
source

More articles:


All Articles