Importing Products - Matching Category IDs

[a1200]

I have a large shop (4,000 categories, 30,000 products) which I am attempting to move to osCommerce. Some of you might have seen other posts (I also try and help where I can too).

 

Anyway, my existing site has the stock code (model) and category name as the identifier but osCommerce uses an Integer as standard for the unique identifier of products and categories. I have successfully recreated the category structure by populating the categories and parents in Excel and importing them into MySQL via phpmyadmin (with the help of OpenCalc to get the CSV with quotation marks on the fields!).

 

So the categories are done and I don't see TOO much of an issue at this stage creating basic product info by giving each product an ID or letting the database autonumber. I don't however see how I can map the right product to the right category. The products_to_categories dB is obviously where I need to map the product and category ID but how am I going to do that? Manually on so many products will be a nightmare!

 

Has anyone got any funky Visual Basic scripts or some php idea to make this array from category name to ID or something??I have looked at Easy Populate and it doesn't seem to have anything what I couldn't do manually with phpmyadmin (correct me if I'm worng).

 

One thing I thought about doing was to not store categories with an Integer as the ID and use the full name but I could see problems with this all over the place with contributions and maybe even the base code?

 

Thanks,

 

Mike

[a1200]

No one got any ideas? Looks like a long winded manual process is on the cards.

[candycanes]

No one got any ideas? Looks like a long winded manual process is on the cards.

 

If you haven't imported products yet, can you add a field to the DB so that when you do, it has the correct category name from your old site included in each product record. (I assume you have this category name data in the product csv? If not then stop reading now I guess!)

 

Then, seeing as you have mapped the category name to an autoincrement number in the categories table, can you not do a clever query that inserts the autonumber of the product and the autonumber of the category for each product into Products_to categories, based on the new field matching to the cat autonumber?

 

Not an (my)sql expert by any means so I don't know the exact way you'd do this, but it seem there has to be a logical way to avoid manually inputting 30000 products into P2C table!

[a1200]

I have just realised something else. The manufacturer's ID will also need matching up and guess what? That's a text identifier too in my existing shop. I am wondering now about changing the field types.

[a1200]

If you haven't imported products yet, can you add a field to the DB so that when you do, it has the correct category name from your old site included in each product record. (I assume you have this category name data in the product csv? If not then stop reading now I guess!)

 

Then, seeing as you have mapped the category name to an autoincrement number in the categories table, can you not do a clever query that inserts the autonumber of the product and the autonumber of the category for each product into Products_to categories, based on the new field matching to the cat autonumber?

 

Not an (my)sql expert by any means so I don't know the exact way you'd do this, but it seem there has to be a logical way to avoid manually inputting 30000 products into P2C table!

 

@Candycanes

 

Sounds like a good idea, if I can run the correct query then that could work. I will have a little more of a play and see what I can find.

[♥mdtaylorlrim]

What you are wanting to do is easiest done with php code. Simply build an associative array with the manufacturers name <-> number and the category name <-> number and as you import the products populate the products-to-category table and manufacturers field with the associative values.

[a1200]

@ mdtaylorlrim

 

Sounds good in theory... OK thanks I will look at this.