[RESOLVED] keyword relevance sorting

[znerol]

I am trying to make a search engine for my school project website but I'm having trouble ordering the results.

The sample table looks like this:
id | title
1 | Bulok Enrollment System
2 | Bulok Enrollment System Again
3 | Bulok Inventory System
4 | Bulok Invetory System Again

If I searched the keyword 'bulok again' then row 2 and 4 should appear on first than row 1 and 2.

Here is the query I use for the searching:

SELECT DISTINCT *, COUNT(*) as count FROM research WHERE (title LIKE '%again%' OR title LIKE '%bulok%') GROUP BY id ORDER BY count DESC

where 'count' should be the one counting the occurence of the words on the title but I can't get it to work. My table is InnoDB btw, just in case.

Thanks in advance.

[NogDog]

Maybe (untested)...

SELECT 
   DISTINCT *, COUNT(*), 
   IF(title LIKE '%again%', 1, 0) + IF(title LIKE '%bulok%', 1, 0) AS matches
FROM research
WHERE matches > 0
ORDER BY matches DESC

[znerol]

I found this tutorial. I'll just give it a try, hopefully I can do it fine. :queasy:

edit: @: I just tried your suggestion but mysql says "#1054 - Unknown column 'matches' in 'where clause'. " But if I remove the 'WHERE matches>0' part it works, problem is that it selects all of the records in the table.

[znerol]

Yay! I got it working... I replaced WHERE with HAVING. Thanks man.