invalid master catgegory id problem

[LissaE]

Linda, I ran your test categories sql on my database because I have had the invalid category id thing show on my products for as long as I can remember LOL but it never seemed to affect anything so I just figured it was meant to be there rofl

I got these results:

categories_id categories_id
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
9 NULL
26 NULL
45 NULL
47 NULL
51 NULL
52 NULL
54 NULL
55 NULL
66 NULL
74 NULL
86 NULL
87 NULL
89 NULL
98 NULL
99 NULL
100 NULL

Forgive me if this is a stoopid question lol but what exactly does that mean? And how do I fix it?

**edit ~ ok so I just (finally) read the linked thread and it appears the invalid bit is meant to be there after all? ...still not sure here lol

[Ajeh]

That is where you need to know your categories ...

Try this one, and make sure that all of those are categories that are either Top level categories or parent categories ... meaning categories that hold categories ...

This should show your the Categories Name ...

select cd.categories_id, ptoc.categories_id, cd.categories_name
from categories_description cd
left outer join products_to_categories ptoc on cd.categories_id = ptoc.categories_id
where ptoc.categories_id is null;

[LissaE]

Thanx Linda :)

Here is my result...the ones highlighted in red are top level categories, the others are sub categories...

categories_id categories_id categories_name
1 NULL **Designer Products** <<<hidden category
2 NULL « Personal Use »
3 NULL « S4H/S4O »
4 NULL « Professional Use »
5 NULL « Designer Resources »
9 NULL Doodles
26 NULL Brushes
45 NULL « Collabs And Bundles »
47 NULL Designer Collabs
51 NULL « Discontinued Products »
52 NULL Going Out At 25% Off
54 NULL Going Out At 75% Off
55 NULL « Free Stuff »
66 NULL Blogwear
74 NULL **Free With Purchase** <<<hidden category
86 NULL ** NSD Bargains ** <<<hidden category, top level, no subs
87 NULL « Build Your Own Collab »
89 NULL Photomasks
98 NULL ♥ Daily Download ♥ <<<hidden category, top level, no subs
99 NULL Buy My Store Deals
100 NULL Designer Duo: creationFrenchgirl and Scrapbookizat...

[Ajeh]

As long as they are valid categories without errors, as in not missing and have not products in the ... meaning they just hold Categories ... then you should be fine ...

What is not good is

if you find missing master_categories_id in the products table

if you find master_categories_id that are not in the products_to_categories

if you find products without products_description

if you find products_descriptions without products

if you find categories without categories_descriptions

if you find categories_descriptions without categories

if you find products_id in products_to_categories without products

if you find categories_id in products_to_categories without categories

There are a number of other tests on the database ... but it gets complex to post how to find them then to break down what they mean ...

I am trying to work out some sort of testing that would give enough data that between a testing report and phpMyAdmin to check the errors ... all of the odd or lost or bad data can be found and then cleaned up ...

It can take an hour to several hours, depending on the results of testing all of these checks that I posted plus over a dozen others, to clean a database that has gotten out of wack ...

Then, once cleaned up ... the fun is finding out what is the source ...

The source could be related to errors on the database glitching when you do not expect it ... data with errors causing additional errors ... manually manipulating the database tables incorrectly ...

Having run old versions with errors that corrupted the data that later was compounded ...

Plus, a bazzillion other things that could have cause problems ...

Periodic database maintenance should be done to catch these errors before they are a problem and possibly identify what is happening to cause them, in order to prevent future problems ...

[LissaE]

Thanx so much Linda...my cart is so highly modified I am skeered it's going to pack it in on me one day and say "no more!" LOL

[nadsab]

Ajeh, thanks sorry for the delay. Here are the results of the first query:

Empty set (0.01 sec)

Results of second query:

+---------------+---------------+
| categories_id | categories_id |
+---------------+---------------+
|            84 |          NULL |
|            88 |          NULL |
|            89 |          NULL |
|            93 |          NULL |
|            97 |          NULL |
|           100 |          NULL |
|           105 |          NULL |
|           106 |          NULL |
|           107 |          NULL |
|           108 |          NULL |
|           113 |          NULL |
|           118 |          NULL |
|           119 |          NULL |
|           122 |          NULL |
+---------------+---------------+
14 rows in set (0.00 sec)

Results of the query you suggested to LissaE:

