Rectangle 27 110

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 (?,?...), (?,?...)....
function placeholders($text, $count=0, $separator=","){
    $result = array();
    if($count > 0){
        for($x=0; $x<$count; $x++){
            $result[] = $text;
        }
    }

    return implode($separator, $result);
}

$pdo->beginTransaction(); // 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);
try {
    $stmt->execute($insert_values);
} catch (PDOException $e){
    echo $e->getMessage();
}
$pdo->commit();

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

I agree and have supported your suggestion with some test data below for reference.

A typo, in the explanation above it mentions $datafields although $datafield is used in $sql. Thus copy paste would result in error. Please do rectify. Thanks for this solution though.

Used this for a while then noticed that values with single quotes in them aren't escaped properly. Using double quotes on implosion works like a charm for me: $a[] = '("' . implode(",", $question_marks) . '", NOW())';

array_merge seems more expensive than just using a array_push.

When you say "there was only a 1 sec difference", how many rows a data were you inserting? 1 sec is pretty significant depending on the context.

php - PDO Prepared Inserts multiple rows in single query - Stack Overf...

php pdo insert prepared-statement
Rectangle 27 56

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

INSERT
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)
$pdo->prepare($qry)
$stmt->execute($params)

$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.)

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.

  • $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
// 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);

try {
   $stmt->execute($dataToInsert);
} catch (PDOException $e){
   echo $e->getMessage();
}

$pdo->commit();

That is really too bad that PDO handles it this way, there are some very elegant ways to do this in other DB drivers.

$rowPlaces
$allPlaces = implode(',', array_fill(0, count($dataVals), '('.str_pad('', (count($colNames)*2)-1, '?,').')'));

Works perfect. I would add to this answer the need to ensure the uniqueness of the (combination of) indexes in the table. Like in ALTER TABLE votes ADD UNIQUE unique_index(user, email, address);

array_push($dataToInsert, ...array_values($dataVals));
foreach ($dataVals as $row => $data) {}

php - PDO Prepared Inserts multiple rows in single query - Stack Overf...

php pdo insert prepared-statement
Rectangle 27 107

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. http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

$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 (?,?...), (?,?...)....
function placeholders($text, $count=0, $separator=","){
    $result = array();
    if($count > 0){
        for($x=0; $x<$count; $x++){
            $result[] = $text;
        }
    }

    return implode($separator, $result);
}
$pdo->beginTransaction(); // 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);
try {
    $stmt->execute($insert_values);
} catch (PDOException $e){
    echo $e->getMessage();
}
$pdo->commit();

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

I agree and have supported your suggestion with some test data below for reference.

A typo, in the explanation above it mentions $datafields although $datafield is used in $sql. Thus copy paste would result in error. Please do rectify. Thanks for this solution though.

Used this for a while then noticed that values with single quotes in them aren't escaped properly. Using double quotes on implosion works like a charm for me: $a[] = '("' . implode(",", $question_marks) . '", NOW())';

array_merge seems more expensive than just using a array_push.

When you say "there was only a 1 sec difference", how many rows a data were you inserting? 1 sec is pretty significant depending on the context.

php - PDO Prepared Inserts multiple rows in single query - Stack Overf...

php pdo insert prepared-statement
Rectangle 27 106

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. http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

$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 (?,?...), (?,?...)....
function placeholders($text, $count=0, $separator=","){
    $result = array();
    if($count > 0){
        for($x=0; $x<$count; $x++){
            $result[] = $text;
        }
    }

    return implode($separator, $result);
}
$pdo->beginTransaction(); // 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);
try {
    $stmt->execute($insert_values);
} catch (PDOException $e){
    echo $e->getMessage();
}
$pdo->commit();

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

I agree and have supported your suggestion with some test data below for reference.

A typo, in the explanation above it mentions $datafields although $datafield is used in $sql. Thus copy paste would result in error. Please do rectify. Thanks for this solution though.

Used this for a while then noticed that values with single quotes in them aren't escaped properly. Using double quotes on implosion works like a charm for me: $a[] = '("' . implode(",", $question_marks) . '", NOW())';

array_merge seems more expensive than just using a array_push.

When you say "there was only a 1 sec difference", how many rows a data were you inserting? 1 sec is pretty significant depending on the context.

php - PDO Prepared Inserts multiple rows in single query - Stack Overf...

