How to sort mysql search results

Question

I wrote a simple mysql query for search my db for usernames and their fullname but i dont know how to sort the result based on their relevance like how its done on instagram. This is my original query
$search is the search string var
SELECT username,fullname from users WHERE username LIKE '%$search%' or fullname LIKE '%$search%'
I would greatly appreciate any help.

Thanks in advance 😀

Answer 1

will try that nd give u feedback

Answer 2

Whichever is more important.

if fullname is more important then it should be ORDER BY fullname asc, username asc

I suggest you should set a LIMIT so it doesn't give you a long list.

LIMIT 10

Answer 3

What do you mean by relevance? What ouput are you after?

Answer 4

i want it to be ordered based on the amount similarities between the words

Answer 5

ORDER BY username asc, fullname asc