As I know, PDO_MYSQLND replaced PDO_MYSQL in PHP 5.3. Confusing part is that name is still PDO_MYSQL. So now ND is default driver for MySQL+PDO.
Overall, to execute multiple queries at once you need:
- PHP 5.3+
- mysqlnd
- Emulated prepared statements. Make sure
PDO::ATTR_EMULATE_PREPARES is set to 1 (default for mysql)
Using exec
$db = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// works regardless of statements emulation
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);
$sql = "
DELETE FROM car;
INSERT INTO car(name, type) VALUES ('car1', 'coupe');
INSERT INTO car(name, type) VALUES ('car2', 'coupe');
";
$db->exec($sql);
Note that this method has a limited use and only suitable for SQL that contains constant values. When the data is supplied for SQL from PHP variables, prepared statements must be used istead:
Using statements
$db = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// wouldn't work if set to 0. You can comment out this line as 1 is a default
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);
$sql = "
DELETE FROM car;
INSERT INTO car(name, type) VALUES (:car1, :type1);
INSERT INTO car(name, type) VALUES (:car2, :type2);
";
$stmt = $db->prepare($sql);
$stmt->execute(
["car1" => "brand1", "type1" => "coupe", "car2" => "brand2", "type2" => "coupe"]
);
// check for errors and collect query results
do {
echo $pdo->lastInsertId(); // for example
} while ($stmt->nextRowset());
Note that in this case you have loop over query results after executing your statement, in order to check for possible errors or collect the query results, as shown above. In case you don't need to collect any results, the loop can be reduced to just
while ($stmt->nextRowset());
which will still check for errors (provided PDO::ATTR_ERRMODE is set to PDO::ERRMODE_EXCEPTION as shown above).
A note:
When using emulated prepared statements, make sure you have set proper encoding (that reflects actual data encoding) in DSN (available since 5.3.6). Otherwise there can be a slight possibility for SQL injection if some odd encoding is used.