php pdo insert prepared-statement
Rectangle 27 53

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

INSERT
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)
$pdo->prepare($qry)
$stmt->execute($params)

$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.)

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.

  • $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
// 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);

try {
   $stmt->execute($dataToInsert);
} catch (PDOException $e){
   echo $e->getMessage();
}

$pdo->commit();

That is really too bad that PDO handles it this way, there are some very elegant ways to do this in other DB drivers.

$rowPlaces
$allPlaces = implode(',', array_fill(0, count($dataVals), '('.str_pad('', (count($colNames)*2)-1, '?,').')'));

Works perfect. I would add to this answer the need to ensure the uniqueness of the (combination of) indexes in the table. Like in ALTER TABLE votes ADD UNIQUE unique_index(user, email, address);

array_push($dataToInsert, ...array_values($dataVals));
foreach ($dataVals as $row => $data) {}

php - PDO Prepared Inserts multiple rows in single query - Stack Overf...

php pdo insert prepared-statement
Rectangle 27 53

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

INSERT
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)
$pdo->prepare($qry)
$stmt->execute($params)

$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.)

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.

  • $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
// 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);

try {
   $stmt->execute($dataToInsert);
} catch (PDOException $e){
   echo $e->getMessage();
}

$pdo->commit();

That is really too bad that PDO handles it this way, there are some very elegant ways to do this in other DB drivers.

$rowPlaces
$allPlaces = implode(',', array_fill(0, count($dataVals), '('.str_pad('', (count($colNames)*2)-1, '?,').')'));

Works perfect. I would add to this answer the need to ensure the uniqueness of the (combination of) indexes in the table. Like in ALTER TABLE votes ADD UNIQUE unique_index(user, email, address);

array_push($dataToInsert, ...array_values($dataVals));
foreach ($dataVals as $row => $data) {}

php - PDO Prepared Inserts multiple rows in single query - Stack Overf...

php pdo insert prepared-statement
Rectangle 27 7

$stmt = $db->prepare('INSERT INTO ids VALUES (0, :url)');
try {
 $db->beginTransaction();
 foreach ($ursl as $url) {
   $stmt->bindValue(':url', $url);
   $stmt->execute();
 }
 $db->commit();
} catch (PDOException $e) {
 $db->rollBack();
}

php - PDO Prepared Inserts multiple rows in single query - Stack Overf...

php pdo insert prepared-statement
Rectangle 27 7

$stmt = $db->prepare('INSERT INTO ids VALUES (0, :url)');
try {
 $db->beginTransaction();
 foreach ($ursl as $url) {
   $stmt->bindValue(':url', $url);
   $stmt->execute();
 }
 $db->commit();
} catch (PDOException $e) {
 $db->rollBack();
}

php - PDO Prepared Inserts multiple rows in single query - Stack Overf...

php pdo insert prepared-statement
Rectangle 27 34

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:

<?php
require('conn.php');

$fname = 'J';
$lname = 'M';

$time_start = microtime(true);
$stmt = $db->prepare('INSERT INTO table (FirstName, LastName) VALUES (:fname, :lname)');

for($i = 1; $i <= 10; $i++ )  {
    $stmt->bindParam(':fname', $fname);
    $stmt->bindParam(':lname', $lname);
    $stmt->execute();

    $fname .= 'O';
    $lname .= 'A';
}


$time_end = microtime(true);
$time = $time_end - $time_start;

echo "Completed in ". $time ." seconds <hr>";

$fname2 = 'J';
$lname2 = 'M';

$time_start2 = microtime(true);
$qry = 'INSERT INTO table (FirstName, LastName) VALUES ';
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?)";

$stmt2 = $db->prepare($qry);
$values = array();

for($j = 1; $j<=10; $j++) {
    $values2 = array($fname2, $lname2);
    $values = array_merge($values,$values2);

    $fname2 .= 'O';
    $lname2 .= 'A';
}

$stmt2->execute($values);

$time_end2 = microtime(true);
$time2 = $time_end2 - $time_start2;

echo "Completed in ". $time2 ." seconds <hr>";
?>

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.

