Hi David,
Hope you are well mate?
I am starting to work on one of my PT sites again in the run up for xmas.
I currently have the Amazon API working great.
What I would like to do, is for products which have say 3 top prices, all exactly the same, is to order them with Amazon as the top retailer in the list, instead of others.
The reasoning for this is generally Amazon give me more commission than a lot of the other retailers, so I would like to have them at the top.
So order would be by 'total price' followed by 'retailer'.
I do think a quick work around for this would be to order by 'total price' then by 'retailers in alphabetical order', as generally Amazon is my first retailer in this order.
Is this possible? and if so please could you advise on how to go about doing this?
Alternatively I could add a new field in the table to give a retailer a certain weight, then the table is ordered by 'total price' then 'retailer weight' with Amazon being '1'?
Cheers for any help!
Craig
Cheers David. Thats great, and I can see how that works... but I do have slightly different code to standard. I have tried a few ways of modifying it to suit, but have not succeeded.
My code is as follows:
$sql = "SELECT *,(price+category) AS totalprice FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY totalprice LIMIT ".$config_resultsPerPage;Thanks again for any help!
Craig
Hi Craig,
Based on your current code, REPLACE with;
$sql = "SELECT *,(price+category) AS totalprice,
CASE merchant
WHEN 'Amazon' THEN 1
WHEN 'Another Merchant' THEN 2
WHEN 'Yet Another Merchant' THEN 3
ELSE 999
END CASE as priority
FROM `".$config_databaseTablePrefix."products`
WHERE name = '".database_safe($q)."'
ORDER BY totalprice,priority";I removed the LIMIT clause as this is left over from a very early version of Price Tapestry - although unlikely to have ever caused a problem the above version will make sure that all merchants are compared, and not unnecessarily limited to $config_resultsPerPage, which is only meant to apply to search results!
Cheers,
David.
--
PriceTapestry.com
Hi David,
Thank you. I do actually think I may have tried that but it didn't work, so I assumed I'd done it wrong. I tried again with the code copied from above (and just the amazon WHEN left in), but get these errors:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /nfs/c02/h09/mnt/30554/domains/topkidstoys.org.uk/html/includes/database.php on line 21
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /nfs/c02/h09/mnt/30554/domains/topkidstoys.org.uk/html/includes/database.php on line 26
Warning: Cannot modify header information - headers already sent by (output started at /nfs/c02/h09/mnt/30554/domains/topkidstoys.org.uk/html/includes/database.php:21) in /nfs/c02/h09/mnt/30554/domains/topkidstoys.org.uk/html/html/header.php on line 6
Maybe I need to update my version of PT? Are there many improvements since the old version I am using?
Cheers,
Craig
Hi Craig,
Sorry about that; it should just be END, not END CASE - have a go with;
$sql = "SELECT *,(price+category) AS totalprice,
CASE merchant
WHEN 'Amazon' THEN 1
ELSE 999
END as priority
FROM `".$config_databaseTablePrefix."products`
WHERE name = '".database_safe($q)."'
ORDER BY totalprice,priority";Regarding updating the script, I don't recommend attempting to upgrade a heavily modified site - instead what I suggest is that if you would like to "back-port" any of the new features into an existing site let me know what features you require, and quite often they are related to a forum post describing the mod based on the original version of Price Tapestry; otherwise I can help with making the changes necessary. Changelog can be found here.
Cheers,
David.
--
PriceTapestry.com
That works a treat, thank you very much!! :)
I'll check out the changelog too.
Cheers,
Craig
Hi Craig,
It's straight forward to add merchant priority to the SELECT SQL in products.php. In that file, look for the following code at line 12:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($q)."' ORDER BY price";...and REPLACE with:
$sql = "SELECT *,CASE merchant
WHEN 'Amazon' THEN 1
WHEN 'Another Merchant' THEN 2
WHEN 'Yet Another Merchant' THEN 3
ELSE 999
END CASE as priority
FROM `".$config_databaseTablePrefix."products`
WHERE normalised_name = '".database_safe($q)."' ORDER BY price,priority";
Cheers,
David.
--
PriceTapestry.com