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..
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?