Note: As of revision 11/09A of Price Tapestry, the description field can be included in search via the Advanced Configuration file.
Hi David,
I know a few people have asked before regarding the search facility, but you have said that it would be too intensive on databases.
Is there any chance of this being made into a mod so that it can be used at a persons risk, with the holiday website, if someone wants to search for Bristol it might not come up with results unless it has in the title Bristol.
Is there any way of coming up with a trial search that does look into the descriptions etc, I have just moved onto dedicated server, so want some way of testing it for realibity.
Regards
Mark
Hi David,
I tried all of the above, but with no joy, trying on a non empty database though, but can't see that being a problem.
I added brand as well to the ON (name,description);
My table prefixes were holiday_products
I have also added the MATCH (name,description,brand)
I'll do a test with a blank database and see if this works, but currently doesn't work on a filled database.
Regards
Mark
Just uploading a product feed to www. worldhotels .org.uk
this is a fresh database with the mods made before, now uploading a datafeed for it.
Hopefully it will have the location to which I was searching for.
I remember there is another modification as well where it breaks down the words if I remember correctly.
So for example if your searching for food products, before it wouldnt show it, but if you used this mod, then it would show products.
Hi David,
Seems to work on a blank database with one feed uploaded.
If you got the website above, and type in for example massage, it gives about 19 results, or if you type in Spain, it also gives results :)
I will try and add, brand and category as well to see what happens, but looking pretty good so far, seems fast as well.
Regards
Mark
Me again :)
Yep just done another test on a full database, and sadly this produced no results. When working on a blank database, and then uploading the product feeds, it is perfect.
I might be able to get around this though, by renaming the file, emptying the database and then re registering the files.
I'll give that ago and report back.
Regards
Mark
Hi Mark,
It's not impossible that under your MySQL configuration the indexes are only built during an import. On my test server, the description search works straight away on a populated database.
This should not cause you any problems however - just include the modifications in each new install before you upload and import feeds.
Cheers,
David.
Hi David,
Sadly no joy my end on a full database, not even removing the files and then starting again.
So looks like it will be a late night to take down and reinstall again, thankfully only got on a couple of sites.
With this mod though, it's going to make a hell of a difference, because when people come to a site, and they have been to kelkoo before, and they type in a word, or a name that isn't in the name field, then they think, well thats crap, and they walk away.
NOW they will hopefully find 99% the results they are looking for, and won't walk away.
Think, I'm going to spend the afternoon going through the mods that I can find, add them to my mastercopy of the script and then, it's all raring to go then for future installations.
This is deffinatly a winning mod though.
Regards
Mark
Hi Mark,
Sounds good!
If you're planning on using this mod on several installs, the easiest thing to do will be to add the SQL to make the additional FULLTEXT index directly into setup.sql. Then, if you also have the modified search.php in your master install you won't need to anything other than the usual installation procedures.
In setup.sql, look for the following code on line 102:
FULLTEXT KEY name_2 (name)..and change this to:
FULLTEXT KEY namedescription (name,description)This mod will replace the original full text index on just the name field with your new one - and you can of course change the above to include your brand / category fields in line with changes that you have already experimented with. Having done this there will be no need to use or run makeIndex.php on your new installs because the index will be created by setup.php.
Hope this helps,
Cheers,
David.
I know why it doesn't work now.
If you check the above it was CREATE FULLTEXT INDEX namedescription ON products (name,description);
But on the last it was FULLTEXT KEY namedescription (name,description)
I shall try installing with the FULLTEXT INDEX namedescription ON products (name,description); and see if that works.
Is there any way that I can edit the previous installs to include this?
It's possibly easy and staring me in the face, but hopefully I don't have to take everything down and start again.
Regards
Mark
Hi David,
Hopefully I have sorted this problem out, people should do the following
1) Do not replace line 102 with FULLTEXT INDEX namedescription ON products (name,description);
infact leave that well alone
2) goto the end of your setup.sql file
then add
CREATE FULLTEXT INDEX namedescription ON products (name,description);
This should do the trick, and keep the related products active.
Hi David,
With the full text index it is only possible to search for full words in the
description. Now my question is if it is possible to search on parts of words.
The reason for this is that people often search on singular keywords while words in the feed are
written in plural, no results at all!
Other question:
Is there a work around for automatic (pre programmed) search and
replace for standard (often used) replacements? Like replace ë with e in Italië
At this moment I do this with the search and replace filter function, for a
lot of feeds this is a lot of work!
Thanks in advanced for some help!
Frank
Hi Frank,
There is the opposite modification (add plurals to the search) in the following thread:
http://www.pricetapestry.com/node/617
However what you're describing would involve adding the plural version (whether valid or not) to every word in the query. This is easy to do, and shouldn't cause any performance issues. Instead of the replacement code included in the other thread, use this:
$words = explode(" ",$parts[0]);
$newWords = array();
foreach($words as $word)
{
$newWords[] = $word."s";
}
$allWords = array_merge($words,$newWords);
$parts[0] = implode($allWords," ");Automatic replacement of accented characters etc. could be done in a similar way, but could go on to have performance issues. Would you consider as an alternative replacing these with standard ASCII counterparts during import, and performing the same replacement on the search? That would be much easier than trying to add every possible version to the query...!
This could be done within the tapestry_normalise() function, which already operates on the product name during import, and the search query when input by the user. In includes/tapestry.php, look for the last line of the tapestry_normalise() function (line 31) as follows:
return $text;...and replace this with:
$search = array("Ã","â");
$replace = array("A","a");
$text = str_replace($search,$replace,$text,);
return $text;Simply expand out the $search and $replace arrays with any characters that you wanted to treat like this and then the search and product names should match up nicely...!
Hope this helps!
Cheers,
David.
Hello David
I've tried to install this mod where searches are made in both product name and description.
I have
1. CREATE FULLTEXT INDEX namedescription ON products (name,description); This query took about half a second
2. in search.php Replaced MATCH name with MATCH (name,description) ( 3 replaced)
It doesn't work though, still only searching product name. I've tried clicking on "import" on all my items
Any advice?
Cheers
Mally
Hi Mally,
This indexing method only applies to the FULL TEXT search method, which I think you disabled recently in order to match partial words.
It's easy enough to add the description to the basic search method which is what you'll need to do. In your latest search.php you should have the following line:
$wheres[] = "search_name LIKE '%".database_safe($word)."%'";...if you replace this with:
$wheres[] = "(search_name LIKE '%".database_safe($word)."%' OR description LIKE '%".database_safe($word)."%')";...then the normal search will include the description as well!
Cheers,
David.
Hello David,
Thats works striaght away. Do I need to undo any of the options 1 and 2 I done before?
Thanks again for your great support!
Mally
Hi Mally,
I wouldn't worry - as you said it only took half a second to construct the full text index in the first place the performance impact on import will be insignificant - and you never know, a use for the full text index may crop up in the future so i'd simply leave it in place!
Cheers,
David.
Hi David
I tried to implement this (in step 1 I used makeIndex.php and it returned 'Done') with MATCH name,description,brand but then when I tried searching, I got this
{code saved}
This is the result returned when trying the debug code in this thread http://www.pricetapestry.com/node/203.
What does this mean?
I removed the changes, then removed the feeds and de-registered them to try and make an empty database but it still didn't work. Is the database empty when you remove the feeds?
I hope this makes some sense.
Thanks
Phil
Hi Phil,
As you have included brand in your new query, did you modify makeIndex.php accordingly to include brand in the index? You would need to use:
<?php
set_time_limit(0);
ignore_user_abort();
require("includes/common.php");
$sql =
"CREATE FULLTEXT INDEX namedescriptionbrand
ON ".$config_databaseTablePrefix."products (name,description,brand)";
database_queryModify($sql,$result);
print "Done.";
?>
There needs to be an index matching the fields used in the AGAINST list in the SQL otherwise the SQL will fail...
Cheers,
David.
Hi Dave,
I thought I had done this last night but I wasn't sure if it successfully overwritten the previous brand-less makeIndex. Turns out all I had to do was change the 'Done' success message to test to see if the new code had been used, and now it works perfectly.
It's always the simple things that catch me out
Thanks Dave
Hello David,
I know its not a good idea for the performance but i saw another site running on this script with about 40.000 products and it seems to be quit fast with fulltext index.
I know it depends on many things on the server but what do you think if you have about 30 a 40.000 products not more and you use the fulltext index is it to much for the server?
Another question is i understand how i need to activate it but what if i want to undo it.
Can i change this code than in something else?
CREATE FULLTEXT INDEX namedescription ON products (name,description);Thanks Leo
Hi Leo,
30-40K products should be fine with the full text index.
If you want to undo the name/description modification, you can execute the following SQL:
DROP INDEX namedescription ON productsCheers,
David.
Hi David,
Thanks for the answer.
One small question about this, i have try with the name,description,brand and category with fulltext index.
Does the fulltext index have something to do with the import process? When i test same sites it feels like the one with the fulltext index on description field takes longer time to import than without?
Leo
Hello Leo,
Yes - it will certainly affect the import time because every record inserted has to update the full text index file...
Cheers,
David.
I followed this and wanted to include name, description and category. I updated both search.php and searchexternal.php as above, but including category. I added the full index by phpmyadmin as above. I removed all the feeds and deregistered them. I then uploaded them and registered them again. However, it still doesn't work.
If I search: http://www.discountpetsupply.co.uk/petsupplies/search.php?q=horse+headcollar
It doesn not find any products, yet I know there are products in the database with both horse and headcollar in the name and description. I then tried doing a search for equestrian headcollar (found in both category and name) but still no results.
The only think that is different from the default setup is that I have my searches logical so horse + headcollar (not horse or headcollar)
Also, if doing a search for rug I get rugby shirts. I just want the whole word, not part.
---------
Jill
Hi Jill,
It doesn't seem to be including the description field at all (for example searching for "entertainment" on its own, which appears in some descriptions on your site).
Could you perhaps email me your modified search.php and i'll check it out. If it looks OK, i'll send you a test script back which will described the new indexes on the products table so that I can check they're OK...
Cheers,
David.
Hi David,
I´ve tried to get this to work, here´s my modifications:
In phpMyAdmin:
CREATE FULLTEXT INDEX namedescriptiongrowboxbrandtypeclimate ON products (name,description,growbox,brand,type,climate);In admin.php here
<?php
WHERE MATCH (name,description,growbox,brand,type,climate)
?>
and here
{content saved}
At first I thought it worked, but now when I search, I often dont get any results at all.
For example, one product is named 'Early Riser', the type is 'Mixed' and the climate is 'Outdoor'.
If I search for 'Early' I don´t find anything, I have to search the full name to get a result.
It wont return any results if I search on 'Outdoor' either.
When I search on Mixed the product shows up, but if I search for type:Mixed: nothing shows up.
Did the full text index screw it all up?
Hi Daniel,
Do you mean that you made the changes in search.php?
To change the full text index in use, you would need to replace:
MATCH name AGAINST ('".database_safe($parts[0])."')...with:
MATCH name,description,growbox,brand,type,climate AGAINST ('".database_safe($parts[0])."')...on lines 79 and 81 (3 instances altogether)
The second modification looks fine - and would apply whenever there is a word
of less than 4 characters in the query as these are not included in a FULL
TEXT index (for performance reasons).
Cheers,
David.
Yeah, sorry, I meant search.php.
I've made the changes you mentioned in your last reply, but somethings really wrong.
It's almost as if the search only uses the description now when searching. Sometimes I get a hit in the description, sometimes I dont get any match at all. And I get no results at all when searching for climate:Indoor: or type:Mixed: or any other of the categories. When searching for brands it works though, for example brand:Nike:.
Think it will help if I delete the fulltext index and empty the sql tables and then do it all over again?
Hi David,
I still haven´t been able to fix the full text search.
At the moment, the search is barely able to return any results at all.
As I wrote in the last reply, I don´t get any hits if I search for type:Mixed: or gender:Female: etc.
The only thing that works is when I search for brand:. When I search for climate:Indoor: and climate:Outdoor it returns the same 4 results, even though I have 4 indoor and 6 outdoor products.
Any idea where I screwed up? Any help would be great, because I can´t launch the website when the search is messed up like this.
Best regards
Daniel
Hi Daniel,
As you have made significant changes, could you perhaps email me your modified search.php and includes/admin.php and I'll take a look for you, plus a link to your site; and the search terms you have been trying together with the products that you would expect to be returned in those results... That will help loads and I'll see what I can suggest...
Cheers,
David.
Hi David,
I´ve fixed part of the problem now. In an earlier post I wrote that the script won´t return any results when searching for the new fields, like when I search for climate:Outdoor: or gender:Regular:. In node 2417 I found this code from search.php and just copied and pasted for the extra fields and now it works!
<?php
case "merchant":
// pass through to category
case "category":
// pass through to brand
case "brand":
?>
Now the only problem I have is the full text search which isn´t working. At the moment it seems like it´s only searching in the description field, when it should search in the name, description, brand, type, climate and gender fields. I´ve followed the instructions and I´m out of ideas really.. Could you take a look at the files I sent you?
If it takes alot of your time I´m of course willing to pay for the support.
Take care,
Daniel
Hi Daniel,
Your email included the source inline rather than attached - could you please send me search.php again as an attachment (perhaps if you .zip the file first it won't be copied in line as some email clients do that automatically for text)...
If you could also let me know; in order to implement this you will have added a full text index against based on the required fields. If you have phpMyAdmin (or a similar tool), could you go to the products table definition page and look at the table of indexes and let me know what the "cardinality" value is for the full text index that you added...
Cheers,
David.
I also am looking to be able to search by name, description and brand.
But for some reason when I search by brand there are no results. Name and Description work great.
I have done the following:
CREATE FULLTEXT INDEX namedescriptionbrand ON products (name,description,brand);
Search PHP:
MATCH (name,description,brand)
Any ideas why no results when searching by brand? It does display products if I click on browse brand link.
Hi Steven,
Are there a wide variety of brands to be included? Words that appear in more than 50% of records are not included in a full text index; nor are words under 4 characters long. It's strange as the index must have been created successfully for the description to have been included. I would first import all feeds again; and if that doesn't help perhaps you could email me a link to your site and a copy of your modified search.php and I'll take a look!
Cheers,
David.
David,
In order to test this I had populated "brand" with the same name for all records.
According to you this is why no results were returned (over 50%)
When I created a product with a different brand name it returned the results fine.
Thanks for pointing the over 50% issue out to me. With "real" data this should not be a problem.
Best software support ever!
Steven
I have tried adding this feature with no success. I first deleted and de-registered all of the existing feeds, used the makeIndex.php script, then re-uploaded and registered the feeds and it still doesn't not search the description area. Based on the comments above, do I really have to start from scratch again?
Hello Nick,
Your modifications all look fine - it's simply down to the fact that there isn't enough variation in your products yet for MySQL's full text index to work at all i'm afraid - as of the 24 products on this site; all have virtually identical title and descriptions - and a Full Text index only operates on words that appear in less than 50% of records.
As you add more products to the site the full text index will then work properly (and will search the description). However, if the site is only intended to cover a small number of products as online at the moment, it's probably best to disable the full text index and do a manual search of the name and description in the basic search method.
I've made this change for you on your site... If you want to roll back, remove the new code on line 93 and uncomment the original code on line 92...
Cheers,
David.
Hi David,
I want the search by default to brand only. Is it possible?
Thanks,
Syed
Hello Syed,
Sure - in search.php, look for the following code around line 128:
if ($config_useFullText)...and REPLACE that with:
if (FALSE)And then slightly further down, replace line 171:
$where .= "search_name LIKE '%".database_safe($word)."%'";$where .= "brand LIKE '%".database_safe($word)."%'";...and line 180:
$where .= " OR search_name LIKE '%".database_safe(substr($word,0,-1))."%'";$where .= " OR brand LIKE '%".database_safe(substr($word,0,-1))."%'";Hope this helps!
Cheers,
David.
Hi David,
Is it possible to search by "name and brand"?
for example "Nike shoes"
Nike is from brand and shoes is from the name and both the data to be displayed?
The name field may contain data like additos shoes, shoes, b162 shoes etc..
I need to display all the shoe varieties and brands. Note: b162 may be a model number of nike shoe.
And also brand may be of 2 or 3 letter word too like bq.
Thanks,
Syed
Hello Syed,
If you would like to email me your search.php I will either add or modify the brand: style search so that it supports the following query, which should do what you want:
brand:nike:name:shoesCheers,
David.
hi david
was wondering is it possible to and EAN/sku/ISBN to the name and description search? do you think this would work ok?
regards
Phil Stone
www.buy24-7.net
Hi Phil,
In theory you can add as many fields as you like in exactly the same way. Simply create and run a dbmod.php to build the index based on the fields you want to include, e.g.
<?php
set_time_limit(0);
ignore_user_abort();
require("includes/common.php");
$sql =
"CREATE FULLTEXT INDEX namedescriptionean
ON ".$config_databaseTablePrefix."products (name,description,ean)";
database_queryModify($sql,$result);
print "Done.";
?>
(here i've added ean to the index)
...and then do a search and replace in search.php, replacing each instance of:
MATCH (name,description)with:
MATCH (name,description,ean)Additional fields can be added in the same way...
Cheers,
David.
--
PriceTapestry.com
Thanks david works great!!
Is there a way you could show a main product ean under the main description even if the merchant with the cheapest product doesn't use ean's in their feed, and if no merchants comparing prices on that page use ean's it would show nothing, only asking to see if it's possible as I'm thinking of using ean, sku and isbn info on my site to enhance the search ability of my site giving the most accurate comparison results as product names are sometimes hard to get the same
Thanks again for creating a great product that's so adaptable!!!
Phil Stone
www.buy24-7.net
Hey David I am wanting to get better search results by searching descriptions as well as the title. Is this thread still good with the latest version as of 7/22/2012? I wanted to make sure before I start making modifications.
Thanks,
Tommy
Hi Tommy,
Search description is included in the latest distribution - you can enable it in config.advanced.php by changing line 10 as follows:
$config_searchDescription = TRUE;...and then browsing to /setup.php again where there will be a link to build the new name+description index...
Cheers,
David.
--
PriceTapestry.com
Hi David
I tried to add my original_fields to the search.php, but its showing up a boolean error?
I created this file dbmod_searchfields.php
<?php
set_time_limit(0);
ignore_user_abort();
require("includes/common.php");
$sql =
"CREATE FULLTEXT INDEX namedescriptionskumodelmpcoriginal_modeloriginal_skuoriginal_description
ON ".$config_databaseTablePrefix."products (name,description,sku,model,mpc,original_model,original_sku,original_description)";
database_queryModify($sql,$result);
print "Done.";
?>then in search.php i changed
$parts[0] = implode($allWords," ");
if ($config_searchDescription)
{
$matchFields = "name,description,sku,model,mpc";
}
else
{
$matchFields = "name,description,sku,model,mpc";
}to
$parts[0] = implode($allWords," ");
if ($config_searchDescription)
{
$matchFields = "name,description,sku,model,mpc,original_model,original_sku,original_description";
}
else
{
$matchFields = "name,description,sku,model,mpc,original_model,original_sku,original_description";
}and
$orderBySelection = $orderByFullText;
}
else
{
$wheres = array();
foreach($words as $word)
{
$where = "(";
$where .= "CONCAT(search_name,description,sku,model,mpc) LIKE '%".database_safe($word)."%'";to
$orderBySelection = $orderByFullText;
}
else
{
$wheres = array();
foreach($words as $word)
{
$where = "(";
$where .= "CONCAT(search_name,description,sku,model,mpc,original_model,original_sku,original_description) LIKE '%".database_safe($word)."%'";do you see any reason why this would not work?
thanks
Phil Stone
www.buy24-7.net
Hi Phil,
Changes look fine - can you copy the full text of the error message that you are getting into a reply and i'll check it out for you... To double check all the database changes if at the same time you could view the page with database debug mode enabled by setting $config_databaseDebugMode = TRUE; at line 6 in config.advanced.php that might reveal more information; for example if the new full text index hadn't been created properly...
Thanks,
David.
--
PriceTapestry.com
David, apologies, just got it working!!
(hadn't properly configured one of the fields)
regards
Phil Stone
www.buy24-7.net
Hi Mark,
It is actually reasonably straight forward to extend the search functionality to include the description field. The instructions are scattered amongst other forum posts, so I thought now is a good time to bring them together into one post....
INCLUDING THE DESCRIPTION FIELD IN SEARCH
Step 1: Create a combined full text index on the name and description fields
Before the script can be modified to search both the name and description when a user enters a query, first the database must be modified to include a combined FULL TEXT index against the name and description fields. For performance reasons, it is recommended that you make this modification against an empty database. The SQL to create the index is as follows:
CREATE FULLTEXT INDEX namedescription ON products (name,description);If you have a MySQL control panel (e.g. phpMyAdmin or Webmin) on your server, the easiest way to run this query is to enter it directly into the SQL form for your Price Tapestry database. If not, you can quickly write a script to execute the query. Run the following script from your Price Tapestry folder:
makeIndex.php
<?phpset_time_limit(0);
ignore_user_abort();
require("includes/common.php");
$sql =
"CREATE FULLTEXT INDEX namedescription
ON ".$config_databaseTablePrefix."products (name,description)";
database_queryModify($sql,$result);
print "Done.";
?>
Step 2: Modify search.php to use the new index
This modification is straight forward, and it is easiest to use the SEARCH & REPLACE function of your text editor to make the changes. The existing SQL that is used to search against the full text index on the name field only is as follows:
MATCH nameTo modify search.php, all you need to do is change the above code to:
MATCH (name,description)There are 3 instances of the above code that you need to replace. MySQL will automatically look for a combined full text index on the fields listed inside the brackets, so as long as you have previously completed step 1 of these instructions your site will now include the description field for all searches.