Advanced Search
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.
--
Developer, Price Tapestry
For unrelated PHP, MySQL or Affiliate Marketing tech help please post your questions on my personal forum
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.
--
Developer, Price Tapestry
For unrelated PHP, MySQL or Affiliate Marketing tech help please post your questions on my personal forum
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
SQL QUERY [SELECT COUNT(DISTINCT(name)) as resultcount FROM `products` WHERE MATCH name,description,brand AGAINST ('Ibico 208X Calculator Desktop Tilt screen Currency Solar and Battery Power 8 Digit Ref IB410062')] FAILED []
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/sites/example.co.uk/public_html/includes/database.php on line 25
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/sites/example.co.uk/public_html/includes/database.php on line 30
SQL QUERY [SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name,description,brand AGAINST ('Ibico 208X Calculator Desktop Tilt screen Currency Solar and Battery Power 8 Digit Ref IB410062') AS relevance FROM `products` WHERE MATCH name,description,brand AGAINST ('Ibico 208X Calculator Desktop Tilt screen Currency Solar and Battery Power 8 Digit Ref IB410062') GROUP BY name ORDER BY relevance DESC LIMIT 0,10] FAILED []
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/sites/example.co.uk/public_html/includes/database.php on line 25
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/sites/example.co.uk/public_html/includes/database.php on line 30
Warning: Cannot modify header information - headers already sent by (output started at /home/sites/example.co.uk/public_html/includes/database.php:19) in /home/sites/example.co.uk/public_html/html/header.php on line 6This 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.
--
Developer, Price Tapestry
For unrelated PHP, MySQL or Affiliate Marketing tech help please post your questions on my personal forum
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.
--
Developer, Price Tapestry
For unrelated PHP, MySQL or Affiliate Marketing tech help please post your questions on my personal forum
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.
--
Developer, Price Tapestry
For unrelated PHP, MySQL or Affiliate Marketing tech help please post your questions on my personal forum
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.