Hiding Categories

[Ally71]

Am new to using the software have everything set up and looking great. Just a quick question that I couldnt seem to find an answer to. Can you hide categories/ subcategories when they dont have any stock or product listed in them?
Just want to save my customers following those links until such time as I upload stock to them.
Thanks
Ally

[kobra]

Try in the admin > config > stock > Products status in Catalog when out of stock should be set to > 0= set product status to OFF

[Ally71]

Thanks for your help will try that

[gjh42]

Unfortunately that won't hide the category when it is empty. You can manually disable any category that doesn't have products ready for sale, in Catalog > Categories/Products. Click on the green button next to the category in the list (under Status) and it will turn red.

[ceegeeaar]

Is there some sql I could paste which would disable empty categories rather than ploughing through them all manually?
I have a site populated with 16000 products in an industry standard category structure. There are unused categories which I have managed to hide in the categories sidebox using the categories dressing mod but there is a category list at the top of the page which still has them and also subcategories displayed on the main page show them so I think it would be better to disable the empty ones rather than 'not display' them - set their status to 0.
With 16000 products and 1500 categories that is a mammoth task and Im sure there is an 'automatic' way to do this with an sql statement but Im not familiar enough with sql to come up with it myself

[gjh42]

For a start, /includes/functions/functions_categories.php should have a function to count the products in a category. From there, you could loop through the categories and apply SQL to reset the status of each cat without products. Someone should be able to come up with the SQL statement for the resetting.

[ceegeeaar]

I noticed the count is shown in the admin side aswell. It could probably be used to perform the same function as clicking the icon, without the confirmation page. Thats as far as I got before I became expertiseless tho!

[tarch]

For a start, /includes/functions/functions_categories.php should have a function to count the products in a category. From there, you could loop through the categories and apply SQL to reset the status of each cat without products. Someone should be able to come up with the SQL statement for the resetting.

Somebody could give this SQL statement? TIA!

[Zwempha]

Sorry for the late reply, but I just found this thread when looking for the same thing. I didn't find the answer so I came up with these queries.
To hide:
To delete:
Use at own risk, dosen't work just yet.

Regards!
Sven ,Sweden
-Noob Carter-

[drrogera]

Here is a query that will return a list of categories that have active products and 4 levels above that those categories are subs to - how you choose to use it is your business. In other words, if you use it to delete categories that you later find should not have been, I am not responsible.

At this stage it only handles 5 levels - but it could go farther if needed. I have tested this in MySQL and it tolerates the nested sub-selects. Using an ODBC connection I found that MS Access does not.

SELECT categories_id FROM zen_products_to_categories
WHERE products_id IN (SELECT products_id FROM zen_products WHERE products_status = 1)
UNION
SELECT zen_categories.parent_id FROM zen_categories 
WHERE zen_categories.categories_id In (SELECT categories_id FROM zen_products_to_categories)
UNION
SELECT parent_id FROM zen_categories 
WHERE categories_id IN( SELECT zen_categories.parent_id FROM zen_categories WHERE zen_categories.categories_id In (SELECT categories_id FROM zen_products_to_categories))
UNION
SELECT parent_id FROM zen_categories 
WHERE categories_id IN (SELECT parent_id FROM zen_categories 
WHERE categories_id IN( SELECT zen_categories.parent_id FROM zen_categories WHERE zen_categories.categories_id In (SELECT categories_id FROM zen_products_to_categories)))
UNION
SELECT parent_id FROM zen_categories
WHERE categories_id IN (
SELECT parent_id FROM zen_categories 
WHERE categories_id IN (SELECT parent_id FROM zen_categories 
WHERE categories_id IN( SELECT zen_categories.parent_id FROM zen_categories WHERE zen_categories.categories_id In (SELECT categories_id FROM zen_products_to_categories))))
ORDER BY categories_id

Good luck and ALWAYS backup your database!