PHP PDO search engine with relevancy

Question

Hi,

I'm desperately trying to create a relatively concise search engine with PHP, mySQL and PDO. I have a database of books, and I'm trying query a search against two of the fields. Here is what I have so far:

"SELECT id, title, author, isbn, MATCH(title, isbn) AGAINST (:term) AS score FROM %sbooks WHERE MATCH(title, isbn) AGAINST (:term) AND is_active = 'y' ORDER BY score DESC LIMIT 0,25");
$sth->execute(array(':term'=>'+'.$keywords));

$keywords is a string from the search field, so could be something like 'Some kind of book'.

I don't want to filter out any words or do anything too fancy, but the above query is ignoring a lot of results, even when I put it IN BOOLEAN MODE.

Also, I have two books in the database, 'book number 1' and 'book number 2'. If I search 'book number 2' then it will still score 'book number 1' higher. The main problem however is that a lot of searches are just returning with no results :(

Could anyway help suggest what I am doing wrong here?

Many thanks

Answer 1

All words that have less than three letters are ignored. So, 1 & 2 at the end of titles are ignored. You can change this setting:

http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html

Matching against ISBN is of no use also as this is not a word from MySQL perspective.

I think you should invent your own search engine if you want to do it good.