Results 1 to 5 of 5
  1. #1
    Pimp Duck popdawg's Avatar
    Join Date
    January 18th, 2005
    Location
    Take off eh?
    Posts
    3,249
    PHP MySQL Bulk Insert
    I'm doing an insert that is generated dynamically. A query loops and adds the insert commands into an array. Something that when finished looks like this:
    insert into TABLENAME (FIELD1,FIELD2) values ('STRING1', 'STRING2'), ('STRING1B', 'STRING2B'), ('STRING1C', 'STRING2C');

    To get this, I am doing something like this:
    LOOP START
    $InsertQuery = $InsertQuery."('$STRING1', '$STRING2'), ";
    LOOP END

    STRING2 values often include single quotes ' and so I am using addslashes to take care of them.

    Needless to say, it's not working. I have printed out the query and it looks fine.
    What's more, it works if I copy and paste the query into phpmyadmin as is.

    I have tried a few different things, including changing the query to something like this.
    insert into TABLENAME (FIELD1,FIELD2) values ('STRING1', 'STRING2') insert into TABLENAME (FIELD1,FIELD2) values ('STRING1B', 'STRING2B') insert into TABLENAME (FIELD1,FIELD2) values ('STRING1C', 'STRING2C')
    Again, this works fine pasted into phpmyadmin but not through php.

    I am missing something stupid here as I had this working before but now can't for the life of me get it together.
    Any help would be appreciated.
    ================================================================
    Been away, now I'm back. Not as much, but I'm back & starting from scratch. Where I was, was fantastic. Where I am now, less so. Things have changed, become harder. So have I. Game ON!!!
    ================================================================

  2. #2
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    Your loop will end with an extra comma at the end.
    Michael Coley
    Amazing-Bargains.com
     Affiliate Tips | Merchant Best Practices | Affiliate Friendly? | Couponing | CPA Networks? | ABW Tips | Activating Affiliates
    "Education is the most powerful weapon which you can use to change the world." Nelson Mandela

  3. #3
    Pimp Duck popdawg's Avatar
    Join Date
    January 18th, 2005
    Location
    Take off eh?
    Posts
    3,249
    Yeah I had caught that thanks
    I trim that out after the loop ends and before I run the query.

    Back to banging the head on the desk
    ================================================================
    Been away, now I'm back. Not as much, but I'm back & starting from scratch. Where I was, was fantastic. Where I am now, less so. Things have changed, become harder. So have I. Game ON!!!
    ================================================================

  4. #4
    Pimp Duck popdawg's Avatar
    Join Date
    January 18th, 2005
    Location
    Take off eh?
    Posts
    3,249
    tink I got it. ... enclosed things in another set of double quotes and it seems to be working. Not sure why.
    ================================================================
    Been away, now I'm back. Not as much, but I'm back & starting from scratch. Where I was, was fantastic. Where I am now, less so. Things have changed, become harder. So have I. Game ON!!!
    ================================================================

  5. #5
    Full Member
    Join Date
    January 18th, 2005
    Posts
    88
    For bulk inserts, use LOAD DATA INFILE Syntax

    It is faster and reliable. Instead of writing directly into the database, write it in a file and load the data at one go.
    if you use loop the script will time out for large data.

    I have a daily batch which refereshes the database like this. Downtime - almost nil
    I also tried looping. The script died for 2mb data.

    http://dev.mysql.com/doc/refman/5.0/en/load-data.html

  6. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Php Page / Form Processing / Mysql Insert / No Page Refresh
    By Uncle Rico in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: January 4th, 2009, 11:55 AM
  2. how to speed bulk insert to sql?
    By josephmalka in forum Programming / Datafeeds / Tools
    Replies: 0
    Last Post: February 11th, 2007, 06:45 AM
  3. How do I insert a PHP script into a .htm file
    By dak142 in forum Programming / Datafeeds / Tools
    Replies: 8
    Last Post: April 2nd, 2004, 06:24 AM
  4. MySQL Insert Data Problem - Help!
    By Taurus in forum Programming / Datafeeds / Tools
    Replies: 11
    Last Post: July 24th, 2002, 09:38 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
  •