Pdo Multiple Queries

PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)

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). Alternatively you can avoid using prepared statements and use $pdo->exec directly.

Using exec

$db = new PDO("mysql:host=localhost;dbname=test", 'root', '');

// 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);

Using statements

$db = new PDO("mysql:host=localhost;dbname=test", 'root', '');

// works not with the following set to 0. You can comment this line as 1 is default
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);

$sql = "
DELETE FROM car;
INSERT INTO car(name, type) VALUES ('car1', 'coupe');
INSERT INTO car(name, type) VALUES ('car2', 'coupe');
";

$stmt = $db->prepare($sql);
$stmt->execute();


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.

Executing Multiple Queries Using PDO

Run your first query which is the insert then after success on that one get the last insertid then use the id on your next query.. Eg.

<?php

try {

$db = new Database(); //Create a new object of type Database establishing a connection to the MySQL database

$query = $db->prepare("INSERT INTO orders (order_type`, `item`, `amount`, `price`, `price_btc`, `status`, `timestamp`, `placed_by`, `secret`, `first_name`, `last_name`, `address_1`, `address_2`, `city`, `zip_code`, `country`, `state`, `phone_number`) VALUES(:order_type, :item, :amount, :price, :price_btc, :status, :timestamp, :placed_by, :secret, :first_name, :last_name, :address_1, :address_2, :city, :zip_code, :country, :state, :phone_number)");

$query->execute(array( /* your values*/ ));

$lastId = $db->lastInsertId(); // fetch last insert id, after success.

$order = $db->prepare("SELECT * FROM `orders` WHERE `ID`=?");
$order->bindValue(1, $lastId);
$order->execute();
//Fetch your records and display.

}
catch (PDOException $e) {
echo "Error : " . $e->getMessage();

}

?>

I left some part of the codes like you did, but the important thing is to run the insert first then collect the last

PHP PDO how to run a multiple query request?

There are three queries in this request and therefore you have to run them in three calls, not one:

$objPdo->query("CREATE TEMPORARY TABLE r1
SELECT CONCAT(MONTH(Heure_deb),'/',DAY(Heure_deb)) as 'Date',
Heure_deb,
Operateur.Id_op ,
Nom_op ,
Prenom_op,
Nom_act ,
TIME(Heure_deb) as heure,
Commentaire,
Type
FROM Operateur, Pointage, Activite
WHERE Operateur.Id_op = Pointage.Id_op
AND Activite.Id_act = Pointage.Id_act
ORDER BY date, Id_op, heure
;";

$objPdo->query("Create temporary table r2
SELECT a.Id_op, a.Heure_deb, MIN(b.heure_deb) as fin, TIMEDIFF(b.Heure_deb, a.Heure_deb) as Time_Difference, ROUND(HOUR(TIMEDIFF(b.Heure_deb, a.Heure_deb)) + MINUTE(TIMEDIFF(b.Heure_deb, a.Heure_deb))/60,2) as Decimal_duree
FROM Pointage a
LEFT JOIN Pointage b ON a.Id_op = b.Id_op
WHERE a.heure_deb < b.heure_deb
Group by a.Id_op, a.Heure_deb
;";

$result = $objPdo->query("select CONCAT(MONTH(r1.Heure_deb),'/',DAY(r1.Heure_deb)) as 'Date',
TIME(r1.Heure_deb) as heure,
r1.Id_op ,
Nom_op ,
Prenom_op,
Nom_act ,
Commentaire,
Type,
Time_Difference,
Decimal_duree
from r1
LEFT JOIN r2 ON r1.Id_op = r2.Id_op and r1.heure_deb = r2.heure_deb
Order by Id_op, Date , heure";

while($row=$result->fetch()){
echo"<tr>
<td>".$row['Date']."</td>
<td>".$row['heure']."</td>
<td>".$row['Nom_op']."</td>
<td>".$row['Prenom_op']."</td>
<td>".$row['Type']."</td>
<td>".$row['Time_Difference']."</td>
<td>".$row['Decimal_duree']."</td>
<td>".$row['Commentaire']."</td>
</tr>";
}
echo"</table></div>";

Multiple queries/results (PDO/MySQL)

Split the entry string by the delimiter of ";", cycle through the released array.

That'd be one way at least.

Here's an alternative.

Here's a link to the PDOStatement::nextRowset page. Read the example.

PDO multiple queries

It turns out that you need to use PDOStatement::nextRowset.

$stmt   = $db->query("SELECT 1; SELECT 2;");
$stmt->nextRowset();
var_dump( $stmt->fetchAll(PDO::FETCH_ASSOC) );

This will return result for the second query.

It is a bit odd implementation. It would certainly be easier if multi-query statement would just return both results sets under one array. However, the advantage is that this implementation allows to fetch every query using different FETCH styles.

How can I get an error when running multiple queries with PDO?

I found the answer in using a prepared statement. After looping through all rowsets, I can check if the last query executed caused an error using $stmt->errorInfo().

$db = new PDO("mysql:host=localhost;dbname=test", 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);

$sql = "
DELETE FROM car;
INSERT INTO car(name, type) SELECT name, from FROM vehicle;
";

$stmt = $db->prepare($sql);
$stmt->execute();
$i = 0;

do {
$i++;
} while ($stmt->nextRowset());

$error = $stmt->errorInfo();
if ($error[0] != "00000") {
echo "Query $i failed: " . $error[2];
die();
}

PDO lastInsertID() failing due to running multiple queries in a single call

First of all, I would strongly recommend to run every query in a distinct API call. This is how an Application Programming Interface is intended to work.

It won't only prevent situations like this but also will make your code a multitude times more readable and maintainable.

And it will make your code much safer too. You can run multiple statements in a single call only at the expense of the native prepared statements. However virtual this vulnerability is, why taking chances at all?

Why not to make a regular SELECT query instead of SET, get the resulting value into a PHP variable and then use it among other variables, just through a placeholder? I don't see any reason why there should be such a complex way to deal with simple data.

In case I failed to convince you, the reason is simple. You are running two queries, and the first one doesn't trigger any insert ids. And obviously, you need this query's metadata (errors, affected rows, whatever), not the other one's first. So you get it. And to get the second'query's metadata you have to ask a database for it. The process is explained in my article: Treating PHP delusions - The only proper PDO tutorial: Running multiple queries with PDO. Basically PDOStatement::nextRowset() is what you need.



Related Topics



Leave a reply



Submit