PDO Prepared Inserts multiple rows in single query
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; $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);
$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.
What is the best way to insert multiple rows in PHP PDO MYSQL?
You have at least these two options:
$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.
PDO and binding multiple value sets during insert - recently
Just create your query text wtih ?
placeholders as:
INSERT INTO table (firstName, lastName) VALUES (?, ?),(?, ?),(?, ?)
And execute it. Sample code can be:
$data = ['Joe', 'Smith','Fred','Sampson','Lisa','Pearce'];
$placeholders = ['(?, ?)', '(?, ?)', '(?, ?)']; // but you should define this data according to your data
$query = 'INSERT INTO table (firstName, lastName) VALUES ' . implode(',', $placeholders);
$stmt = $dbh->prepare($query);
$stmt->execute($data);
PDO MySQL: Insert multiple rows in one query
An easy way for this avoiding the complications would be something like this
$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();
}
However, this executes the statement multiple times. So, it is better if we create a long single query in order to do this.
Here is an example of how we can do this.
$query = "INSERT INTO foo (key1, key2) VALUES "; //Prequery
$qPart = array_fill(0, count($data), "(?, ?)");
$query .= implode(",",$qPart);
$stmt = $dbh -> prepare($query);
$i = 1;
foreach($data as $item) { //bind the values one by one
$stmt->bindValue($i++, $item['key1']);
$stmt->bindValue($i++, $item['key2']);
}
$stmt -> execute(); //execute
How to insert multiple rows with PDO prepared statmenets based off of a SELECT query?
As you're trying to select all rows from one table, and insert into another, there are actually various ways of doing this.
Using the code you've started above, you could use a prepared query:
$groupid = $_GET['groupid'];
$myquery = $con->prepare("SELECT e.id AS empid FROM employees e WHERE e.groupid = :groupid");
$myquery->execute([':groupid' => $groupid]);
$insert_query = $con->prepare("INSERT INTO new_table (empid) VALUE (:empid)");
$insert_query->bindParam(':empid', $empid);
while ($result = $myquery->fetch()) {
if ($empid = $result['empid']) {
$insert_query->execute();
}
}
However, this is rather slow, and instead, I'd recommend a single query:
$groupid = $_GET['groupid'];
$con->prepare("INSERT INTO new_table (empid) SELECT e.id FROM employees WHERE groupid = :group_id");
$con->execute(['group_id' => $group_id]);
This has the advantage of passing off all the logic to the SQL server, so you're not passing data back to the PHP only for it to be passed back to the SQL server. Obviously, if you need to do any PHP processing, then you'll need the first option.
Insert multiple rows using PHP PDO
$stmt->execute($array);
wants an associative array with the PDO bindings, you give a array of such arrays -> you have to do it for each of those so (as Your Common Sense already said) :
foreach ($bundleParams as $row)
$stmt->execute($row);
}
Related Topics
How to Rename Sub-Array Keys in PHP
Running Two PHP Versions on the Same Server
Disable Laravel's Built-In Error Handling Methods
Curl , Get Redirect Url to a Variable
Converting Object to JSON and JSON to Object in PHP, (Library Like Gson for Java)
How to Set Default Value for Form Field in Symfony2
Laravel Eloquent Display Query Log
Include, Include_Once, Require or Require_Once
PHP Function to Delete All Between Certain Character(S) in String
How to Get System Environment Variables into PHP While Running Cli & Apache2Handler
How to Read a List of Files from a Folder Using PHP
How to Find the Date of a Day of the Week from a Date Using PHP
Check If a Variable Is Undefined in PHP
How to Use Mamp's Version of PHP Instead of the Default on Osx