homepage Welcome to WebmasterWorld Guest from 107.20.109.52
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySql search order by the character searched for
achshar




msg:4154493
 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




msg:4154508
 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




msg:4154560
 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




msg:4154570
 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..

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved