Magento categories not showing in admin

Magento category fix

/** Check integrity */
SELECT c.entity_id, c.children_count as original_children_count, COUNT(c2.children_count) as `children_count`, c.level as original_level, (LENGTH(c.path)-LENGTH(REPLACE(c.path,'/',''))) as `level`
FROM mage_catalog_category_entity c
LEFT JOIN mage_catalog_category_entity c2 ON c2.path like CONCAT(c.path,'/%')
GROUP BY c.path

/** Fix child counts */
UPDATE mage_catalog_category_entity SET children_count = (SELECT COUNT(*) FROM (SELECT * FROM mage_catalog_category_entity) AS table2 WHERE path LIKE CONCAT(mage_catalog_category_entity.path,"/%"));

/** more info */
http://inchoo.net/magento/solving-problems-with-category-tree-database-information/

The first thing we have to notice is whether your category tree looks OK. If you imported it from some other system using a custom script, chances are that everything looks just fine. It’s relatively easy to check that since it’s visible even on the front-end – if your shop’s categories are right, then your category tree is healthy.

Category tree is stored as a database column called “path” in “catalog_category_entity” table. Each entry in that table represents one category and each “path” column value represents the exact position of a category in the category tree. The problem we encountered in one of our recent projects is that the categories were imported from osCommerce a while ago and whoever wrote the import script forgot to update the level of the categories as well as the number of children each category has. That caused indexer failure on “Catalog URL Rewrite” process as well as the issues with displaying available categories on the Product edit page in the Magento’s admin area.

So, we obviously have a healthy category tree (“path” column) and messed up “level” and “children_count” columns. Luckily, we can use data in “path” column to fix this. Since this is a one time operation, I will stick with using raw SQL queries.

Important note: Use these queries on your own discretion. Do not apply to a live system directly and always test on your local/development machines before deciding to apply it on a live environment.

Updating “level” column values
UPDATE catalog_category_entity SET level =
(SELECT LENGTH(path)-LENGTH(REPLACE(path,'/','')) AS tmpl
FROM (SELECT * FROM catalog_category_entity) AS table1
WHERE catalog_category_entity.entity_id = table1.entity_id);
What we have done here is reading the number of category delimiter characters (“/”) in the path column and adding a number of them to the “level” column. That will reliably tell us the exact level of the category.

Updating “children_count” column values
UPDATE catalog_category_entity SET children_count =
(SELECT COUNT(*) FROM
(SELECT * FROM catalog_category_entity) AS table2
WHERE path LIKE
CONCAT(catalog_category_entity.path,"/%"));
This query is a bit more complicated, since it runs through whole table and collect how many rows have the same sub-string (which is equal to the “path” column value of the observed row) on the start of the “path” column value.

If you know exactly how many children one category has and you want to check if this query did the trick, you can do it with this query:

SELECT COUNT(*) FROM catalog_category_entity
WHERE path LIKE '1/15/%';
… where the category with ID = 15 is the one you are checking.

Post execution tasks
After executing these queries, you just have to re-index and you’re done. Clearing the cache and Logging out and back in the admin area is also a good idea.