how can I display 1 to 10 score on my query.
for e.g. I get 10 records with this and I want to match highest with 10 and so on.
SELECT DISTINCT c.id, c.title, cv.title2 , match ( c.title, c.title2, c.title3, c.title4 ) against ('Support' IN BOOLEAN MODE) AS score
FROM table1 AS c
WHERE match ( c.title, c.title2, c.title3, c.title4 ) against ('Support' IN BOOLEAN MODE)
When I do a search my database returns set of results.
If keyword matches 50 keywords in c.title, c.title2, c.title3, c.title4 ( this is just an example) then I want to display 10 out of 10 as the score and say it's a perfect match.
If keyword matches 45 keywords in c.title, c.title2, c.title3, c.title4 ( this is just an example) then I want to display 9 out of 10 as the score.
If keyword matches 30 keywords in c.title, c.title2, c.title3, c.title4 ( this is just an example) then I want to display 7 out of 10 as the score, some perfect.
If keyword matches 2 keywords in c.title, c.title2, c.title3, c.title4 ( this is just an example) then I want to display 1 out of 10 as the score some keywords match.
and so on.
Ok I think I got it.
SELECT DISTINCT c.id, c.title, cv.title2 , (match ( c.title, c.title2, c.title3, c.title4 ) AGAINST('desktop') * 4.5 ) AS score
FROM job
WHERE
match ( c.title, c.title2, c.title3, c.title4 ) AGAINST('desktop')
ORDER BY score DESC
Please let me know if I can write it better.