DB Table Structure for Linked Prods?

[Tapper]

Hi

I need to unlink all linked prods in my store. I inherited a group of zc stores, each of which has over 45,000 products, thousands of which were in the wrong cats. Also, I have made dozens of new cats and completely re-arranged others. It is an ongoing process: I spend a day or two each week on category fixes. We have about 20 top-level cats, around 300 sub cats. Latest versions of ZC and EZ Pop.

So now the prods are linked all over the place, and I want to clean up the data. No prod should be in more than 1 category. For instance, to discontinue a prod I move it into misc/disco, which is disabled so customers can't see it. But after the move it's in the old cat as well as in misc/disco. I disco thousands of prods a month so the moves are done in EZ Pop, not by hand.

Today I read about the Multiple Categories Link Manager and that's nice, but it's one prod or cat at a time. I installed the Quick Updates mod, and that's nice too, but again it's single edits.

I have to unlink well over 15,000 prods across 8 zc stores. I looked at the table products_to_categories but I'm not sure how it signifies the master vs linked cat.

I'd like to know where in the zc table structure it shows the linked cat vs the master cat for a product. I can prob write sql to make the linked cats the master and remove the linked cat, but I don't see what tables or fields I would hit. The tools I have in the admin force me to unlink the cats one at a time, and with over 300 cats this is practically an infinite task.

TIA

--Pat

[Ajeh]

Is the master_categories_id of each Product correct?

If so, then you can rebuild the products_to_categories table with just those categories_id and have the 1 to 1 relationship and eliminate all Linked Products ...

[Tapper]

Well, even if they're not all correct, I do have the latest prod ids and cat ids for all of my stores. One strange thing though: in one of my stores' products table the master_category_id is all 0. The other stores all have ids in that field. All stores get the same upload file, all have the same linked prods.

So if I read you correctly, I can drop all records in products_to_categories and insert a single record for each prod with it's proper category. And as long as all IDs are valid it won't break anything. I can do that, thanks.

But how is a linked cat defined? Is it just the second record for a particular product in products_to_categories? Once I get rid of these bad links I plan to have some prods linked to a "Discount" cat, and I'd like to do it through SQL.


Thanks Ajeh!

--Pat

[Ajeh]

That is the basic idea ...

I was going to have you use the products_id and master_categories_id to rebuild the products_to_categories table for the products_id and categories_id ...

You can still do this, and anyone in categories_id 0 needs to be fixed and they will be put in the wrong place, the Top Level Category ...

You could then go a step further and make a temp category like Broken Category and set all the categories_id that are 0 to that categories_id and all the master_categories_id where those are 0 to that new categories_id and then fix them ...

[Tapper]

OK, so is this correct:

1. products.master_categories_id is the primary spot to assign a master cat

2. products_to_categories.products_id and categories_id creates a secondary/linked cat.

3. My products.master_categories_id of 0 means that the prods are using some entry in products_to_categories.categories_id as the master cat

4. So there are 2 entries for each linked prod in products_to_categories when products.master_categories_id = 0

5. I can assign master cats in products.master_categories_id, and if the products_to_categories table is empty (if I delete all recs) it won't break anything, and will kill all prod cat links.

[Ajeh]

Not exactly ...

Products use the tables:
products
products_description

Categories use the tables:
categories_description

The relationship of Products to Categories is stored in the:
products_to_categories

table by putting in a products_id to a categories_id ...

There can be multiple products_id to various categories_id and this is what makes Linked Products ...

The problem is, on pricing and searching etc. there has to be a "main" or "master category" to rule them all ...

This one "master category" has its categories_id stored in the products table as the master_categories_id ...

If I had no Linked Products I could rebuild the products_to_categories from:
products_id
master_categories_id

by saving those to a file as:
products_id
categories_id

and empty the products_to_categories table and load that ...

If I had no Linked Products I could reset the master_categories_id for all Products from the products_to_categories table as there would only be 1 record for each Product ...

note: this is done in the Store Manager for those who really gobber up their site with imports ... but be careful as the first categories_id found in the products_to_categories table will be "assumed" to be the master_categories_id and that is usually the lowest valued categories_id based on the indexing ...

Does that clear it up a bit?

[Tapper]

I think I have it now.

For my master_category_id = 0 issue, I can set that to the proper category in the products table and establish a correct master cat. [And I won't worry about how that happened! That site always did have wierd search results, now I know why.]

For the rest, I will make the proper assigns of master cat and update products.master_category_id.

Then I'll take those product_id and master_category_id pairs and insert them into the products_to_categories table, after deleting all records first.

This will leave each product with a master cat assigned in the products table, and a single entry in the products_to_categories table, with no links.

This will kill all links.

Then, if I need to, I can insert a second record into the products_to_categories table for whichever prods I want to show up elsewhere.

And then the One Ring shall be mine! Bwahahahaha

One ring to rule them all, one ring to find them, one ring to bring them all, and in the darkness bind them.

Thanks Ajeh!

[Ajeh]

Now you have the right idea ... my precious ...