MySQL Performance: LAMP => make ranking on search result

Members

Help

Home

Home » Performance » LAMP » make ranking on search result

Show: Today's Messages :: Show Polls :: Message Navigator

make ranking on search result [message #3192]

Wed, 18 June 2008 02:29

larvus
Messages: 5
Registered: June 2008
Location: india

Junior Member

i hv a product table chemicals(prodname,prodcat,proddesc)

my query is

SELECT prodname FROM bw_product WHERE

prodname like '%sodium chloride%'

OR
prodcateg like '%sodium chloride%'

OR
proddesc like '%sodium chloride%'

order by prodname

"sodium chloride" is searched ,
I am getting a very large number of results and the product "sodium chloride" is nowhere near the top.
The search needs to be configured so that exact (or nearly exact) "prodname" appear at the top,exact (or nearly exact) "prodcateg" appear at the next level and the lowest "ranked" results (ones at bottom) should be ones that had a search hit in the "proddesc" field.

how can i possible these ? pls give the correct query

Larvus

Report message to a moderator

Re: make ranking on search result [message #3200 is a reply to message #3192 ]

Thu, 19 June 2008 03:08

debug
Messages: 132
Registered: March 2008

Senior Member

Maybe something like this:

SELECT prodname,2 as sort_id FROM bw_product WHERE prodname like '%sodium chloride%'
UNION
SELECT prodname,2 as sort_id FROM bw_product WHERE prodcateg like '%sodium chloride%'
UNION
SELECT prodname,3 as sort_id FROM bw_product WHERE proddesc like '%sodium chloride%'
ORDER by sort_id;

Mikhail Solovyev, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting

Report message to a moderator