CODEWITHSUNDEEP
mysqlsorting
immd
immd

Reputation: 23

Sorting mySql results using LIKE '%variable%'

I have a search form on my website that allows users to search for employees in a company. I'm using the input to search a table in a mySql DB. My SELECT state is here:

SELECT title, uid FROM table_data 
WHERE title LIKE '%$search_name%' AND blog_id = 6 
ORDER BY title ASC LIMIT 50

Currently, a search for 'Jon' would result in some like:

Angela Jones
Dejonas Lucero
Ernesto Jon
Jon White
Rick Jonston

Is there a way to have it sort in this order?

search_name%
%search_name
%search_name%

Jon White
Ernesto Jon
Angela Jones
Dejonas Lucero
Rick Jonston

Upvotes: 2

Views: 4368

Answers (2)

mathematical.coffee
mathematical.coffee

Reputation: 56915

This is one way to do it, although I feel it's not very elegant:

SELECT title, uid FROM table_data 
WHERE title LIKE '%$search_name%' AND blog_id = 6 
ORDER BY IF( title LIKE '$search_name%',0,
             IF( title LIKE '%$search_name', 1, 2 ) )

LIMIT 50

It assigns each title a '0' if it matches 'search_name%', 1 if it matches '%search_name', and 2 otherwise (which is '%search_name%' by virtue of the WHERE clause).

Then it sorts by it (ascending).

You could also do it with a CASE WHEN..THEN..END statement:

SELECT title, uid FROM table_data 
WHERE title LIKE '%$search_name%' AND blog_id = 6 
ORDER BY CASE WHEN title LIKE '$search_name%' THEN 0
              WHEN title LIKE '%$search_name' THEN 1
              ELSE 2
         END

Upvotes: 6

Jim Garrison
Jim Garrison

Reputation: 86774

You will need to issue three separate queries and concatenate the result sets. You could do it in one query as follows:

SELECT '1' as q, title, ...  WHERE title LIKE '$search_name%' ...
UNION
SELECT '2' as q, title, ...  WHERE title LIKE '%$search_name' ...
UNION
SELECT '3' as q, title, ...  WHERE title LIKE '%$search_name%' ...
ORDER BY 1,2

Upvotes: 0

Related Questions