How Can I Execute Multiple Queries with PDO in PHP?

PDO Support for Multiple Queries: PDO_MYSQL and PDO_MYSQLND

The traditional PDO doesn't natively support executing multiple queries in a single statement. However, PDO_MYSQL and PDO_MYSQLND were developed to address this limitation.

PDO_MYSQL and PDO_MYSQLND

PDO_MYSQL is now considered deprecated. In fact, since PHP 5.3, MySQL PDO has been using the PDO_MYSQLND driver by default.

Executing Multiple Queries with PDO

To execute multiple queries with PDO, you require the following:

• PHP 5.3
• PDO_MYSQLND driver (available as the default MySQL driver)
• Emulated prepared statements (PDO::ATTR_EMULATE_PREPARES set to 1)

Using exec

For SQL queries with constant values, the exec() method can be used:

$db->exec("
    DELETE FROM car;
    INSERT INTO car(name, type) VALUES ('car1', 'coupe');
    INSERT INTO car(name, type) VALUES ('car2', 'coupe');
");

Using Prepared Statements

For queries with data from PHP variables, prepared statements are necessary:

$stmt = $db->prepare("
    DELETE FROM car;
    INSERT INTO car(name, type) VALUES (:car1, :type1);
    INSERT INTO car(name, type) VALUES (:car2, :type2);
");

$stmt->execute([
    "car1" => "brand1",
    "type1" => "coupe",
    "car2" => "brand2",
    "type2" => "coupe"
]);

while ($stmt->nextRowset()) {
    echo $db->lastInsertId();
}

Note that in both cases, it's essential to set PDO::ATTR_EMULATE_PREPARES to 1 when using multiple queries.

Encoding Note

When using emulated prepared statements, ensure you specify the correct encoding in the DSN to prevent potential SQL injection vulnerabilities.

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