@JM4 - great idea to put 10 rows directly in one execution. But how can I insert thousands of rows when they are stored in an object like JSON? My code below works perferctly. But how can I adjust it to insert 10 rows in one execution? ` foreach($json_content as $datarow) { $id = $datarow[id]; $date = $datarow[date]; $row3 = $datarow[row3]; $row4 = $datarow[row4]; $row5 = $datarow[row5]; $row6 = $datarow[row6]; $row7= $datarow[row7]; // now execute $databaseinsert->execute(); } // end of foreach `

@JM4 - ... and my second question is: "why is there no bind_param statement in the second import routine"?

php - PDO Prepared Inserts multiple rows in single query - Stack Overf...

php pdo insert prepared-statement
Rectangle 27 33

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:

<?php
require('conn.php');

$fname = 'J';
$lname = 'M';

$time_start = microtime(true);
$stmt = $db->prepare('INSERT INTO table (FirstName, LastName) VALUES (:fname, :lname)');

for($i = 1; $i <= 10; $i++ )  {
    $stmt->bindParam(':fname', $fname);
    $stmt->bindParam(':lname', $lname);
    $stmt->execute();

    $fname .= 'O';
    $lname .= 'A';
}


$time_end = microtime(true);
$time = $time_end - $time_start;

echo "Completed in ". $time ." seconds <hr>";

$fname2 = 'J';
$lname2 = 'M';

$time_start2 = microtime(true);
$qry = 'INSERT INTO table (FirstName, LastName) VALUES ';
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?)";

$stmt2 = $db->prepare($qry);
$values = array();

for($j = 1; $j<=10; $j++) {
    $values2 = array($fname2, $lname2);
    $values = array_merge($values,$values2);

    $fname2 .= 'O';
    $lname2 .= 'A';
}

$stmt2->execute($values);

$time_end2 = microtime(true);
$time2 = $time_end2 - $time_start2;

echo "Completed in ". $time2 ." seconds <hr>";
?>

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.

@JM4 - great idea to put 10 rows directly in one execution. But how can I insert thousands of rows when they are stored in an object like JSON? My code below works perferctly. But how can I adjust it to insert 10 rows in one execution? ` foreach($json_content as $datarow) { $id = $datarow[id]; $date = $datarow[date]; $row3 = $datarow[row3]; $row4 = $datarow[row4]; $row5 = $datarow[row5]; $row6 = $datarow[row6]; $row7= $datarow[row7]; // now execute $databaseinsert->execute(); } // end of foreach `

@JM4 - ... and my second question is: "why is there no bind_param statement in the second import routine"?

php - PDO Prepared Inserts multiple rows in single query - Stack Overf...

php pdo insert prepared-statement
Rectangle 27 33

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:

<?php
require('conn.php');

$fname = 'J';
$lname = 'M';

$time_start = microtime(true);
$stmt = $db->prepare('INSERT INTO table (FirstName, LastName) VALUES (:fname, :lname)');

for($i = 1; $i <= 10; $i++ )  {
    $stmt->bindParam(':fname', $fname);
    $stmt->bindParam(':lname', $lname);
    $stmt->execute();

    $fname .= 'O';
    $lname .= 'A';
}


$time_end = microtime(true);
$time = $time_end - $time_start;

echo "Completed in ". $time ." seconds <hr>";

$fname2 = 'J';
$lname2 = 'M';

$time_start2 = microtime(true);
$qry = 'INSERT INTO table (FirstName, LastName) VALUES ';
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?)";

$stmt2 = $db->prepare($qry);
$values = array();

for($j = 1; $j<=10; $j++) {
    $values2 = array($fname2, $lname2);
    $values = array_merge($values,$values2);

    $fname2 .= 'O';
    $lname2 .= 'A';
}

$stmt2->execute($values);

$time_end2 = microtime(true);
$time2 = $time_end2 - $time_start2;

echo "Completed in ". $time2 ." seconds <hr>";
?>

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.

@JM4 - great idea to put 10 rows directly in one execution. But how can I insert thousands of rows when they are stored in an object like JSON? My code below works perferctly. But how can I adjust it to insert 10 rows in one execution? ` foreach($json_content as $datarow) { $id = $datarow[id]; $date = $datarow[date]; $row3 = $datarow[row3]; $row4 = $datarow[row4]; $row5 = $datarow[row5]; $row6 = $datarow[row6]; $row7= $datarow[row7]; // now execute $databaseinsert->execute(); } // end of foreach `

@JM4 - ... and my second question is: "why is there no bind_param statement in the second import routine"?

