How Can I Execute Multiple Queries in a Single PDO Statement with PDO_MYSQL and PDO_MYSQLND?

PDO Support for Multiple Queries with PDO_MYSQL and PDO_MYSQLND

Although PDO typically doesn't allow multiple queries in a single statement, the extensions PDO_MYSQL and PDO_MYSQLND provide support for this functionality.

PDO_MYSQLND has since replaced PDO_MYSQL in PHP 5.3, with PDO_MYSQL remaining as the default driver for MySQL PDO. To execute multiple queries with PDO, the following requirements must be met:

• PHP version 5.3 or higher
• MySQL Native Driver (mysqlnd) enabled
• Emulated prepared statements, with PDO::ATTR_EMULATE_PREPARES set to 1 (the default for MySQL)

Using exec()

The exec() method can execute multiple queries when:

$db = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$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);

This method is limited to executing SQL statements with constant values.

Using Prepared Statements

When data is sourced from PHP variables, prepared statements must be used:

$db = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$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"]);

while ($stmt->nextRowset()) {
    echo $db->lastInsertId(); // as an example
}

This approach allows for error checking and collection of query results through iteration over rowsets.

Caution with Emulated Prepared Statements

When using emulated prepared statements, ensure that the encoding in the DSN (available since PHP 5.3.6) matches the actual data encoding. Otherwise, there may be a potential for SQL injection with certain encodings.

The above is the detailed content of How Can I Execute Multiple Queries in a Single PDO Statement with PDO_MYSQL and PDO_MYSQLND?. For more information, please follow other related articles on the PHP Chinese website!