Insert Array into MYsql using multiple insert

Posted on 2005-04-10
Last Modified: 2013-12-12
Hi Everyone.

New and interesting problem.  I am extracting data from very obscure db thru odbc and inserting the information into MYsql to be viewd on intanet.

what would be the fastest form of inserting the data into the mysql table. I have created my own " odbc_fetch_assoc() " function to return an array similar to MYsql.

Array ( [odbc_affected_rows] => 0 [ItemID] => 1 [IsInactive] => N [ItemName] => Polyclar QS [ItemNumber] => 17106 [QuantityOnHand] => 29.522000 [ValueOnHand] => 929.940000 [SellOnOrder] => 0.000000 [PurchaseOnOrder] => 0.000000 [ItemIsSold] => Y [ItemIsBought] => Y [ItemIsInventoried] => Y [IncomeAccountID] => 63 [ExpenseAccountID] => 30 [InventoryAccountID] => 138 [Picture] => [ItemDescription] => [UseDescription] => N [CustomList1ID] => 0 [CustomList2ID] => 0 [CustomList3ID] => 0 [CustomField1] => [CustomField2] => [CustomField3] => [BaseSellingPrice] => 40.000000 [PriceIsInclusive] => N [SellTaxCodeID] => 12 [SalesTaxCalcBasisID] => ASP [SellUnitMeasure] => Kg [SellUnitQuantity] => 1 [TaxInclusiveLastPurchasePrice] => 34.650200 [TaxExclusiveLastPurchasePrice] => 34.650200 [BuyTaxCodeID] => 12 [BuyUnitMeasure] => Kg [BuyUnitQuantity] => 1 [PrimarySupplierID] => 24 [SupplierItemNumber] => Polyclar QS - 11722245083 [MinLevelBeforeReorder] => 10.000000 [DefaultReoderQuantity] => 22.700000 [ChangeControl] => 0253900031 )

So now i need to extract the values and do an insert.

1) should i create and totalised array that contains a key for each entry then form a multiple MYsql insert(not really certain how to do that one)
2)Should do an insert for each array extracted

I am thinking that maybe the 1st option may be the better option as it is performing only one insert.  Is one really big insert better than doing a lot of little inserts?

I hope this is explained clearly to understand
Question by:mrwilde
    LVL 11

    Expert Comment

    You can insert everything at once. If the fieldnames are identical in both tables then it won't be much work.
    You can insert into mysql like this:

    INSERT INTO tablename (field1, field2, field3) VALUES ('val1', 'val2','val3') , ('val1', 'val2','val3') ,('val1', 'val2','val3') ,etc.

    So if you have an array($arr) of associative arrays of all the rows as you have shown above you could do something like:

    $sql = 'INSERT INTO tablename (';
    $fieldNames = '';
    $fieldValues = ' VALUES ';
       $fieldValues .= '(';
       foreach($arr[$i] as $key=>$value)
          if ($i == 0) $fieldNames .= $key.',';
          $fieldValues .= "'$value',";
       $fieldValues = rtrim($fieldValues,',').'),';
    $fieldNames = rtrim($fieldNames,',');
    $fieldValues = rtrim($fieldValues,',');
    $sql .= $fieldNames .') '.$fieldValues;
    mysql_query($sql) or die(msyql_error().$sql);

    This is untested, but you can try it or and it there's a sql error it will print it out along with the query.
    So try to get the query in the format I post above, or post the query.

    LVL 8

    Expert Comment

    When you're looking at a speed, I guess it would be better to use LOAD DATA INFILE ( ). With this function you can read a comma (or any symbol you want) seperated file into the database at once. This has the advantage that no checking is happening on your data (since it is coming from a database it should be okay and the integrity should also be okay). You have somewhat more of a hasle with creating a file with the values etc., but I've seen applications become more than ten times faster by this.
    But again, when it is a small table, you'd probably don't want to bother.
    LVL 1

    Author Comment

    the issue I have is that the table it is coming from contains between 3500 and 10500 lines and around 40 colums.

    I have tried a few querys and get time out errors and errors Like "MYsql has gone"
    I am very interested in the file opption Marcel has described. Could you possibly offer some more info as to how i create a file from the array then upload the file into MYsql
    LVL 8

    Accepted Solution

    To create a text file with tab-separated values that is enquoted:

    $handle = @fopen('\var\tmp\insertfile.txt', 'w') or die('Cannot open file');
    foreach ($arr as $arrline) {
       $txt = "\"" . implode("\"\t\"", $arrline) . "\"\n";
       fwrite($handle, $txt);

    You then need the following SQL statement to insert the file:

    LOAD DATA LOCAL INFILE '\var\tmp\insertfile.txt' REPLACE
        INTO TABLE tbl_name

    where tbl_name is the name of the table (duh). You can use IGNORE instead of REPLACE to ignore the lines that already exist (where REPLACE replaces them).
    Also make sure you have the right to read the file (or more important: that mysql has the right to do it). Also make sure that the user using mysql has the right (database)rights to do this.
    LVL 1

    Author Comment

    The code supplied was extremley usefull Thankyou. The only problem is i cannot make this query work

    $query = "LOAD DATA LOCAL INFILE '../var/tmp/insertfile.txt' REPLACE
      INTO TABLE Items
      ENCLOSED BY '\"'
      ESCAPED BY '\\'

    this the error
    check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 6

    something to do with single and double quotes i think. Any suggestions

    LVL 8

    Expert Comment

    Maybe some more escape characters:

    $query = "LOAD DATA LOCAL INFILE '../var/tmp/insertfile.txt' REPLACE
      INTO TABLE Items
      ENCLOSED BY '\"'
      ESCAPED BY '\\\\'
      LINES TERMINATED BY '\\n' ";

    I think MySQL expects the characters to be escaped, so you do not want PHP to expand them already.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
    Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now