MySql search order by the character searched for

Forum Moderators: open

Message Too Old, No Replies

MySql search order by the character searched for

achshar

12:35 am on Jun 18, 2010 (gmt 0)

hello..
i have a table like

id cat
1 Miscellaneous
2 Songs
3 Auto
4 Sports
5 Cricket
6 Tennis
when i do a simple 'LIKE %s%' search for row cat for letter 's'..
i get all the rows that contain letter 's' in order of their id..
so the result is:

Miscellaneous
Songs
Sports
Tennis

but if i type 's' the words starting with 's' must come first, coz more chances are that i am looking for a word with that begins with 's', this is common sense.. so the intended result should be something like

Songs
Sports
Miscellaneous
Tennis

where words starting with 's' appear first and then the words containing 's'

i did a lot of searching but couldn't find anything helpful.. i really hope someone out there could help..

i simply want to know how to order search by the character searched for..
i hope this makes sense :)
thanks..

achshar

1:02 am on Jun 18, 2010 (gmt 0)

also the search behaves awkward.. when i search for 's' all the above results show up but if i search for 'sp' (which should show only one result i.e sports) nothing comes up..

rocknbil

4:22 am on Jun 18, 2010 (gmt 0)

but if i type 's' the words starting with 's' must come first

Similar to the numeric solution in this thread [webmasterworld.com], I would bet (untested) something like this would work.

select * from category where cat like '%s%' order by cat like 's%' desc, cat asc;

Note it does not have the wildcard on the left - 's%' - so that would mean it matches only on letters beginning with s, but *only* in the order by.

desc may seem backwards, but like the solution in that thread, those that *don't* start with s will fall below the ones that do, and the second order clause would order those alphabetically. Try it . . . let us know if it works.

when i search for 's' all the above results show up but if i search for 'sp' (which should show only one result i.e sports) nothing comes up

Is this mySQL? If so, makes no sense at all, it should, even if you have a space before sports. like should match both Sports and sPorTs, it's case-insensitive. Maybe a problem in programming logic?

achshar

4:36 am on Jun 18, 2010 (gmt 0)

>>select * from category where cat like '%s%' order by cat like 's%' desc, cat asc;

ok WOW i could have never thought of that..! YES it worked like a charm.. quite surprising how much ORDER BY can take..
thanks alot :) a new thing learned today..

>>Is this mySQL? If so, makes no sense at all, it should, even if you have a space before sports. like should match both Sports and sPorTs, it's case-insensitive. Maybe a problem in programming logic?

yes i mistakenly had if(totalrows>1) insted of if(totalrows>0) :P
my mistake.. maybe because i was half asleep the day i wrote it.. but yeah thanks a ton..