php - PDO Prepared Inserts multiple rows in single query - Stack Overf...

php pdo insert prepared-statement
Rectangle 27 5

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ];

$sql = "insert into `table_name` (col1, col2, col3) values (?,?,?)";

$stmt = $db->prepare($sql);

foreach($rows as $row)
{
    $stmt->execute($row);
}

OR:

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ];

$sql = "insert into `table_name` (col1, col2, col3) values ";

$paramArray = array();

$sqlArray = array();

foreach($rows as $row)
{
    $sqlArray[] = '(' . implode(',', array_fill(0, count($row), '?')) . ')';

    foreach($row as $element)
    {
        $paramArray[] = $element;
    }
}

// $sqlArray will look like: ["(?,?,?)", "(?,?,?)", ... ]

// Your $paramArray will basically be a flattened version of $rows.

$sql .= implode(',', $sqlArray);

$stmt = $db->prepare($sql);

$stmt->execute($paramArray);

As you can see the first version features a lot simpler code; however the second version does execute a batch insert. The batch insert should be faster, but I agree with @BillKarwin that the performance difference will not be noticed in the vast majority of implementations.

What is the best way to insert multiple rows in PHP PDO MYSQL? - Stack...

php mysql pdo sql-insert
Rectangle 27 5

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ];

$sql = "insert into `table_name` (col1, col2, col3) values (?,?,?)";

$stmt = $db->prepare($sql);

foreach($rows as $row)
{
    $stmt->execute($row);
}

OR:

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ];

$sql = "insert into `table_name` (col1, col2, col3) values ";

$paramArray = array();

$sqlArray = array();

foreach($rows as $row)
{
    $sqlArray[] = '(' . implode(',', array_fill(0, count($row), '?')) . ')';

    foreach($row as $element)
    {
        $paramArray[] = $element;
    }
}

// $sqlArray will look like: ["(?,?,?)", "(?,?,?)", ... ]

// Your $paramArray will basically be a flattened version of $rows.

$sql .= implode(',', $sqlArray);

$stmt = $db->prepare($sql);

$stmt->execute($paramArray);

As you can see the first version features a lot simpler code; however the second version does execute a batch insert. The batch insert should be faster, but I agree with @BillKarwin that the performance difference will not be noticed in the vast majority of implementations.

What is the best way to insert multiple rows in PHP PDO MYSQL? - Stack...

php mysql pdo sql-insert
Rectangle 27 23

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.

$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; $x<$count; $x++){
            $result[] = $text;
        }
    }

    return implode($separator, $result);
}

$pdo->beginTransaction();

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);
try {
    $stmt->execute($insert_values);
} catch (PDOException $e){
    echo $e->getMessage();
}
$pdo->commit();
array_push($data, ...array_values($row))
$data = array_merge($data, array_values($row));

Why 5.6 ? Documentation doesn't say anything about 5.6, array_push() is available even in php 4.

@Piero it is PHP 5.6+ only code not because of the use of array_push(), but because @Mark is using argument unpacking. Notice the ...array_values() call there?

@mariano.iglesias array_values() is as well available in php 4. Not sure if that's what you mean by argument unpacking.

php - PDO Prepared Inserts multiple rows in single query - Stack Overf...

php pdo insert prepared-statement
Rectangle 27 21

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.

$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; $x<$count; $x++){
            $result[] = $text;
        }
    }

    return implode($separator, $result);
}

$pdo->beginTransaction();

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);
try {
    $stmt->execute($insert_values);
} catch (PDOException $e){
    echo $e->getMessage();
}
$pdo->commit();
array_push($data, ...array_values($row))
$data = array_merge($data, array_values($row));

Why 5.6 ? Documentation doesn't say anything about 5.6, array_push() is available even in php 4.

@Piero it is PHP 5.6+ only code not because of the use of array_push(), but because @Mark is using argument unpacking. Notice the ...array_values() call there?

@mariano.iglesias array_values() is as well available in php 4. Not sure if that's what you mean by argument unpacking.

php - PDO Prepared Inserts multiple rows in single query - Stack Overf...

php pdo insert prepared-statement
Rectangle 27 21

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.

$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; $x<$count; $x++){
            $result[] = $text;
        }
    }

    return implode($separator, $result);
}

