Boolean search results not precise enough

tvcrazyman

I've at website building for a long time, but I'm terrible with php. I bought this script for displaying products from an affiliate program. It works fine except the search is never exact enough. For instance if I searched for "Superman Action Figures" I would get everything to do with Superman, action figures would be on page 10 or something like that. I can put it in quotes which pulls up the exact phrase, but I want the user to be able to get exactly what they put in the search box without having to add quotes plus the quotes throw off the pagination.

I've tried playing with this code below but I just can't get it. Any help would be greatly appreciated. I know just enough about php to be frustrated. I even took a class on it once, but still can't get it.

if($search){
$query_count = "SELECT * FROM products WHERE MATCH(Name) AGAINST('$search' IN BOOLEAN MODE) AND Price >= $minimum_price";

traq

Sounds like a natural language search might be closer to what you want than boolean mode.

tvcrazyman

I replaced every instance of IN BOOLEAN MODE with IN NATURAL LANGUAGE MODE but then I got this error

Can't find FULLTEXT index matching the column list

dalecosp

You may have to add a FULLTEXT index, and it may not even be possible to do; see the docs here. (Note that's for MySQL 5.0, but requirements are not changed as late as 5.5 AFAICT, and those docs are also available).

sneakyimp

MySQL offers powerful search functions. They take some thought and consideration to understand and may or may not ultimately provide what you want. I'd recommend playing around with the queries offered in the docs we've linked here before you start to alter your PHP code. Adding an index to a particular field in a particular table is easy if you can access your db with the appropriate permissions in either PHPMyAdmin or MySQL Workbench.

You might also consider google custom search (search for it). The basic idea is that you can configure a google search that searches only your site and then displays the results. There might of course be google ads in the results unless you pay google to not put them there. The amount you pay will depend on how many searches you need to run.