php-pdoBatch insert with PHP PDO
We're going to generate ? placeholders and use VALUES(),(),() SQL syntax to insert multiple rows at once.
$data = [
['name' => 'Donald', 'age' => 90],
['name' => 'Joe', 'age' => 95],
];
$place_holder = '(' . implode(',', array_fill(0, count($data[0]), '?')) . ')';
$place_hoders = implode(',', array_fill(0, count($data), $place_holder));
$st = $pdo->prepare('INSERT INTO test(name, age) VALUES' . $place_hoders);
$flat = call_user_func_array('array_merge', array_map('array_values', $data));
$st->execute( $flat );ctrl + c$datalist with multiple arrays to batch insert into table | $place_holdergenerate single |
$place_hodersgenerate all | $pdo->prepareprepare given query to execute |
$flat = flatten our | $st->execute(run query on the server |
Usage example
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'usr', 'pwd');
$data = [
['name' => 'Donald', 'age' => 90],
['name' => 'Joe', 'age' => 95],
];
$place_holder = '(' . implode(',', array_fill(0, count($data[0]), '?')) . ')';
$place_hoders = implode(',', array_fill(0, count($data), $place_holder));
$st = $pdo->prepare('INSERT INTO test(name, age) VALUES' . $place_hoders);
$flat = call_user_func_array('array_merge', array_map('array_values', $data));
$st->execute( $flat );
echo $st->rowCount();output
2More of Php Pdo
- Set charset in PHP PDO
- Fetch single column value with PHP PDO
- Commit transaction in PHP PDO
- How to get results after PHP PDO statement execute()
- Set execution timeout with PHP PDO
- Get last query error with PHP PDO
- Bind current datetime with PHP PDO
- Escape string with PHP PDO
- Get current database name with PHP PDO
- Insert JSON into table with PHP PDO
See more codes...