Jump to content

How to display sub category products in main categories.


Recommended Posts

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

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.

  • Like 3
Link to comment
Share on other sites

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

  • 4 months later...
  • 3 months later...

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

  • 3 weeks later...
  • 11 months later...
  • 2 years later...

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

  • 1 year later...

 

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

  • 4 months later...
×
×
  • Create New...