Insert multiple rows mysql php pdo

Multiple Values Insert with PDO Prepared Statements

Inserting multiple values in one execute statement. Why because according to this page it is faster than regular inserts.

$datafields = array['fielda', 'fieldb', ... ];

$data[] = array['fielda' => 'value', 'fieldb' => 'value' ....];
$data[] = array['fielda' => 'value', 'fieldb' => 'value' ....];

more data values or you probably have a loop that populates data.

With prepared inserts you need to know the fields you're inserting to, and the number of fields to create the ? placeholders to bind your parameters.

insert into table [fielda, fieldb, ... ] values [?,?...], [?,?...]....

That is basically how we want the insert statement to look like.

Now, the code:

function placeholders[$text, $count=0, $separator=","]{
    $result = array[];
    if[$count > 0]{
        for[$x=0; $xbeginTransaction[]; // also helps speed up your inserts.
$insert_values = array[];
foreach[$data as $d]{
    $question_marks[] = '['  . placeholders['?', sizeof[$d]] . ']';
    $insert_values = array_merge[$insert_values, array_values[$d]];
}

$sql = "INSERT INTO table [" . implode[",", $datafields ] . "] VALUES " .
       implode[',', $question_marks];

$stmt = $pdo->prepare [$sql];
$stmt->execute[$insert_values];
$pdo->commit[];

Although in my test, there was only a 1 sec difference when using multiple inserts and regular prepared inserts with single value.

Dharman♦

27.9k21 gold badges75 silver badges127 bronze badges

answered Jan 20, 2010 at 2:30

Herbert BalagtasHerbert Balagtas

1,8771 gold badge12 silver badges6 bronze badges

9

Same answer as Mr. Balagtas, slightly clearer...

Recent versions MySQL and PHP PDO do support multi-row INSERT statements.

SQL Overview

The SQL will look something like this, assuming a 3-column table you'd like to INSERT to.

INSERT INTO tbl_name
            [colA, colB, colC]
     VALUES [?, ?, ?], [?, ?, ?], [?, ?, ?] [,...]

ON DUPLICATE KEY UPDATE works as expected even with a multi-row INSERT; append this:

ON DUPLICATE KEY UPDATE colA = VALUES[colA], colB = VALUES[colB], colC = VALUES[colC]

PHP Overview

Your PHP code will follow the usual $pdo->prepare[$qry] and $stmt->execute[$params] PDO calls.

$params will be a 1-dimensional array of all the values to pass to the INSERT.

In the above example, it should contain 9 elements; PDO will use every set of 3 as a single row of values. [Inserting 3 rows of 3 columns each = 9 element array.]

Implementation

Below code is written for clarity, not efficiency. Work with the PHP array_*[] functions for better ways to map or walk through your data if you'd like. Whether you can use transactions obviously depends on your MySQL table type.

Assuming:

• $tblName - the string name of the table to INSERT to
• $colNames - 1-dimensional array of the column names of the table These column names must be valid MySQL column identifiers; escape them with backticks [``] if they are not
• $dataVals - mutli-dimensional array, where each element is a 1-d array of a row of values to INSERT

Sample Code

// setup data values for PDO
// memory warning: this is creating a copy all of $dataVals
$dataToInsert = array[];

foreach [$dataVals as $row => $data] {
    foreach[$data as $val] {
        $dataToInsert[] = $val;
    }
}

// [optional] setup the ON DUPLICATE column names
$updateCols = array[];

foreach [$colNames as $curCol] {
    $updateCols[] = $curCol . " = VALUES[$curCol]";
}

$onDup = implode[', ', $updateCols];

// setup the placeholders - a fancy way to make the long "[?, ?, ?]..." string
$rowPlaces = '[' . implode[', ', array_fill[0, count[$colNames], '?']] . ']';
$allPlaces = implode[', ', array_fill[0, count[$dataVals], $rowPlaces]];

$sql = "INSERT INTO $tblName [" . implode[', ', $colNames] . 
    "] VALUES " . $allPlaces . " ON DUPLICATE KEY UPDATE $onDup";

// and then the PHP PDO boilerplate
$stmt = $pdo->prepare [$sql];

$stmt->execute[$dataToInsert];

$pdo->commit[];

Dharman♦

27.9k21 gold badges75 silver badges127 bronze badges

answered Dec 30, 2010 at 1:24

jamesvljamesvl

1,60912 silver badges8 bronze badges

4

For what it is worth, I have seen a lot of users recommend iterating through INSERT statements instead of building out as a single string query as the selected answer did. I decided to run a simple test with just two fields and a very basic insert statement:

While the overall query itself took milliseconds or less, the latter [single string] query was consistently 8 times faster or more. If this was built out to say reflect an import of thousands of rows on many more columns, the difference could be enormous.

answered Jan 31, 2012 at 23:28

JM4JM4

6,62218 gold badges75 silver badges123 bronze badges

4

The Accepted Answer by Herbert Balagtas works well when the $data array is small. With larger $data arrays the array_merge function becomes prohibitively slow. My test file to create the $data array has 28 cols and is about 80,000 lines. The final script took 41s to complete.

Using array_push[] to create $insert_values instead of array_merge[] resulted in a 100X speed up with execution time of 0.41s.

The problematic array_merge[]:

$insert_values = array[];

foreach[$data as $d]{
 $question_marks[] = '['  . placeholders['?', sizeof[$d]] . ']';
 $insert_values = array_merge[$insert_values, array_values[$d]];
}

To eliminate the need for array_merge[], you can build the following two arrays instead:

//Note that these fields are empty, but the field count should match the fields in $datafields.
$data[] = array['','','','',... n ]; 

//getting rid of array_merge[]
array_push[$insert_values, $value1, $value2, $value3 ... n ]; 

These arrays can then be used as follows:

function placeholders[$text, $count=0, $separator=","]{
    $result = array[];
    if[$count > 0]{
        for[$x=0; $xbeginTransaction[];

foreach[$data as $d]{
 $question_marks[] = '['  . placeholders['?', sizeof[$d]] . ']';
}

$sql = "INSERT INTO table [" . implode[",", array_keys[$datafield] ] . "] VALUES " . implode[',', $question_marks];

$stmt = $pdo->prepare[$sql];
$stmt->execute[$insert_values];
$pdo->commit[];

Dharman♦

27.9k21 gold badges75 silver badges127 bronze badges

answered May 19, 2012 at 21:18

Chris M.Chris M.

5034 silver badges4 bronze badges

6

Two possible approaches:

$stmt = $pdo->prepare['INSERT INTO foo VALUES[:v1_1, :v1_2, :v1_3],
    [:v2_1, :v2_2, :v2_3],
    [:v2_1, :v2_2, :v2_3]'];
$stmt->bindValue[':v1_1', $data[0][0]];
$stmt->bindValue[':v1_2', $data[0][1]];
$stmt->bindValue[':v1_3', $data[0][2]];
// etc...
$stmt->execute[];

Or:

$stmt = $pdo->prepare['INSERT INTO foo VALUES[:a, :b, :c]'];
foreach[$data as $item]
{
    $stmt->bindValue[':a', $item[0]];
    $stmt->bindValue[':b', $item[1]];
    $stmt->bindValue[':c', $item[2]];
    $stmt->execute[];
}

If the data for all the rows are in a single array, I would use the second solution.

answered Jul 24, 2009 at 9:28

ZyxZyx

4753 silver badges8 bronze badges

3

That's simply not the way you use prepared statements.

It is perfectly okay to insert one row per query because you can execute one prepared statement multiple times with different parameters. In fact that is one of the greatest advantages as it allows you to insert you a great number of rows in an efficient, secure and comfortable manner.

So it maybe possible to implement the scheme you proposing, at least for a fixed number of rows, but it is almost guaranteed that this is not really what you want.

answered Jul 24, 2009 at 9:04

sebasgosebasgo

3,83122 silver badges28 bronze badges

5

A shorter answer: flatten the array of data ordered by columns then

//$array = array[ '1','2','3','4','5', '1','2','3','4','5'];
$arCount = count[$array];
$rCount = [$arCount  ? $arCount - 1 : 0];
$criteria = sprintf["[?,?,?,?,?]%s", str_repeat[",[?,?,?,?,?]", $rCount]];
$sql = "INSERT INTO table[c1,c2,c3,c4,c5] VALUES$criteria";

When inserting a 1,000 or so records you don't want to have to loop through every record to insert them when all you need is a count of the values.

answered Dec 9, 2011 at 21:09

fyryefyrye

2693 silver badges3 bronze badges

Here is my simple approach.

    $values = array[];
    foreach[$workouts_id as $value]{
      $_value = "[".$value.",".$plan_id."]";
      array_push[$values,$_value];
    }
    $values_ = implode[",",$values];

    $sql = "INSERT INTO plan_days[id,name] VALUES" . $values_."";
    $stmt = $this->conn->prepare[$sql];
    $stmt->execute[];

answered Mar 28, 2017 at 18:19

2

Here's a class I wrote do multiple inserts with purge option: