1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

PHP or SQL script needed

Discussion in 'PHP' started by gemini, Apr 12, 2005.

  1. #1
    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?
     
    gemini, Apr 12, 2005 IP
  2. mushroom

    mushroom Peon

    Messages:
    369
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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.
     
    mushroom, Apr 12, 2005 IP
  3. gemini

    gemini Peon

    Messages:
    256
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #3
    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.
     
    gemini, Apr 12, 2005 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #4
    I wouldn't update, I'd just empty the table and rebuild it.

    To read the xml I recommend class.xml.php as being very easy to use - relative to the xml scripts I've tried.

    (and I've submitted to your directory, you are up on mine)

    sarah
     
    sarahk, Apr 12, 2005 IP
  5. Bobby Easland

    Bobby Easland Guest

    Messages:
    50
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Sounds like more work than is needed...why not just create a cats2affiliate map table?

    Bobby
     
    Bobby Easland, Apr 12, 2005 IP
  6. gemini

    gemini Peon

    Messages:
    256
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Thanks Sarah - I will look the script you suggested.
    Thanks for the link too - you're approved ;)
     
    gemini, Apr 12, 2005 IP
  7. mushroom

    mushroom Peon

    Messages:
    369
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    0
    #7
    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)
     
    mushroom, Apr 12, 2005 IP
  8. gemini

    gemini Peon

    Messages:
    256
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #8
    thanks mushroom - will try that :)
     
    gemini, Apr 12, 2005 IP
  9. Bobby Easland

    Bobby Easland Guest

    Messages:
    50
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #9
    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
     
    Bobby Easland, Apr 13, 2005 IP
  10. gemini

    gemini Peon

    Messages:
    256
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #10
    I actually mistaken when I said there are 7K of records - its 7MB - 46K records..
     
    gemini, Apr 13, 2005 IP