Results 1 to 7 of 7
  1. #1
    Newbie
    Join Date
    January 19th, 2007
    Posts
    4
    Datafeeds to my MYSQL Db
    Hello,

    I'm new here and into the datafeeds world and i have a script that grabs the xml into my database.

    But i have a question i will daily reload the xml file to have a correct product list of zzounds.com.

    Do you empty the database table(s) and reinsert the data from the xml file or something else ?


    Kind Regards,
    Kris

  2. #2
    Animal Lover
    Join Date
    January 18th, 2005
    Location
    oz
    Posts
    1,210
    HI Kris

    I don't usually empty out the original database and reload it - that would mean that during the time it takes to reload, your site would have broken pages...guess it doesn't matter if your feed has only a couple of products that doesn't take long to load but if it's a mega feed that could take longer it's not great for your visitors.

    What I do is create a temporary table to house the new datafeed - and leave the original table as is in the meantime. Once the new temporary table has finished uploading, I then delete the original table and rename the temporary table as the original - this means minimal down time on your site.

    Oscar
    My DataFeed Scripts - php datafeed scripts for your site
    Shareasale datafeed scripts - to display Shareasale datafeeds
    Linkshare datafeed scripts - for multiple Linkshare merchants

  3. #3
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    Some possible area to consider:

    - It might be better to truncate the original table and then move the data over

    - If you have any indexes don't forget to redo those when the table is reloaded
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  4. #4
    Lite On The Do, Heavy On The Nuts Donuts's Avatar
    Join Date
    January 18th, 2005
    Location
    Winter Park, FL
    Posts
    6,930
    instead of a temporary table, i have 2 permanent, identical tables. let's call them products1 and products2. products1 is the table that drives the site. products2 is the one that i empty and fill with the new data when ever i update things. so when i'm done filling / correcting / optimizing / indexing products2, and it's ready to go online by replacing products1, i run these mysql commands:

    RENAME TABLE products1 TO products3;
    RENAME TABLE products2 TO products1;
    RENAME TABLE products3 TO products2;

    line 1 temporarily gives products1 an unused name so i can...
    line 2 renames the newly filled table (products2) into products1 and it becomes the table now driving the site.
    line 3 makes the old table's name change to products2

    these 3 lines aren't magic - they just swap the two tables names (which requires calling one of them products3 for a brief fraction of a second) so that the new table is online (products2 becomes products1) and the old one is then parked (as products2), filled with the old data, and sits there in reserve in case my new table's contents has any problems (in which case I can switch them back quickly and keep my site up).

    the total downtime is the fraction of a second between line 1 and line 2 (when there's no table called products1). in mysql, this is roughly 0.005 seconds.

    mysql lets you run multiple commands at once, so you can paste all three lines into phpmyadmin (or create a web page that runs them or whatever) and mysql executes them consecutively.

    i also have a few special (non-public, non-linked) web pages that are programmed just like my normal pages but they are set to pull from products2 (instead of products1). so after i fill products2 with the new data, i can view those pages and make sure everything's loading correctly BEFORE i swap the names out and put the new table online.

    and btw, having the replaced data sitting there in reserve should something go wrong has saved my butt several times.

  5. #5
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    I do it like oscar.

    I have the script ftp the feed, parse it and upload it to a temp table in the db, then when it's done the script drops the original table and renames the temp table to the original table name.

    Shawn Kerr (.com) | Disney World | SEC Football

  6. #6
    Speechless OTProf's Avatar
    Join Date
    November 4th, 2006
    Location
    Sunny SoCal
    Posts
    832
    Donuts -

    You are awesome to give such helpful advice at such a level of detail!! Very, very cool!!

  7. #7
    Animal Lover
    Join Date
    January 18th, 2005
    Location
    oz
    Posts
    1,210
    i also have a few special (non-public, non-linked) web pages that are programmed just like my normal pages but they are set to pull from products2 (instead of products1). so after i fill products2 with the new data, i can view those pages and make sure everything's loading correctly BEFORE i swap the names out and put the new table online.
    Clever move. Had a few nightmare times when I casually renamed the original table assuming that the new data was working as it should...and they weren't...broken pages as a result and a mad scramble to load up old data again till I looked at the new table to see what had changed (usually the merchant had changed the category field numbers or something). Learnt to be more careful the hard way.

    Oscar
    My DataFeed Scripts - php datafeed scripts for your site
    Shareasale datafeed scripts - to display Shareasale datafeeds
    Linkshare datafeed scripts - for multiple Linkshare merchants

  8. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Mysql Table Definition for Google Aff Network Datafeeds
    By Uncle Rico in forum Programming / Datafeeds / Tools
    Replies: 11
    Last Post: December 23rd, 2009, 12:35 PM
  2. Will the datafeeds in the merchants datafeeds thread track my commissions?
    By john9245 in forum Programming / Datafeeds / Tools
    Replies: 5
    Last Post: March 29th, 2005, 09:42 AM
  3. Datafeeds, MySQL, PHP, and SEO
    By squidnunc in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: August 26th, 2004, 07:21 PM
  4. mysql sl-o-o-o-o-o-o-w
    By Heyder in forum Programming / Datafeeds / Tools
    Replies: 4
    Last Post: December 20th, 2002, 06:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •