Tadej Gašparovič Tadej Gašparovič - 1 month ago 7
MySQL Question

Executing Multiple Queries Using PDO

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!

Answer

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

Comments