Hi All, I am new to the forum and to Pricetapestry. Have been searching for a product like this for a long time and I like what I see so far!
I have installed and have one or two small issues:
Description Search
Is it possible to do a search and include the description fields as well as the product title? What I am trying to do is create predefined searches and, for example, a search for "Gadget" returns only products with that word in the product name but I want to retur products with that word in the description.
Register.php
I've managed to automate downloading the feeds. I am finding difficulty in automating registering the feeds. On my windows box I am using the following to attempt to register an aff window feed:
path/php "path/register.php" Merchant.csv "csv|44|1|34" "Merchant Name" "name" "description" "imageURL" "deeplink" "searchPrice" "masterCategory" "" "brand" ""
This appears to register the feed because the filters, extras and import links become available after running the script. Import seems to work but no products get listed.
Any help appreciated,
Regards,
Mike
David,
Thanks very much for the quick reply, I'll look at using xml later tonight.
It would be excellent to be able to search the descriptions. I can see how it could be a heavy task though! Maybe some sort of "excluded words" list might help.
Regarding two letter searches. The word "TV" is a very important keyword for shopping sites. I notice that several search engines can't even recognise the word!
Thanks again,
Mike
I have done an implemenation of this..
I created a full text index on name, description, brand and category and included these columns in search.php for products..
The imports are a bit slower, but the selects are about the same, doesnt consume much extra cpu at all.
/Olov
Hello David! I have the same question as Mike. I'd like the search engine to search both the product name AND the description field. Have you had the opportunity to test this yet?
The thing is that some merchants don't actually have a product name field in the datafeed at all. Instead, one of my merchants has two fields: "product_brand" and "product_model".
Take a look at this (yes I know, I have modified your script quite a bit):
http://www.electronicsstores.com/product/DCR-DVD105.html
As you can see I had to map the "product_model" field as the product name. The actual product name ("Sony DVD Handycam Camcorder") is mentioned in the product description field. Thus when searching for "Sony handycam" you will get no search results.
Please let me know if you can help me with this.
Thanks heaps!
Johan
Hello Johan,
In the case of a feed that contains separate fields for manufacturer and model, the trick is to register the manufacturer field as the product name, and then create a "Text After" filter to append the model number to manufacturer to create an overall unique product name.
To do this, register product_manufacturer as the product name, and then create the following filter against the feed:
Filter Type: Text After
Field: Product Name
Text: " %product_model%"
(excluding the quotes, and note the leading SPACE so that the model number is separated from the manufacturer name)
Note that if it is an XML feed that you are working with then product_model may have to be in upper case. In any case, it just has to be exactly as it is displayed on the first record preview that you see when registering a new feed).
Great looking site by the way!
Hope this helps!
David.
I'm glad you like the site! Far from finished though.
The filter didn't work quite as expected. The quotation marks show in the product names (for example: Denon" DVD 3910B"). I tried to remove the quotation marks from the filter, but then the products with a space in the "product_model" field (for example: DVD 3910B) became inaccessable. The product detail page just displayed an empty screen.
Products with no hyphens in the product model field show up just fine.
I'm lost;)
Hi,
Was just wondering if anything more has been done about this search being turned on for the description field. Has anyone got the code or hack for this so that we can do some testing of our own.
Thanks
Regards
David
www.buyone.at
www.tinyrascals.co.uk
Hi David,
The code modification to include the description field in the search is straight forward - you just need to add ",description" to the 2 SQL statments starting at line 65 of search.php, giving you the following code:
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH (name,description) AGAINST ('".database_safe($parts[0])."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH (name,description) AGAINST ('".database_safe($parts[0])."') GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE MATCH (name,description) AGAINST ('".database_safe($parts[0])."')";However, that won't work until you have created a combined FULL TEXT index on the name and description fields. You can do it from the mysql command line tool by logging into your server via Telnet or SSH, and doing something like this:
$mysql -u <username> -p
Enter Password: <password>
mysql> use <price-tapestry-database>
mysql> create fulltext index ft_namedescription on products (name,description);
mysql> quit
Bye.
$If you have hundreds of merchants / products; you may find it will take a long time to create the new index - in which case you would be better off creating the index on an empty database and them re-importing all products.
Hope this helps!
Cheers,
David.
Hello David,
is it possible to make two search boxes?
One for title search and one for title&description search?
Cheers, Peter
Hi Peter,
It should be possible to make a description search box. Firstly, you'd want to create a full text index on the description field instead of the combined name,description as described above. The MySQL commands would be as follows:
$mysql -u <username> -p
Enter Password: <password>
mysql> use <price-tapestry-database>
mysql> create fulltext index ft_description on products (description);
mysql> quit
Bye.
$If you cannot access your server to do this, you can write a script to do it instead:
makeIndex.php:
<?php
set_time_limit(0);
ignore_user_abort();
require("includes/common.php");
require("includes/database.php");
require("includes/header.php");
$sql = "create fulltext index ft_description on products (description)";
database_queryModify($sql,$rows);
print "<p>Done.</p>";
require("includes/footer.php");
?>
Run the script above in the top level directory of your Price Tapestry installation and that will build the index - much faster if the database is empty of course (but it only needs to be done once).
Let me know when you're ok with the index, and i'll help you with the code to make a description search box...
Cheers,
David.
Hello David,
i made the full text index on the description field.
My search.php...
case "bw":
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH (name,description) AGAINST ('".database_safe($parts[0])."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH (name,description) AGAINST ('".database_safe($parts[0])."') GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE MATCH (name,description) AGAINST ('".database_safe($parts[0])."')";
$orderBySelection = $orderByDefault;
break;
default:
if (strlen($parts[0]) > 3)
{
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH (name,description) AGAINST ('".database_safe($parts[0])."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH (name,description) AGAINST ('".database_safe($parts[0])."') GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE MATCH (name,description) AGAINST ('".database_safe($parts[0])."')";
$orderBySelection = $orderByFullText;
}
else
{
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH (name,description) AGAINST ('".database_safe($parts[0])."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH (name,description) AGAINST ('".database_safe($parts[0])."') GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE MATCH (name,description) AGAINST ('".database_safe($parts[0])."')";
$orderBySelection = $orderByDefault;and my index.php...
print "<div align='center'>";
print "<div style='height:30px;'> </div>";
require("html/searchform.php");
print "<font size='1'>Die Suche kann auf Grund der Anzahl der Produkte etwas länger dauern</font>";
print "<small>";And in my database i have now:
name_2-FULLTEXT-1-name
ft_namedescription-FULLTEXT-1-name description
ft_description-FULLTEXT-1-description
Thanks a lot, Peter
Hi Peter,
I'm not sure what's going on in your search.php, as normally the default case contains this:
<?php
default:
if (strlen($parts[0]) > 3)
{
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($parts[0])."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH name AGAINST ('".database_safe($parts[0])."') GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE MATCH name AGAINST ('".database_safe($parts[0])."')";
$orderBySelection = $orderByFullText;
}
else
{
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE search_name LIKE '%".database_safe($parts[0])."%' GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE search_name LIKE '%".database_safe($parts[0])."%'";
$orderBySelection = $orderByDefault;
}
break;
?>
Notice how the second section (if the search string is not more than 3 characters) uses the normal index. In your code, the full text indexing seems to be being used in both - is that intentional?
How do you want to integrate the "description only" search into your site? I think the easiest way would be to create a new script; otherwise there will need to be modifications to all the following on links on the page (sort order, next, prev etc.).
What way are you looking to present this to your users?
One nice option might be rather than 2 search boxes, have a drop down box alongside the text box that has the options of "Name" or "Name and Description". What do you think of doing that?
Cheers,
David.
Hello David,
now i changed my search.php to the original code.
case "bw":
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE search_name LIKE '%".database_safe($parts[0])."%' GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE search_name LIKE '%".database_safe($parts[0])."%'";
$orderBySelection = $orderByDefault;
break;
default:
if (strlen($parts[0]) > 3)
{
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($parts[0])."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH name AGAINST ('".database_safe($parts[0])."') GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE MATCH name AGAINST ('".database_safe($parts[0])."')";
$orderBySelection = $orderByFullText;
}
else
{
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE search_name LIKE '%".database_safe($parts[0])."%' GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE search_name LIKE '%".database_safe($parts[0])."%'";
$orderBySelection = $orderByDefault;
}
break;And your idea with the drop down box is great.
Thanks a lot, Peter
Hi Peter,
Probably best to get this working in stages....!
Firstly, the search form. To add the drop down box (to feed a parameter called "index" through to search.php, modify html/searchform.php as follows:
<div class='searchform'>
<form name='search' action='<?php print $config_baseHREF ?>search.php'>
<input type='text' name='q' size='35' value='<?php print (isset($q)?$q:""); ?>' />
<select name='index'><option>
</option value='name'>Name</option>
</option value='name,description'>Name and Description</option>
</select>
<input type='submit' value='<?php print translate("Search"); ?>' />
</form>
</div>The will send a parameter index= through to search.php. Now, just to make sure things work - it's simply a case of changing the SQL in search.php to contain the value of $_GET["index"].
Firstly, at the top of the script, add the following line:
$index = (isset($_GET["index"])?$_GET["index"]:"name");Secondly, modify the SQL in the section that uses the full text index to use the value of $index where required:
if (strlen($parts[0]) > 3)
{
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH (".database_safe($index).") AGAINST ('".database_safe($parts[0])."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH (".database_safe($index).") AGAINST ('".database_safe($parts[0])."') GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE MATCH (".database_safe($index).") AGAINST ('".database_safe($parts[0])."')";
$orderBySelection = $orderByFullText;
}Notice that this only applies to that small section of code where the query is > 3 characters, as this is the only part that uses the full text index.
Cheers,
David.
Great job David, but the description search don´t work.
Only the name search is ok.
If i search in description i get following errors:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/.sites/116/site454/web/includes/database.php on line 21
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/.sites/116/site454/web/includes/database.php on line 26
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/.sites/116/site454/web/includes/database.php on line 21
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/.sites/116/site454/web/includes/database.php on line 26and nothing will found.
Here my includes/databse.php line 21 to 27
while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
$rows[] = $row;
}
return mysql_num_rows($result);
}But i think there is not the mistake.
So i post the search.php
$index = (isset($_GET["index"])?$_GET["index"]:"name"); case "bw":
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE search_name LIKE '%".database_safe($parts[0])."%' GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE search_name LIKE '%".database_safe($parts[0])."%'";
$orderBySelection = $orderByDefault;
break;
default:
if (strlen($parts[0]) > 3)
{
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH (".database_safe($index).") AGAINST ('".database_safe($parts[0])."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH (".database_safe($index).") AGAINST ('".database_safe($parts[0])."') GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE MATCH (".database_safe($index).") AGAINST ('".database_safe($parts[0])."')";
$orderBySelection = $orderByFullText;
}
else
{
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE search_name LIKE '%".database_safe($parts[0])."%' GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE search_name LIKE '%".database_safe($parts[0])."%'";
$orderBySelection = $orderByDefault;
}
break;
}You can test it on my page HERE
I only change the name and description to my language.
The first ist the name search and the second ist the description search.
Cheers, Peter
Hello David,
i think it´s my mistake, because i delete the
ft_namedescription-FULLTEXT-1-name description
i thought that the two...
name_2-FULLTEXT-1-name
ft_description-FULLTEXT-1-description
are enough.
Should i made the ft_namedescription-FULLTEXT-1-name description
again?
I hope not, beause it takes a lot of time :)
Thanks, Peter
Hi Peter,
Yes - i'm afraid it does need the ft_namedescription as it is a combined index. When the SQL says "MATCH (name,description)", MySQL looks for an index that has been built with those fields. If one does not exists then it generates an error.
Also, please ignore my last post [i've deleted it now] as that information was incorrect.
You are right - this will be why it isn't working!
Cheers,
David.
David,
It doesn`t work for me. Could you help me?
It only searches for "name" in "name" and "name and description" field.
Thanks,
Gabriel
Hello Gabriel,
I think this is related to what you have done in this thread:
http://www.pricetapestry.com/node/843
The full text index is only used in the first case, so because you have made this only work if the query is longer than 30 characters none of the changes described here will have any effect (unless the query is longer than 30 characters, but that is unusual)
What I would suggest is lowering the limit again and doing some test queries to see where you would like to have the limit set. Maybe 4 or 5 characters would work better in your scenario; meaning that single words would be searched for using the basic method, and longer queries using the full text index.
In fact, thinking about it this might lead to a good all-round compromise; and something i'd not considered before. Instead of seeing if the query is longer than 3 characters, instead look to see if there is a SPACE in the query. To do this, in search.php (line 63), change:
if (strlen($parts[0]) > 3)to:
if (strpos($parts[0]," "))Hope this helps!
Cheers
David.
Hi
I have just installed the latest version, does this hack for desc. search work with the new version.
Thanks
Hi,
Yes - these mods will work fine with the latest version - there haven't been any changes to the core search functionality for some time.
Cheers,
David.
After updated with the makingindex.php the search result show me for more than 3 words the following error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /www/htdocs/hegbtnfd/wohh-shop/includes/database.php on line 21
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /www/htdocs/hegbtnfd/wohh-shop/includes/database.php on line 26
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /www/htdocs/hegbtnfd/wohh-shop/includes/database.php on line 21
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /www/htdocs/hegbtnfd/wohh-shop/includes/database.php on line 26
Warning: Cannot modify header information - headers already sent by (output started at /www/htdocs/hegbtnfd/wohh-shop/includes/database.php:21) in /www/htdocs/hegbtnfd/wohh-shop/html/header.php on line 12
Although the makinindex show me the following error:
Fatal error: Cannot redeclare database_queryselect() (previously declared in /www/htdocs/hegbtnfd/wohh-shop/includes/database.php:3) in /www/htdocs/hegbtnfd/wohh-shop/includes/database.php on line 3
What can I do now?
Hi,
This sounds like whilst you have added the index, you still need to make the corresponding code changes so that the full text search (where the query is greater than 3 characters) uses the new index. The better instructions to follow are in the following thread (see second post):
http://www.pricetapestry.com/node/1224
Hope this helps,
Cheers,
David.
Hi Mike,
In reverse order;
I've just recreated the problem with an Affiliate Window CSV feed, and it would seem that every masterCategory value contains a new line on the end of the value. Consequently, whilst the file format is autodetected correctly the records are not actually valid. This appears to be happening for all merchants.
In the mean time, I would recommend using the XML feed format with Affilaite Window and wherever else an XML feed is available as an alternative to CSV as they are much more robust to work with. The command line registration for an AW XML feed is pretty much the same, except for the product name field (PRODUCTNAME in XML), and they're all in CAPS:
path/php "path/register.php" Merchant.xml "xml|PRODUCTS/PRODUCT/" "Merchant Name" "PRODUCTNAME" "DESCRIPTION" "IMAGEURL" "DEEPLINK" "PRICE" "MASTERCATEGORY" "" "BRAND" ""With regards searching the description field; this is certainly possible but may have performance issues. It would need a FULLTEXT index creating on the combination of product name and description; and then the SQL modified to use the combined index rather than just the product name. I'm not sure of the code for this off the top of my head, so i'll have a go on my test server and get back to you with the mods if it works as expected. It would be limited to queries of words greater than 3 characters (required for the FULLTEXT index method); otherwise there would be too much of a performance hit.
Bear with me and i'll investigate the practicalities.
Cheers,
David.