SQL command

[Dave L]

Hi

 

Could anyone having MySQL database experience tell be what the command line should be to en-mass uncheck the home selection in Products>Associations please. 

The site I am working on for a friend has about 500 products with Home selected so featured products has a few too many selections :-0

 

Thanks.

[Alexandre Carette]

Hello,

DELETE FROM `ps_category_product` WHERE id_category = 2

amicalement

[Dave L]

Hi Alexandre

 

Thank you for the quick response. I tried that but unfortunately no change. 

 

EDIT:

 

Just for clarity I want to have the home category uncheck for all products. Right now all products have this:

 

Edited May 13, 2017 by Dave L (see edit history)

[Dave L]

Hello,

DELETE FROM `ps_category_product` WHERE id_category = 2

amicalement

 

Hi,

With a fresh mind I relooked and you were totally correct. The problem was my Home Category was 1 and not 2. So using the original query removed all from Category 2.

 

So now I need to have all products in subcategory 13, 14, 15 16, 17, 18 and 109 to also be included it Category 2.

 

Appreciate your further help.

 

Thank you.

Edited May 14, 2017 by Dave L (see edit history)

[musicmaster]

You are acting a bit risky now. If you had products that were only in category 1 or 2 they are no longer accessible in the backoffice.

 

The problem that you are asking now is that it concerns a quite complex query - what requires that someone tests it well before applying. Most people would do this with a mix of php and Mysql instead.

 

For this kind of problem it is much easier to use a tool like Prestools. Prestools is free.

Edited May 15, 2017 by musicmaster (see edit history)

[Dave L]

You are acting a bit risky now. If you had products that were only in category 1 or 2 they are no longer accessible in the backoffice.

 

The problem that you are asking now is that it concerns a quite complex query - what requires that someone tests it well before applying. Most people would do this with a mix of php and Mysql instead.

 

For this kind of problem it is much easier to use a tool like Prestools.

 

Hi

 

Thanks for the advice. Actually all of the products are in the other categories as well.

I need to have the products that are in subcategories 13, 14, 15 16, 17, 18 and 109 also included in the parent category which is category 2.

 

 

If this is complicated or a dangerous thing to do I will have to go through each products association manually    

 

Thanks.

[DataKick]

First, you need to find out all child categories or parent category 2:

select c.id_category 
from ps_category p, 
     ps_category c 
where p.id_category = 2 
  and c.nleft >= p.nleft 
  and c.nright <= p.nright

Then, find out all distinct products associated with these children categories

select distinct pc.id_product 
from ps_category_product pc 
where pc.id_category in (
  select c.id_category 
  from ps_category p, 
       ps_category c 
  where p.id_category = 2 
    and c.nleft >= p.nleft 
    and c.nright <= p.nright
)

And now the association. The clean way would be to remove already associated product from the list. But since we are lazy, we can use insert ignore

insert ignore into ps_category_product(id_product, id_category) 
select distinct pc.id_product, 2
from ps_category_product pc
where pc.id_category in (
  select c.id_category
  from ps_category p,
       ps_category c
  where p.id_category = 2
    and c.nleft >= p.nleft
    and c.nright <= p.nright
)

Hope this helps a bit

[Dave L]

Hi DataKick

 

Appreciate your time in putting this together for me. It's a bit late this evening but I will study it over the coming days to see if I dare have a go with it.

 

I will post back when I have a result, good or bad 

 

Appreciated.

 

 

Edit:

Just a thought, would it make the job easier if I captured all the product ID's ?

Edited May 15, 2017 by Dave L (see edit history)