PHP or SQL script needed

I'm a real rookie in this, so don't kick me too hard.

I want to import affiliate feed into my database. The affiliate gives me products and the names of the categories they belong to. In order to make it work I need to rename all those categories into id# of the category names I already have in my own categories table. There are 3K of categories and over 7K products. I will need to do this procedure every day to update my products - where do I start?

 

Who ever does it will need a copy of your categories table, the stucture of the tables that the data will be inserted into and a flat text file of the affiliate feed or access to it.

If you are on a Linux sever it may be even set up as a cron job running all by itself every day.

 

I was thinking about doing this on my own - this way I could learn something
First I need to figure the correct algo how it should go step-by-step. My guess:
1. read the first record from the category table - id name
2. update all the records in the products table to the id# where category name matches.
3. go to the next record in the category table...

I'll try to play with it tonight and see if I get anywhere. Any suggestions or examples would be helpful.

 

Sounds like more work than is needed...why not just create a cats2affiliate map table?

Bobby

 

Thanks Sarah - I will look the script you suggested.
Thanks for the link too - you're approved

 

How I would do it
1. read all of the category table " $id, $name"
2. load into arrays
3. open feed
4. start loop
4a. read line
4b. set $cat= "category from line"
4c. $key=array_search( $cat, $name, TRUE);
4d. $cat=$id[$key];
5. insert line in to db
6. next (back to 4a)

 

thanks mushroom - will try that

 

That solution is not scalable.

Once you load the products table (7K products) it will chew through about 2.5-4 Mb of RAM on the server...then looping back through will consume another 2.5-4 Mb of RAM.

The best method is to create a map table and parse the XML line by line. It may execute more queries (one for each data element) but will ultimately be more scalable using less server resources.

This is an issue that is pretty common with osCommerce which is the application I'm most familiar.

...just my 2 cents.

Bobby

 

I actually mistaken when I said there are 7K of records - its 7MB - 46K records..