Can PDO_MYSQL and PDO_MYSQLND Execute Multiple SQL Queries in One Statement?

PDO Support for Multiple Queries (PDO_MYSQL, PDO_MYSQLND)

Despite the perception that PDO does not support multiple queries in a single statement, PDO_MYSQL and PDO_MYSQLND provide this functionality.

PDO_MYSQL and PDO_MYSQLND

PDO_MYSQL has been replaced by PDO_MYSQLND in PHP 5.3. Confusingly, the name remains PDO_MYSQL, but PDO_MYSQLND is the default driver for MySQL PDO.

How to Execute Multiple Queries

To execute multiple queries simultaneously, the following requirements must be met:

• PHP 5.3 or later
• mysqlnd extension
• Emulated prepared statements (PDO::ATTR_EMULATE_PREPARES set to 1, which is the default for MySQL)

Methods for Executing Multiple Queries

Using exec:

$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', 'coupe'); 
INSERT INTO car(name, type) VALUES ('car2', 'coupe');";

$db->exec($sql);

Using statements:

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

// Check for errors and collect query results
do {
    echo $pdo->lastInsertId(); // for example
} while ($stmt->nextRowset());

Note: When using emulated prepared statements, set the appropriate encoding in the DSN to avoid potential SQL injection vulnerabilities.

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