$pdo->beginTransaction();

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);
try {
    $stmt->execute($insert_values);
} catch (PDOException $e){
    echo $e->getMessage();
}
$pdo->commit();
array_push($data, ...array_values($row))
$data = array_merge($data, array_values($row));

Why 5.6 ? Documentation doesn't say anything about 5.6, array_push() is available even in php 4.

@Piero it is PHP 5.6+ only code not because of the use of array_push(), but because @Mark is using argument unpacking. Notice the ...array_values() call there?

@mariano.iglesias array_values() is as well available in php 4. Not sure if that's what you mean by argument unpacking.

php - PDO Prepared Inserts multiple rows in single query - Stack Overf...

php pdo insert prepared-statement
Rectangle 27 15

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.

Can you suggest a better way to insert multiple rows into a table?

@Crashthatch: Just do it the naive way: Setup the prepared statement once, then execute it for each row with different values for the bound parameters. That's the second approach in Zyk's answer.

The purpose you mentioned for prepared statement is right. But, using multi -insert is another technique to improve insert speed and it can be used with prepared statement too. In my experience, while migrating 30 million row data using PDO prepared statement, I saw multi-insert was 7-10 times faster then grouped single insert in transactions.

php - PDO Prepared Inserts multiple rows in single query - Stack Overf...

php pdo insert prepared-statement
Rectangle 27 15

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.

Can you suggest a better way to insert multiple rows into a table?

@Crashthatch: Just do it the naive way: Setup the prepared statement once, then execute it for each row with different values for the bound parameters. That's the second approach in Zyk's answer.

The purpose you mentioned for prepared statement is right. But, using multi -insert is another technique to improve insert speed and it can be used with prepared statement too. In my experience, while migrating 30 million row data using PDO prepared statement, I saw multi-insert was 7-10 times faster then grouped single insert in transactions.

php - PDO Prepared Inserts multiple rows in single query - Stack Overf...

php pdo insert prepared-statement
Rectangle 27 15

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.

Can you suggest a better way to insert multiple rows into a table?

@Crashthatch: Just do it the naive way: Setup the prepared statement once, then execute it for each row with different values for the bound parameters. That's the second approach in Zyk's answer.

The purpose you mentioned for prepared statement is right. But, using multi -insert is another technique to improve insert speed and it can be used with prepared statement too. In my experience, while migrating 30 million row data using PDO prepared statement, I saw multi-insert was 7-10 times faster then grouped single insert in transactions.

php - PDO Prepared Inserts multiple rows in single query - Stack Overf...

php pdo insert prepared-statement
Rectangle 27 4

The problem you are running into is you are calling execute() before you have bound the proper number of parameters. Instead, you need to bind all of your parameters first, then call execute().

$db->beginTransaction();

$stmt = $db->prepare("INSERT INTO mytable (column1, column2) VALUES (:blah, :whatever)");

foreach($test as $insertRow){

   // now loop through each inner array to match binded values
   foreach($insertRow as $column => $value){
      $stmt->bindParam(":{$column}", $value);
   }

}

// NOW DO EXECUTE
$stmt->execute();

$db->commit();

In response to your comment, it's hard to tell exactly what you are trying to accomplish, but if you are only receiving one record, then it is because of what Gerald brought up, these are separate queries to all be transacted at once. Take a look at this revision:

// Start Transaction
$db->beginTransaction();

// Insert each record
foreach($test as $insertRow){

   // Prepare statement
   $stmt = $db->prepare("INSERT INTO mytable (column1, column2) VALUES (:blah, :whatever)");

   // now loop through each inner array to match binded values
   foreach($insertRow as $column => $value){
      $stmt->bindValue(":{$column}", $value);
   }

   // Execute statement to add to transaction
   $stmt->execute();

   // Clear statement for next record (not necessary, but good practice)
   $stmt = null;
}

// Commit all inserts
$db->commit();

I think the execute();belongs inside of the outer foreach loop

In that case, the outer foreach would need to wrap outside the prepare() statement as well. It's hard to tell when the parameters are ":blah" and ":whatever" so I can't really match it up with the array data.

Thanks for your suggestion. I get no errors but I only get 1 record inserted instead of 3000 which i expect and just noticed it doesn't actually insert any data except the timestamp i have set in the database

VALUES (:blah, :whatever)

PHP PDO batch insert from multi dimensional array failing to insert in...

php mysql sql pdo bulkinsert