kingoftheworld_87 Posted August 25, 2010 Share Posted August 25, 2010 Iv been messing around with the CSV files and had finally done around 3000 products when I realised I have made a mistake.I have only referenced products to the sub categories so they only come up in that specific area.For example if you click on Kitchen>>>Accessories>>>Crockery you will get the kitchen crockery, but if you click on kitchen or accessories non of these products will show up, id like all sub category products to show up.Is there a way to do this without inserting numbers into every field on the CSV?Thanks Link to comment Share on other sites More sharing options...
rocky Posted August 26, 2010 Share Posted August 26, 2010 It would be safer to edit your CSV files and add all the parent category IDs to each product. It is very difficult to write a query to copy products into their parent categories, but I've tried anyway. Back up your ps_category_product table before performing this query so you can restore it to the way it was it anything goes wrong. Here's the query I ended up with: SET @position = 0; INSERT INTO `ps_category_product` SELECT c.`id_parent`, cp.`id_product`, @position:=@position+1 as `position` FROM `ps_category_product` cp LEFT JOIN `ps_category` c ON (cp.`id_category` = c.`id_category`) WHERE `id_parent` > 1 AND cp.`id_product` NOT IN (SELECT `id_product` FROM `ps_category_product` WHERE `id_category` = c.`id_parent`) Change the four ps_ to your database prefix if necessary. This will copy all the products into their parent category, except for the ones whose parent category is the "Home" category. 3 Link to comment Share on other sites More sharing options...
kingoftheworld_87 Posted August 27, 2010 Author Share Posted August 27, 2010 I cant find the file, where is it located?thanks Link to comment Share on other sites More sharing options...
rocky Posted August 27, 2010 Share Posted August 27, 2010 It's not in a file, since it is an SQL query. You must log in to phpMyAdmin, use the "Export" tab to make a backup of your ps_category_product table, then use the "SQL" tab to run the SQL query. You'll need to run the query multiple times depending on how many levels of categories you have. Link to comment Share on other sites More sharing options...
trusktr Posted January 14, 2011 Share Posted January 14, 2011 rocky, i noticed above you said "except for the ones whose parent category is the “Home” category."... Well i have a question... how do you make this work for the "home" category as well?For example, in my "home" category page i see no products, but instead i'd like to see ALL products.http://bettafootwear.com/CrownYourFeet/shop/1-home Link to comment Share on other sites More sharing options...
rocky Posted April 28, 2011 Share Posted April 28, 2011 I just needed to copy all products into the home category for a client. To do it, I used the following query: DELETE FROM `ps_category_product` WHERE `id_category` = 1; SET @position = 0; INSERT INTO `ps_category_product` SELECT 1, `id_product`, @position:=@position+1 as `position` FROM `ps_product`; This will delete all featured products, then make all products featured products. Change all ps_ to your database prefix. Link to comment Share on other sites More sharing options...
trusktr Posted May 3, 2011 Share Posted May 3, 2011 Hey rocky, it seems that one of the Prestashop updates since my last post in this thread has solved this issue. I haven't made any modifications or executed any queries, but if you look here:http://bettafootwear.com/CrownYourFeet/shop/1-homeThen you'll see all my products, just like I wanted originally. Thanks for the suggestion though! Link to comment Share on other sites More sharing options...
Goodrig Posted May 24, 2011 Share Posted May 24, 2011 Many thanks, this query has been immensely useful!Bernhardwww.goodrig.fr Link to comment Share on other sites More sharing options...
reynoon Posted April 29, 2012 Share Posted April 29, 2012 This is very useful. Thank u so much! Link to comment Share on other sites More sharing options...
FERMB Posted November 7, 2014 Share Posted November 7, 2014 It would be safer to edit your CSV files and add all the parent category IDs to each product. It is very difficult to write a query to copy products into their parent categories, but I've tried anyway. Back up your ps_category_product table before performing this query so you can restore it to the way it was it anything goes wrong. Here's the query I ended up with: SET @position = 0;INSERT INTO `ps_category_product` SELECT c.`id_parent`, cp.`id_product`, @position:=@position+1 as `position` FROM `ps_category_product` cp LEFT JOIN `ps_category` c ON (cp.`id_category` = c.`id_category`) WHERE `id_parent` > 1 AND cp.`id_product` NOT IN (SELECT `id_product` FROM `ps_category_product` WHERE `id_category` = c.`id_parent`) Change the four ps_ to your database prefix if necessary. This will copy all the products into their parent category, except for the ones whose parent category is the "Home" category. Perfect !. Thank you very much. If the item is in the category of Level 3, just run the SQL repeatedly query and thus show items at each higher level, ie, to run it once, add the items that are on level 3 also at level 2 and run the sql query again, the show also at level 1. Perfecto!. Muchas gracias. Si el artículo están en categorias de nivel 3, basta con ejecutar la query SQL varias veces y así mostrará los artículos en cada nivel superior, es decir, al ejecutarlo una vez, añade los articulos que están en el nivel 3 tambien en el nivel 2 y al ejecutar la consulta sql de nuevo, los muestra tambien en el nivel 1. Link to comment Share on other sites More sharing options...
rt000v95 Posted February 11, 2016 Share Posted February 11, 2016 Hi, need help Error consulta SQL: INSERT INTO `ps_category_product` SELECT c.`id_parent` , cp.`id_product` , @position := @position +1 AS `position` FROM `ps_category_product` cp LEFT JOIN `ps_category` c ON ( cp.`id_category` = c.`id_category` ) WHERE `id_parent` >1 AND cp.`id_product` NOT IN ( SELECT `id_product` FROM `ps_category_product` WHERE `id_category` = c.`id_parent`)MySQL ha dicho: #1062 - Duplicate entry '2-442' for key 'PRIMARY' This is my problem Any Idea ? txs. Link to comment Share on other sites More sharing options...
deltahel Posted July 2, 2016 Share Posted July 2, 2016 Thanks rocky! Your solution has saved me more than once. Link to comment Share on other sites More sharing options...
Recommended Posts