php+mysql: insert a php array into mysql

Question

Hi

I have an array with 30000 plus entries that need to go into a MySQL table.

What is the best practice? From here? Lets say [0], [1] and [2] in the database would be 'title', 'type' and 'customer'

Is it add key names matching the column names in the table and call som "magic" function? Or build the query manually...

Array
(
    [0] => Array
        (
            [0] => 2140395946
            [1] => 1SAP
            [2] => 0041451463
        )

    [1] => Array
        (
            [0] => 2140411607
            [1] => 2SAP
            [2] => 0041411940
        )

    [2] => Array
        (
            [0] => 2140706194
            [1] => 4SAP
            [2] => 0041411943
        )
etc. etc.

UPDATE - based on answers

Thanks for the answers.

The solution would normally be to manually create the SQL-string and all rows can be inserted as one:

INSERT INTO `tx_opengate_table` (`machine` ,`customer` ,`type`)
VALUES
  ('m123', 'dfkj45', 'A'),
  ('m137', 'kfkj49', 'A'), "repeat this line for each entry in the array"
  ... ... ...
  ('m654321', '34dgf456', 'C4') "end with out comma, or remove last comma"
;

Special for TYPO3

I happen to do this in the CMS TYPO3 and just came across a new function added not that long ago:

//Insert new rows
$table = 'tx_opengate_stuff';
$fields = array('machine','type','customer');
$lines = "array as given above"
$GLOBALS['TYPO3_DB']->exec_INSERTmultipleRows($table,$fields,$lines);

Answer 1

Magic function? I'm guessing you mean some sort of DB abstraction layer? IMHO that would just double your work.

Just build the query manually looping through array[] INSERT'ing values as you go.

Answer 2

You will have to build the query manually if you want the best performance during this operation. If you would iteratively add everything using PDO or some abstarction layer, you will have 30000+ insert queries.

Use foreach to iterate over the arraay, build one nested INSERT query that does all the work at once, and just send it to the server.

Answer 3

I would say just build it yourself. You can set it up like this:

$query = "INSERT INTO x (a,b,c) VALUES ";
foreach ($arr as $item) {
  $query .= "('".$item[0]."','".$item[1]."','".$item[2]."'),";
}
$query = rtrim($query,",");//remove the extra comma
//execute query

Don't forget to escape quotes if it's necessary.

Also, be careful that there's not too much data being sent at once. You may have to execute it in chunks instead of all at once.

Answer 4

$statement = "INSERT INTO table (title, type, customer) VALUES ";
foreach( $data as $row) {
   $statement .= ' ("' . explode($row, '","') . '")';
}