When searching for: coffee machine
I get result for "coffee" and for "machine" (added together)
Using quotes does't make a difference.
Is it possible when searching for "coffee machine" (without the quotes), I only get the result for coffee + machine.
As it is now I also get result like bread machine, because machine is in the name column.
Yes it did. Perfect.
Two more things.
The plural change is not working in combination with the above. Single is correct. I added this in front of the above:
if (strlen($parts[0]) > 3)
{
$words = explode(" ",$parts[0]);
$newWords = array();
foreach($words as $word)
{
if (substr($word,-1)=="s")
{
$newWords[] = substr($word,0,-1);
}
}
$allWords = array_merge($words,$newWords);
$parts[0] = implode($allWords," ");One more. I hope it is possible.
Search is now in the product name column. Is it possible to do a search in category + name and with category listed first.
Example. When searching for coffee machine, first listings from the category coffee machine and second from name.
Hello John,
I think the reason it isn't working in combination with the plural modification is because by using a logical AND you will be looking for a record containing BOTH the singular and plural versions of the word, which won't exist - although I would have thought that singular words would work and plurals would not as it is for plurals that the code generates the extra word. Does this make sense...?
Regarding searching by category; for efficiency reasons the search has to be performed against an index; so to do this you would need to create a combined index against the product name and category fields. You can do this, and it would still be compatible with the full text indexing by following the instructions for search by name and description in this thread:
http://www.pricetapestry.com/node/224
However, in your case you want to specify category first so that it takes higher relevance. The SQL to generate the index would be:
create fulltext index ft_categoryname on products (category,name);Then, for the SQL modifications, instead of the changes described in the thread you would use:
MATCH (category,name) AGAINST ('".database_safe($parts[0])."') AS relevanceHope this helps!
Cheers,
David.
I am not giving up yet. One more try. Only the first problem (single and plural vs keyword + keyword).
I switched it. Now I have first keyword + keyword and then the plural part.
The difference is, I now get search result all the time and no zero result anymore, but it is now or single or plural.
I did this:
default:
if (strlen($parts[0]) > 3)
{
$match = "+".str_replace(" "," +",$parts[0]);
$sql = "SELECT *, MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($match)."' IN BOOLEAN MODE) AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH name AGAINST ('".database_safe($match)."' IN BOOLEAN MODE) GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE MATCH name AGAINST ('".database_safe($match)."' IN BOOLEAN MODE)";
$orderBySelection = $orderByFullText;
}
else
{
$words = explode(" ",$parts[0]);
$newWords = array();
foreach($words as $word)
{
if (substr($word,-1)=="s")
{
$newWords[] = substr($word,0,-1);
}
}
$allWords = array_merge($words,$newWords);
$parts[0] = implode($allWords," ");Hi John,
In the first case (searches for words > 3 characters) you don't seem to have the code that the create the array of plural words. It is shown in the second part of your code where the query is up to 3 characters.
Did you mean to have this code in the first case also?
Hope this makes sense...!
Cheers,
David.
First I tried this (see below), but this didn't work, because I had zero search result and you explained this. So I switched them and the result improved.
default:
if (strlen($parts[0]) > 3)
{
$words = explode(" ",$parts[0]);
$newWords = array();
foreach($words as $word)
{
if (substr($word,-1)=="s")
{
$newWords[] = substr($word,0,-1);
}
}
$allWords = array_merge($words,$newWords);
$parts[0] = implode($allWords," ");
$match = "+".str_replace(" "," +",$parts[0]);
$sql = "SELECT *, MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($match)."' IN BOOLEAN MODE) AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH name AGAINST ('".database_safe($match)."' IN BOOLEAN MODE) GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE MATCH name AGAINST ('".database_safe($match)."' IN BOOLEAN MODE)";
$orderBySelection = $orderByFullText;
}
else
{Maybe I should explain what I want. When search for brown shoes only the brown shoes results. This works perfect with you modification. And single and plural should be the same.
Both of your modifications do work, but not in combination. I think the 3 character part can be removed. Words like "car" are ignored. As it is now, it is not possible to find car speakers, because car is ignored.
Hi John,
The three character part should be left in for the short searches that do not use the full text index - even though it won't be used very often . You can configure MySQL to index words shorter than 3 characters, see the following page for more information:
http://dev.mysql.com/doc/refman/4.1/en/fulltext-fine-tuning.html
Anyway, back to the plural + all words issue. The problem is of course that after the plural mod, the search is as follows:
+brown +shoes +shoe
What we actually want is:
+brown +(shoes shoe)
The logical OR should be implied by the brackets, with the preceeding + meaning that at least one of the bracketed words must be present. Try the following code to do this:
default:
if (strlen($parts[0]) > 3)
{
$words = explode(" ",$parts[0]);
foreach($words as $k => $word)
{
if (substr($word,-1)=="s")
{
$plurals[] = " +(".$word." ".substr($word,0,-1).") ";
unset($words[$k]);
}
}
$parts[0] = implode($words," ");
$plurals = implode($plurals," ");
$match = "+".str_replace(" "," +",$parts[0]).$plurals;
$sql = "SELECT *, MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($match)."' IN BOOLEAN MODE) AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH name AGAINST ('".database_safe($match)."' IN BOOLEAN MODE) GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE MATCH name AGAINST ('".database_safe($match)."' IN BOOLEAN MODE)";
$orderBySelection = $orderByFullText;
}
else
{
Hello John,
This is happening because MySQL's full text search by default returns results that only contain some of the words. A result that contains all the words is always ranked higher in relevance; so in most cases this produces the best results.
If you want to restrict the results to those containing all the words you need to modify the script to use a BOOLEAN MODE query on the full text index. This is available as of MySQL version 4.01. There's more information and the mods in this thread:
http://www.pricetapestry.com/node/420
Hope this helps!
Cheers,
David.