Is there a way to turn off categories and subcategories if all products are inactive?

[mikeel100]

Is there a way to turn off categories and subcategories where all products in those groups are inactive?

I know I can click on the green button and do it but I'm looking for a way to automate it with mysql statement or by modifying the php code in one of the files.

Thanks.

[allthingsidLeroy]

I whipped something up for you. It's fairly complex, so I'll explain it for you.

You shouldn't attempt this on your live website and should do it on a test version of your database. Make backups in case something goes wrong. Here's the query:

UPDATE categories 
SET categories_status = 0 
WHERE categories_id NOT IN  
(
  SELECT categories_id FROM
  (
    SELECT DISTINCT categories_id 
    FROM products_to_categories 
    NATURAL JOIN categories 
  ) as foo 
)                                                                                                                                                               
AND parent_id != 0;

Basically, if the category isn't in the sub-select statement (the one two parenthesis in), turn it off.

The sub-select gathers all the distinct categories that are actually being used. If a category has a product, it will be selected. Since we're looking for all the categories that aren't being used (via NOT IN), the query will turn off the categories that do not get selected.

And, finally, don't turn off categories that are parents at the highest level in Zen Cart (the categories you'd see on the front page) even if they don't hold products themselves.

MySQL doesn't like when you try and update a table and then sub-select a WHERE clause from the same table you're updating. In the above query, I have made a temporary table called 'foo' to work around this.

I really hope this helps.

Again: don't do this on your live website!

[allthingsidLeroy]

The above query will work, but it's a bit of a mess. Here's a better version of the above query:

UPDATE categories
SET categories_status = 0
WHERE categories_id NOT IN
(
  SELECT DISTINCT categories_id 
  FROM products_to_categories 
)
AND parent_id != 0;

I was trying to do some cool stuff by naturally joining the categories table before, but I didn't need to. It works pretty much the same way as before except now there's no temporary table.

[mikeel100]

I appreciate your help...I'll give it a shot on my test site tonight and let you know how it works.

[mikeel100]

The above query will work, but it's a bit of a mess. Here's a better version of the above query:

UPDATE categories
SET categories_status = 0
WHERE categories_id NOT IN
(
  SELECT DISTINCT categories_id 
  FROM products_to_categories 
)
AND parent_id != 0;

I was trying to do some cool stuff by naturally joining the categories table before, but I didn't need to. It works pretty much the same way as before except now there's no temporary table.

I'm not sure this will work.

I need the WHERE statement to point to the place in the database that stores ## of ## which is next to the green or red dots in the admin/categories area. I.E. 0 of 0 or 0 of 150 would need to be turned off.

I don't know if or how this number is calculated with php code or if it is stored in the database somewhere...but I know that's the info I need the WHERE statement to key off of.

I'll try your code on my test store and see how it works...cautiously.

Thanks again.

[mikeel100]

I tried it but it just randomly turned off categories at the second level...even the categories that still have active products.

If I could just find out where the calculations are done and stored for the # of # (like I mentioned above), then I'm sure we could figure out the WHERE statement that we need to do this.

Suggestions anyone?

[allthingsidLeroy]

I tried it but it just randomly turned off categories at the second level...even the categories that still have active products.

If I could just find out where the calculations are done and stored for the # of # (like I mentioned above), then I'm sure we could figure out the WHERE statement that we need to do this.

Suggestions anyone?

I didn't even take into consideration categories that technically have no products themselves, but have sub-categories with products.

Unfortunately, I don't know of any place where Zen Cart stores the information about a category's total products other than counting individual products in the `products_to_categories' table, which I think happens in the PHP.

I'll think about this and hopefully I will return with a solution.

[mikeel100]

I kind of felt like the code might be buried in the php so I went to stackoverflow to try and find a solution to the coding.

I know what info to feed off of in the database...just don't know how to write the MySQL code to make it work. The details are at the link below if you (or anyone else) is interested.

http://stackoverflow.com/questions/1...egory-are-zero

I found another thread on zencart that is talking about this subject: http://www.zen-cart.com/showthread.p...ctive-products

Maybe, between these 3 threads, someone can come up with the code we need.

I appreciate your help!

[mikeel100]

Ajeh answered this at the link below...it works for me!

http://www.zen-cart.com/showthread.p...products/page2