Queries within queries using MySQL

I'm trying to order my search results more accurately. I specify a search string - e.g. "Beijing Olympics"

• If the title column contains "Beijing Olympics" then the score 100 is added to the score, otherwise nothing is added
• If the shortDescription column contains "Beijing Olympics" then 50 is added to the score, otherwise nothing is added
• If the longDescription column contains "Beijing Olympics" then 10 is added to the score, otherwise nothing is added

In开发者_如何学JAVA the end the maximum score possible per record would be 160 and I want the results to be ordered by highest score first and limited to a maximum of 10 results.

Below is definitely wrong, but it should illustrate what I am hoping to achieve!

SELECT 
    title, 
    id, 
    (
    IF((match 'Beijing Olympics' against title)*100) + 
    IF((match 'Beijing Olympics' against shortDescription)*50) + 
    IF((match 'Beijing Olympics' against longDescription)*10)
    ) as score 
from listings 
order by score desc limit 10

You probably want to use CASE
http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

Otherwise I think your selection logic is fine.

SELECT 
    title
    ,id
    ,(
        (CASE WHEN title LIKE '%Beijing Olympics%' THEN 100 ELSE 0 END) 
        + (CASE WHEN shortDescription LIKE '%Beijing Olympics%' THEN 50 ELSE 0 END) 
        + (CASE WHEN longDescription LIKE '%Beijing Olympics%' THEN 10 ELSE 0 END) 
     ) AS score 
FROM 
    listings 
ORDER BY 
    score desc 
LIMIT 10

Note that I haven't tested this query, but it should be close.

EDIT: Also note that this matches for exact value.