0

I would like to search a list of products, on their title alone. The product titles, can be one , two , three or more words, like 'Jungle Warrior' for example. I'd like to rank the results with relevance, something like this.

Full Match

  1. Does the search term appear in the first word of the product title
  2. Does the search term appear in the first word and more than once in the product title (number of times it appears donates how it is ranked - more = higher ranking - do not include partial matches

Partial Match

  1. Does the search term appear in the second word and any other words in the product title
  2. Does the search term appear in the third or more words of the product title
  3. Does the search term appear as part of any of the first words and any other words in the product title(for example, the term "War" appears in the word "Warrior")

I get my array of product titles by running a standard query on the database

$q = "SELECT product_title FROM products_table WHERE product_title LIKE '%" . searchString . "%' AND product_status >= '1'";

and populate a products array

if($products = $db->recordset($q))

So then I need to work through the array and rank each result with a relevance based on my ranking criteria.. this is where I'm stuck.

It has been suggested to use FULLTEXT search which I've ticked as an answer. I've got an issue with that query though so I've opened a new question

Rich
  • 512
  • 1
  • 6
  • 18
  • In MySQL, you need to use [FULL TEXT](http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html) indexes to implement that. – Alma Do Sep 05 '13 at 10:01
  • Have you tried splitting the words and using the [`levenshtein()`](http://www.php.net/manual/en/function.levenshtein.php) function to check the relevance? – MisterBla Sep 05 '13 at 10:02

1 Answers1

0

MySQL has support for full-text indexing and searching:
FULLTEXT SEARCH

InnoDB now supports full text search as of 5.6

See How to find similar results and sort by similarity?

Community
  • 1
  • 1
zloctb
  • 10,592
  • 8
  • 70
  • 89
  • ok I've followed the info from the 'how to find similar results and sort by similarity' - the table is InnoDB so I've created a temp table as MyISAM, but I'm still getting the error 'the used table type does not support FULLTEXT searches' ? – Rich Sep 05 '13 at 10:47
  • ALTER TABLE your_name ENGINE='MYISAM' – zloctb Sep 05 '13 at 10:58
  • alright I've done that, I'm posting my query to the question - it's returning no results even though I know there are matches – Rich Sep 05 '13 at 11:33