使用mysql顶部的“like”显示查询的搜索记录

Question

I am writing a search query which makes a search based on first name and last name. The query is like this:

select fname,lname 
from users 
where fname like fname like '%$name%' or lname like '%$name%';

if the user enters first name and last name in the search box,I have exploded it with a 'space' and the query goes like this

select fname,lname 
from users 
where fname like fname like '%$f_name%' or lname like '%$l_name%';

In the second query if I enter John Thomas,it shows me all the records with John or Thomas in first or last name,but the actual search result i.e John Thomas(exact match) is somewhere below in the results.(If I have 100 results its on the 60th position).

How can I modify the query to achive this or do I have to handle it programatically? i.e Check the result array and match the check box value for its presence and display it fist.

Answer 1

Try:

SELECT fname, lname FROM users 
WHERE (fname = '$f_name' AND lname = '$l_name') 
OR (fname LIKE '%$f_name%') OR (lname LIKE '%$l_name%');

And if you need to order then ORDER BY lname, fname in the end of the query, depending on what you need to order by of course.