MySQL keyword frequency

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

MySQL keyword frequency

Post by jaoudestudios »

Hi,

I hope someone can help.

I am doing a mysql search on 3 columns within a table, and I am ranking the returned result and ordering by the total relevance. However, I would like to add another count to the relevance if the keyword appears in the field more than once.

Below is a simplified version.
SELECT *, (
(CASE WHEN title LIKE '%the%' THEN 2 ELSE 0 END) +
(CASE WHEN content LIKE '%the%' THEN 1 ELSE 0 END)
) as relevance
FROM content WHERE (page LIKE '%the%' OR title LIKE '%the%' OR content LIKE '%the%')
ORDER BY relevance DESC


So if 'the' appears in the 'title' column and 'content' column the result will be 3. But if 'the' appears three times in the 'content' column it won't total 3 and then add it to the 2 from 'title' to equal 5. But I would like it to total 5.

Hope that makes sense.
Thanks,
Eddie
Post Reply