Hello,
I've added the field "sku", and it works in PT.
I've figured to compare by SKU, I'd change (in products.php):
<?php
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY price LIMIT ".$config_resultsPerPage;
?>
...to:
<?php
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".$row["name"]."' ORDER BY price LIMIT ".$config_resultsPerPage;
?>
But if a merchant didn't supply a SKU, then this wouldn't work correctly. So, I thought maybe I could compare initially by product name and then by a SKU (if found).
For example, I have 3 products (that are the same, from 3 different merchants):
TITLE1: Apple Video iPod 30gb
SKU1: ?
TITLE2: Apple Video iPod 30gb
SKU2: 55555555
TITLE3: Apple iPod 30 gb
SKU3: 55555555
Would it be possible to compare the 1st 2 by title, and then find Product3 by SKU2?
...And then after that compare all three (and sort by price)?
Then at the end we'd find all three merchants on one product page comparison (for example):
MERCHANT1 Apple Video iPod 30gb $200.00
MERCHANT2 Apple Video iPod 30gb $250.00
MERCHANT3 Apple iPod 30 gb $300.00
This may be very hard to do, but it would be a great improvement to PT.
Thank you for all your support,
Michael
Thank you,
It works great. If I understand this correctly, it would also be possible to add an "upc" field, and then change it to:
<?php
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' LIMIT 1";
database_querySelect($sql,$rows);
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($rows[0]["name"])."' OR sku = '".database_safe($rows[0]["sku"])."' OR upc = '".database_safe($rows[0]["upc"])."' ORDER BY price LIMIT ".$config_resultsPerPage;
$numRows = database_querySelect($sql,$rows);
?>
Using that system, I should be able to also add MPN and other product codes.
Thanks again David,
Michael
PS: Should I also edit the "search" page?
Hi Michael,
Yes - you can add other match fields into the second query; but keep an eye on performance as this will not be using an index and so will invoke full table scans.
The search page is tricker because the products have to be grouped straight out of the database; and so this grouping has to be based on one field. If you have sufficient sku's in your database you could try changing the "GROUP BY name" to "GROUP BY sku" (there are 2 instances of GROUP BY in search.php).would be required...
Cheers,
David.
I like to use this one but there is a problem, when there is no sku in the database it gives an error / no compare.
Thx
Henk
Hello Henk,
Having made this change, is it just the product page that you are having a problem with?
I presume that you are getting satisfactory search results?
This will need a modification to make this work if only some of your products have an SKU, but I want to make sure your search is working first...
Cheers,
David.
The search is working ok only the product and comparing doesn't work.
Thx
HEnk
Hello Henk,
The code above should still work when there is no SKU, because it matches by name also. Can you email to me a link to the page with no results on it, and a copy of your products.php and I will have a look...
Cheers,
David.
Sry but i try to install this one again and i have the same error, it doesnt compare the product with no sku.
link
Thx Henk
Hello Henk,
Products with no SKU will only be compared if their product name exactly matches the main product name. Is this not the case?
The page should show every product called "After dinner workshop", or every product that has the same SKU as the cheapest product selected called "After dinner workshop", providing that the cheapest product has an SKU field...
Feel free to email me your modified products.php and i'll check the changes for you...
Cheers,
David.
Hi,
Its make an error on the products pages when i make this change. please check the link i will send you the products.php.
Thx
Henk
Hi,
I have some trouble with sku 0000000000000 is it possible to exclude this sku
Thx Henk
Hello Henk,
This is probably because of a comparison - if ($sku), so it is evaluating to zero.
Can you email me a link to a page showing the problem so that I understand it properly and can show you the best way to exclude it...
Cheers,
David.
I see how the comparison by 2 ways work, but how do you search 2 ways by name or sku? If I enter a sku into search bar no results are showed. It only works with the product name.
Hi,
This change only affects the product comparison page - as it stands (as you have discovered), SKU is not a field that is searched. The easiest ways to do this (without having to add the SKU field to the full text index) would be to create a search modifier like brand: or category:. To do this, look for the following code starting at line 36 of search.php:
case "merchant":
// pass through to category
case "category":
// pass through to brand
case "brand":
$where = " ".$parts[0]."='".database_safe($parts[1])."' ";...and REPLACE this with:
case "merchant":
// pass through to category
case "category":
// pass through to brand
case "brand":
case "sku":
$where = " ".$parts[0]."='".database_safe($parts[1])."' ";With that in place, you could search for:
sku:12345678Cheers,
David.
Hi David
Is this also possible with pricesexternal script.
Thx
Henk
Hello Henk,
Sure, the equivalent change in pricesExternal.php would be as follows; look for the following code at starting at line 62:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($q)."' ORDER BY price";
$numRows = database_querySelect($sql,$rows);...and REPLACE with:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($q)."' LIMIT 1";
database_querySelect($sql,$rows);
if ($rows[0]["sku"])
{
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($rows[0]["normalised_name"])."' OR sku = '".database_safe($rows[0]["sku"])."' ORDER BY price LIMIT ".$config_resultsPerPage;
}
else
{
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($rows[0]["normalised_name"])."' ORDER BY price";
}
$numRows = database_querySelect($sql,$rows);Cheers,
David.
--
PriceTapestry.com
Hmm, i dont have any result on sku, name is working but sku didn't work.
Thx Henk
Hello Henk,
Ah - if you wanted to be able to pass an SKI value to pricesExternal.php that would require a slightly different modification; so in place of the previous suggestion, please use:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($q)."' OR sku = '".database_safe($q)."' LIMIT 1";
database_querySelect($sql,$rows);
if ($rows[0]["sku"])
{
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($rows[0]["normalised_name"])."' OR sku = '".database_safe($rows[0]["sku"])."' ORDER BY price";
}
else
{
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($rows[0]["normalised_name"])."' ORDER BY price";
}
$numRows = database_querySelect($sql,$rows);Cheers,
David.
--
PriceTapestry.com
Yes it works now with Joomla and virtue mart :)
Thx
Henk
No problem Henk,
I will let you know when Joomla plugin beta is available.
Cheers,
David.
--
PriceTapestry.com
Hi Micheal,
It should work by selecting first by product name; and then by product name _and_ SKU in the second query. This is what another user who has added a product code field does and they are happy with the results. The existing code in products.php is as follows:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY price LIMIT ".$config_resultsPerPage;$numRows = database_querySelect($sql,$rows);
You need to modify this along the lines of the following; replacing "sku" with whatever field name you are using in your database:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' LIMIT 1";database_querySelect($sql,$rows);
if ($rows[0]["sku"])
{
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($rows[0]["name"])."' OR sku = '".database_safe($rows[0]["sku"])."' ORDER BY price LIMIT ".$config_resultsPerPage;
}
else
{
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($rows[0]["name"])."' ORDER BY price LIMIT ".$config_resultsPerPage;
}
$numRows = database_querySelect($sql,$rows);
That should be all there is to it;
Regards,
David.