Hi, I bought a module called "Price comparison listing" for Prestashop that was supposed to help me push product listing to kelkoo. Unfortunately it doesnt work and the developers don't fix, and they don't refund.... I have reported this to prestashop, but I need a solution..
Be careful buying anything from prestashop.. This was a community addon, but on prestashop webshop.
Problem is this sql statement;
$productsSQL = 'SELECT p.additional_shipping_cost, p.on_sale, m.name AS manufacturer,p.reference,p.supplier_reference,p.weight,p.price, pl.description_short,p.id_tax_rules_group, p.id_product, p.quantity, pl.link_rewrite, cl.`link_rewrite` category, ean13, pl.name,
(SELECT id_image FROM '._DB_PREFIX_.'image WHERE `cover` = 1 AND id_product=p.id_product) AS id_image
FROM '._DB_PREFIX_.'product p
LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND pl.`id_lang` = cl.`id_lang`)
LEFT JOIN '._DB_PREFIX_.'manufacturer m ON p.id_manufacturer = m.id_manufacturer
WHERE p.`active` = 1 AND pl.`id_lang` = '.$id_lang.'
ORDER BY p.id_product ASC LIMIT '.$limitStart.','.(int)Tools::getValue('toSendEachIteration');
$products = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($productsSQL);
if($firstrow)
It works but will give you only the category of the product, not the entire category path that kelkoo demands.
The database looks like this;
It has a table called ps_category that contains, among other, these fields:
id_category id_parent level_depth
Above sql statement finds the id of the category, but should also look for id_parent (and level_depth?) to include top category level(s). I have maximum 3 levels, like "phone"->"mobile phone"->"iphone" where the statement above gives you only "iphone" and nothing else..
I don't know how to fix the above sql statement so it includes even top level categories, if there.
Any help would be highly appreciated since I'm kind of stuck here with a module that is unusable... but paid for..
---------- Post updated at 12:10 PM ---------- Previous update was at 09:13 AM ----------
I guess this is the part of the sql-statement that needs adjusting;
LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND pl.`id_lang` = cl.`id_lang`)
In table ps_category it should look for cl.`id_category` as above, but if `id_parent` on the same row is not = 0 then it should take id_parent and look that id up in id_category, because thats the toplevel of categories.
Now if that toplevel category also has `id_parent` not =0 it should again take that id and look it up in `id_category` because then the product has a three step path in categories.
Then it should merge those category steps into one name like:
categorylevel1.categorylevel2.categoryofproduct and use that as a value for cl.`id_category` instead of only categoryofproduct as it does now.
Does this make sense? Can you even do it with mysql?
Any help or input would be appreciated.
---------- Post updated at 01:25 PM ---------- Previous update was at 12:10 PM ----------
This is how far I come by my self:
SELECT p.additional_shipping_cost, p.on_sale, m.name AS manufacturer,p.reference,p.supplier_reference,p.weight,p.price, pl.description_short,p.id_tax_rules_group, p.id_product, p.quantity, pl.link_rewrite, cl.`link_rewrite` category, ean13, pl.name,
(SELECT id_image FROM ps_image WHERE `cover` = 1 AND id_product=p.id_product) AS id_image
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN `ps_category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND pl.`id_lang` = cl.`id_lang`)
*******************
Looking for toplevel category...
LEFT JOIN ps_category pc (ON cl.`id_category`= pc.`id_category`)
>>Trying to figure out if id_parent is bigger than 0 then look id_parent up...
IF pc.`id_parent` > 0 THEN
LEFT JOIN `ps_category_lang` cl ON (pc.`id_parent` = cl.`id_category` AND pl.`id_lang` = cl.`id_lang`)
Looking for top-top-level category...
LEFT JOIN ps_category pc (ON cl.`id_category`= pc.`id_category`)
Trying to figure out if id_parent is bigger than 0 then look id_parent up...
IF pc.`id_parent` > 0 THEN
LEFT JOIN `ps_category_lang` cl ON (pc.`id_parent` = cl.`id_category` AND pl.`id_lang` = cl.`id_lang`)
put all category levels together as such level1.level2.category and preferebly assign this to `category`in top SELECT so it fits with the rest of the script.
*******************
LEFT JOIN ps_manufacturer m ON p.id_manufacturer = m.id_manufacturer
WHERE p.`active` = 1 AND pl.`id_lang` = 6
ORDER BY p.id_product ASC
I hope it makes sense so someone can see what I try to do. Please help, I would hate to pay more money to make this work since I already payed for it but without being able to use it or get support from those scams selling this...