使用PDO执行多个查询 [英] Executing Multiple Queries Using PDO

问题描述

我正在尝试使用PDO准备语句执行以下查询，但是当我调用$query->fetch();时，它将引发异常SQLSTATE[HY000]: General Error.

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.

这是PHP代码(请注意，类Database-或代码中的变量$db-只是类PDO的简单包装，因此所有PDO调用都必须使用$db->pdo->{some PDO function}();完成):

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!

我尝试通过PHPMyAdmin手动执行查询，并且工作正常.我还读到PDO在同一条语句中不支持多个查询，但是在运行$query->execute();时它不应该抛出异常吗?

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();?

此外，$query->rowCount();确实返回1，但是当我尝试获取结果时，它将引发一般错误异常.

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

我尝试了很多其他事情，例如用SELECT LAST_INSERT_ID();替换SELECT语句，但是似乎没有任何作用.

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

感谢您的帮助！

推荐答案

运行您的第一个查询，即插入，然后在成功执行该查询后，获取最后一个insertid，然后在下一个查询中使用该ID. >

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

这篇关于使用PDO执行多个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！