+---------------+---------------+----------------------------------+
| categories_id | categories_id | categories_name                  |
+---------------+---------------+----------------------------------+
|           122 |          NULL | Festool Dust Extraction          |
|           119 |          NULL | OF 1010 Accessories              |
|           118 |          NULL | MFK 700 Router Accessories       |
|           113 |          NULL | Festool Routers                  |
|            84 |          NULL | Festool Guide Rail Systems       |
|            88 |          NULL | Festool Hole Guide Rail Access.  |
|            89 |          NULL | Festool Jigsaws, Jig Accessories |
|            93 |          NULL | Festool Compound Miter Saws      |
|            97 |          NULL | FT Cordless Drills & Accessories |
|           100 |          NULL | Multifunction Tables             |
|           105 |          NULL | MFT/3 Kapex Accessories          |
|           106 |          NULL | Festool Planers and accessories  |
|           107 |          NULL | Festool Planers                  |
|           108 |          NULL | Festool Planer Accessories       |
+---------------+---------------+----------------------------------+
14 rows in set (0.04 sec)

[nadsab]

Forgot to mention, the bold faced are top level categories.


Results of the query you suggested to LissaE:

+---------------+---------------+----------------------------------+
| categories_id | categories_id | categories_name                  |
+---------------+---------------+----------------------------------+
|           122 |          NULL | Festool Dust Extraction          |
|           119 |          NULL | OF 1010 Accessories              |
|           118 |          NULL | MFK 700 Router Accessories       |
|           113 |          NULL | Festool Routers                  |
|            84 |          NULL | Festool Guide Rail Systems       |
|            88 |          NULL | Festool Hole Guide Rail Access.  |
|            89 |          NULL | Festool Jigsaws, Jig Accessories |
|            93 |          NULL | Festool Compound Miter Saws      |
|            97 |          NULL | FT Cordless Drills & Accessories |
|           100 |          NULL | Multifunction Tables             |
|           105 |          NULL | MFT/3 Kapex Accessories          |
|           106 |          NULL | Festool Planers and accessories  |
|           107 |          NULL | Festool Planers                  |
|           108 |          NULL | Festool Planer Accessories       |
+---------------+---------------+----------------------------------+
14 rows in set (0.04 sec)

[nadsab]

|           119 |          NULL | OF 1010 Accessories

Is for now empty and is child of Festool Routers.

|           118 |          NULL | MFK 700 Router Accessories

Is empty for now and child of Festool Routers.

|            88 |          NULL | Festool Hole Guide Rail Access.  |

Is for now empty and a child of Festool Guide Rail Systems.

   105 |          NULL | MFT/3 Kapex Accessories

Not empty, holds linked products only and is a child of Festool Compound Miter Saws.

|           107 |          NULL | Festool Planers

Holds two sub categories (of which both hold products and no cats) and no products.

|           108 |          NULL | Festool Planer Accessories       |

Holds two sub categories (of which both hold products and no cats) and no products. One of the sub cats holds linked products.

[Ajeh]

The goal is to make sure that all Categories referenced in the products_to_categories table have a matching Category in the categories table ...

The check against the categories_description table is to see what their names are ...

The master_categories_id from the products table should have a record in the products_to_categories table ... and the products_id in the products_to_categories table need to have a record in the products table ...

Two other tests that can be done are ...

MATCH MISSING ON master_categories_id products vs products_to_categories

SELECT * FROM products WHERE products.master_categories_id NOT IN (SELECT products_to_categories.categories_id FROM products_to_categories);

MATCH MISSING ON master_categories_id products vs categories

SELECT * FROM products WHERE products.master_categories_id NOT IN (SELECT categories.categories_id FROM categories);

Without actually sitting down with the database it is hard to try to interpret the results in posts ... as I say, it does take some time to go through the database tables and test the results and confirm what is wrong and the best method to clean things as you could have a products_id in the products_description and products_to_categories but not in the products table and other such bad data ...

[nadsab]

mysql> SELECT * FROM products WHERE products.master_categories_id NOT IN (SELECT products_to_categories.categories_id FROM products_to_categories);

Empty set (0.14 sec)

mysql> SELECT * FROM products WHERE products.master_categories_id NOT IN (SELECT categories.categories_id FROM categories);

Empty set (0.02 sec)

At first glance, have you seen anything that sticks out as a problem?