Executing Multiple Queries Using PDO

Question

I am trying to execute the following query using a PDO Prepared Statement, but when I call $query->fetch(); it throws an exception SQLSTATE[HY000]: General Error.

This is the PHP code (note that class Database - or the variable $db in code - is just a simple wrapper for the class PDO thus all PDO calls have to be done using $db->pdo->{some PDO function}();):

$db = new Database(); //Create a new object of type Database establishing a connection to the MySQL database
$query = $db->pdo->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);
                         SELECT * FROM `orders` WHERE `ID`=LAST_INSERT_ID();"); //Prepare the two queries to be executed

/*HERE IS SOME CODE TO BIND PLACEHOLDERS TO SOME VALUES*/

if(!$query->execute()){
    error(); //Handle the error and terminate execution
}
if($query->rowCount() != 1){
    error(); //Handle the error and terminate execution
}

$query->setFetchMode(PDO::FETCH_ASSOC);
$order = $query->fetch(); //THIS IS WHERE THE EXCEPTION IS THROWN!

I have tried executing the query manually through PHPMyAdmin and it worked fine. I've also read that PDO doesn't support multiple queries in the same statement, but shouldn't it then throw and exception when running $query->execute();?

Also, $query->rowCount(); DOES return 1, but when I try to fetch the result it throws a general error exception.

I have tried a lot of other things like replacing the SELECT statement with a SELECT LAST_INSERT_ID();, but nothing seems to work.

I would appreciate your help!

`/*HERE IS SOME CODE TO BIND PLACEHOLDERS TO SOME VALUES*/` - So, leaving the code out for the binds you find to be irrelevant, or have you not binded those? Question's unclear (for me). Or, is this question more of a "why this behaviour"? — Funk Forty Niner, Nov 01 '16 at 11:53
Why do you need `SELECT * FROM \`orders\` WHERE `ID`=LAST_INSERT_ID();`? Use http://php.net/manual/en/pdo.lastinsertid.php. — chris85, Nov 01 '16 at 12:02

score 5 · Accepted Answer · answered Nov 01 '16 at 13:00

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

Thank you! This is exactly what I needed! The `$db->lastInsertId();` function. — Tadej Gašparovič, Nov 01 '16 at 15:24
Will this cause issues if there are other statements running in parallel? The advantage of having it in one statement is that you can guarantee execution order. — Akumaburn, Mar 17 '17 at 15:17
@MasivuyeCokile If you have multiple users accessing this function at the same time, In scenario where $db variable is global.. What happens if there is another MYSQL insert before your php worker gets to the `$db->lastInsertID();` line? Will you get your insert, or other user's insert? — Akumaburn, Mar 17 '17 at 15:32

score 2 · Answer 2 · edited Jun 20 '20 at 09:12

You don't need a multiple statement.

So just run your queries one by one

$db = new Database(); //Create a new object of type Database establishing a connection to the MySQL database
$query = $db->pdo->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)"
); //Prepare the first query

/*HERE IS SOME CODE TO BIND PLACEHOLDERS TO SOME VALUES*/

$query->execute();

$order = $db->pdo->query("SELECT * FROM `orders` WHERE `ID`=LAST_INSERT_ID()")->fetch(PDO::FETCH_ASSOC);

score 0 · Answer 3 · edited May 23 '17 at 12:01

0

You would be correct in that PDO (and I believe any PHP method?) does not allow for multiple queries in a single by default. There are some workarounds which you can read about more, such as:

PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)

but they do increase the risk of SQL injection so it is ill-advised.

edited May 23 '17 at 12:01

Community

1
1

answered Nov 01 '16 at 12:07

bucketman

463
6
15

2

There is mysqli_multi_query in php this could handle multiple query results in one call but you will need to use mysqli_connect for the connection – Christopher Pelayo Sep 16 '19 at 01:13

Executing Multiple Queries Using PDO

3 Answers3

You don't need a multiple statement.

Linked

Related