Hi Guys!
Pretty new to this script, only got it yesterday.
Been doing some modifications, and have been looking and looking and cant seem to find what I am looking for.
The script currently compares the product names, and matches if they are identical. This poses a small problem for me as most the stores I wish to list dont always have identical product names.
I would like to add product model to the database, which is entered during the import process - this shouldnt be too difficult to get right.
What I am battling with at the moment though - is WHERE does the script actually do the comparison? I presume I could add a simple or model = model to the sql query somewhere.
Looked high and low through the code - anybody able to point me in the right direction.
Example - http://www.ebuyer.co.za/product/Symantec-Gateway-Security-300-MN-Series-80211BG-VPNSGSWLAN.html is the same as http://www.ebuyer.co.za/product/My-Symantec-Gateway-Security-300-MN-Series-80211BG-VPNSGSWLAN.html except the name is different. The model numbers are the same so would like to compare on model number if the name doesnt match!
Thanks for any help I can get!
Hiya!
Wow - thanks so much for pointing me to the right direction.
Not too familiar with the "match against" queries in sql - will get there as my OR statement doesnt seem to work - but once I have it right i'll post it for everyone else.
In the meantime matching products according to product code is no problem at all - so if there is anyone else out there looking for this sort of thing, this is what i did (does need room to improve)
$prodcode = "SELECT code FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY price LIMIT ".$config_resultsPerPage;
$query_model = database_querySelect($prodcode, $rows1);
foreach($rows1 as $k1 => $row1)
{
$models = $row1["code"];
break;
}
if (strlen($models) > 3)
{
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' or code = '" . $models . "' ORDER BY price LIMIT ".$config_resultsPerPage;
}
else
{
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY price LIMIT ".$config_resultsPerPage;
}
Good luck! Thanks once again! ;)
Hi
Do these instructions above still stand, for adding a field and changing the field that price tapestry matches products to compare?
Hi Clare,
The basic idea should work, however there is a lot of work involved in adding a new field as changes need to be made to both registration and import. There's help for adding new fields in the following thread...
http://www.pricetapestry.com/node/313
Cheers,
David.
Could I just add another field to be indexed using the extras option?
So that the only changes needed are those mentioned in this thread?
Hi Clare,
Unfortunately not i'm afraid. The Extras feature only appends field contents to the description; they do not get imported separately.
A couple of other users have recently requested the ability to compare prices by a unqiue article number / product code etc. as some merchants are beginning to provide this information so this is something I am considering.
Cheers,
David.
Hi,
I am trying to follow the above instructions and have added the field I want to use for comparing to the feed and products tables.
So I have new fields "compare" and "field_compare" and all the admin stuff for that works fine.
I dont quite follow what changes I have to make to search.php. I see the part you refer to, the default Case part, but cant work out where to put the code that was written and what to remove or change in that bit.
switch($parts[0])
{
case "merchant":
// pass through to category
case "category":
// pass through to brand
case "brand":
$where = " ".$parts[0]."='".database_safe($parts[1])."' ";
if (isset($parts[2])) if ($parts[2]) $where .= "AND search_name LIKE '%".database_safe(tapestry_search($parts[2]))."%'";
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE ".$where;
$orderBySelection = $orderByDefault;
break;
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[1])."%' GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE search_name LIKE '".database_safe($parts[1])."%'";
$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])."%'";Would you be able to show me which part of this section of code I have to change to refer to my compare field I have added to use instead of the name field?
Hi Clare,
Can you confirm how you are expecting this to work...
Presumably; your new field "compare" contains a product reference number that a user of the site is not going to know and would not be searching for directly.
Therefore; what you need to happen is:
1) User enters keyword "widget"
2) An preliminary SQL query finds the most relevant product
3) The main SQL query (that provides the results) then searches for all products with the same "compare" field as returned by the first query.
Does this sound about right?
Cheers,
David.
Hi,
Yes that is exactly what I was trying to do. It is because the feeds I have at the moment I have had a hard time getting the product names to be the same as well as being descriptive, so this way I can just put in the compare field the model number whatever and the actual name field can contain more keywords etc.
With other feeds it may not be so important, but with the electrical goods, it is quite hard matching the names of the same items from different merchants. I wanted the name itself to be more than a number, just for SE purposes.
Hi Clare,
Try something like this in your default section (from the above code):
default:
if (strlen($parts[0]) > 3)
{
$sqlCompare = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE MATCH name AGAINST ('".database_safe($parts[0])."') LIMIT 1";
database_querySelect($sqlCompare,$rows)
$compare = $rows[0]["compare"];
$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 compare = '".database_safe($compare)."' OR MATCH name AGAINST ('".database_safe($parts[0])."') GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE compare = '".database_safe($compare)."' OR MATCH name AGAINST ('".database_safe($parts[0])."')";
$orderBySelection = $orderByFullText;
}
else
{
$sqlCompare = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE MATCH search_name LIKE '%".database_safe($parts[0])."%' LIMIT 1";
database_querySelect($sqlCompare,$rows)
$compare = $rows[0]["compare"];
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE compare = '".database_safe($compare)."' OR search_name LIKE '%".database_safe($parts[0])."%' GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE compare = '".database_safe($compare)."' OR search_name LIKE '%".database_safe($parts[0])."%'";
}Within each section (where the query is either greater or less than 3 characters - you need to handle both cases) I have added 3 lines that select the first product, and extract the value of the "compare" field into $compare. That field is then used in the following 2 SQL statement constructions to add a condition to the WHERE clause to match "compare" as well as the product name...
Hope this helps,
Cheers,
David.
I also changed line 12 of products.php to compare instead of name.
<?php
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE compare = '".database_safe($q)."' ORDER BY price LIMIT ".$config_resultsPerPage;
?>
Using the above code, I get the following error
Parse error: syntax error, unexpected T_VARIABLE in /**/**/**/shopUK/search.php on line 67
That is the seventh line of the code I inserted where it says
<?php
$compare = $rows[0]["compare"];
?>
Did I need to change anything in search.php other than the default section, this is the full page I have for search.php
[snipped]
Hi Clare,
There's nothing obviously wrong with your modification to products.php, however an unexpected T_VARIABLE error quite often indicates an error on a preceeding or subsequent line; for example a missing semi-colon.
If you're still getting the syntax error; feel free to email me your modified products.php as an attachment and i'll take a look.
With regards to search.php; you shouldn't have to make any other modifications. If it's not working as you would expect let me know what results are being returned vs. what you are expecting and i'll try and help (i've taken a copy of your modified search.php)...
Cheers,
David.
Thats the thing I was not getting any results with that search.php I posted, just the error on the line I mentioned above.
So I looked and saw as you said the line above had no ;
Added a ; to line 66
Added a ; to line 75
I removed the break from line 83
removed the final } on line 156
These were the errors returned when in browser. So I changed them and now it does search, but isnt returning relevant results nor comparing those with same compare field.
For example at www.findallsorts.com/shopUK/
I have uploaded 2 datafeeds that have similar products. One product that is in both is the "Newworld 5051FF Integrated Fridge Freezer", it has the same value for both feeds in the compare field, just slightly different names fields in both feeds. The other feed the name field is "Newworld 5051FF Built In Integrated Fridge Freezer". Both have the value "Newworld5051FF" in compare field, which is what I wanted the comparisons to go by.
But if I search for newworld fridge freezer, I get all sorts of results and no comparisons.
I made the changes to the search.php I posted based on the error messages I got fom the browser, I cannot see anything wrong with products.php, I simply changed the word name, to compare in that line of the code, so I am thinking my errors must be in search.php.
Hi Clare,
A couple of points here.
Firstly, I notice that your products.php is still not showing the product. The problem here is that the link to the product page is created on the "name" field, but you are then trying to select based on the "compare" field.
A two step process would therefore be required as well in products.php, essentially the same trick that you are using in search.php, so where you have:
<?php
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY price LIMIT ".$config_resultsPerPage;
$numRows = database_querySelect($sql,$rows);
?>
...change this to:
<?php
$sqlCompare = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = ".database_safe($parts[0])."' LIMIT 1";
database_querySelect($sqlCompare,$rows)
$compare = $rows[0]["compare"];
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE compare = '".database_safe($compare)."' ORDER BY price LIMIT ".$config_resultsPerPage;
$numRows = database_querySelect($sql,$rows);
?>
With regards to search, does every product definitely have a compare field? I'm wondering if the initial search on the name is returning a record that does not have a compare field; and it is then selecting records where compare is empty?
What will help you is to print out the SQL that is being generated in search.php. You want to be looking at the second statement generated (using the compare field). To do this, add the following line after $sql = ....
<?php
print "[".$sql."]";
?>
I use the square brakets when debugging SQL as it lets you see exactly where the query starts and ends. If you could let me know when you've added this i'll have a look at your site again and study the SQL to try and identify what's going on...
It would also help to be able to see the value of the compare field. You could add it to html/searchresults.php - where it prints the description:
<p><?php print substr($product["description"],0,250); ?></p>...change to:
<p><?php print substr($product["description"],0,250)." [".$product["compare"]."]"; ?></p>Again, square brackets being used to make the debug info stand out...
Cheers,
David.
Hi,
I added the compare value to the search results and the value isnt returned, just the word "compare" in square brackets. Every product that I uploaded does have a value in the compare field.
www.findallsorts.com/shopUK/search.php?q=fridge&x=0&y=0
I wasnt quite sure where to put the print statement to show the sql being generated in search.php. I put it after the second $sql statement so that it read
<?php
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE compare = '".database_safe($compare)."' OR search_name LIKE '%".database_safe($parts[0])."%' GROUP BY name";
print "[".$sql."]";
?>
But am not seeing anything from it.
The search is now producing relevant results, it just isnt comparing and clicking on "more information" returns alot of errors.
Hi Clare,
If the variable $product["compare"] is returning the word "compare" then it seems that this must be the value that is in the compare field of every product. This would indicate an error in the way the field is being loaded during the import, so i'd need to see that to understand what is going on. Perhaps you can email me your modified includes/admin.php and i'll check over your import code.
Have you confirmed that the database does hold the correct values by looking at the products table directly using a tool like phpMyAdmin?
Cheers,
David.
Hi Nick,
There are 2 places where SQL is generated that combine products by their name - firstly in the search results script and secondly on the individual product pages.
In search.php, you will find a CASE statement beginning at line 34 which constructs the appropriate SQL depending upon the type of search being requested.
If you follow the code down to the default case; you will see an IF statement that checks the length of the query. If it is greater than 3 characters, an SQL statment using the FULL TEXT index is constructed, otherwise a normal text match is done (but note the use of the "searchname" field which is stripped of any spaces).
If you are going to make changes to the WHERE clause of $sql, you must make the same changes to the WHERE clause of $sqlResultCount; otherwise the result count will not be correct!
Secondly, on line 12 of products.php you will see the code to construct the SQL that selects all products with the same name for display on the product page.
Hope this helps!
David.