PDO多查询"SQLSTATE [HY000]:一般错误"； [英] PDO Multi-query "SQLSTATE[HY000]: General error"

问题描述

我仍在学习PDO，所以我可能会错过一些东西，但是基本上我正在尝试在表中插入一行，然后选择生成的ID.

I'm still learning PDO so I might of missed something but basically I'm trying to insert a row into a table and then select the generated id.

我不确定它是否喜欢一个pdo语句中的两个查询.这是我用来执行SQL的代码.

I'm not sure if it likes both queries in one pdo statement. Here is the code I'm using to execute the SQL.

public function ExecuteQuery($sql, $params = array())
    {

        if($this->_handle == null)
            $this->Connect();

        $query = $this->_handle->prepare($sql);

        foreach($params as $key => $value)
        {
            if(is_int($value)){
                $query->bindValue(':'.$key, $value, \PDO::PARAM_INT);
            }else if(is_bool($value)){
                $query->bindValue(':'.$key, $value, \PDO::PARAM_BOOL);
            }else if(is_null($value)){
                $query->bindValue(':'.$key, $value, \PDO::PARAM_NULL);
            }else{
                $query->bindValue(':'.$key, $value, \PDO::PARAM_STR);
            }
        }

        $query->execute();

        $x = $query->fetchAll(\PDO::FETCH_ASSOC);

        var_dump($x);

        return $x;
    }

此函数是数据库类的一部分，$ this-> _ handle是PDO对象.

This function is part of a database class, $this->_handle is the PDO object.

public function Connect()
    {
        try {
          $this->_handle = new \PDO('mysql:host='.$this->_host.';dbname='.$this->_database, $this->_username, $this->_password);
          $this->_handle->setAttribute( \PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION ); 
        }
        catch(PDOException $e) {
            echo $e->getMessage();
        }
    }

我正在运行的SQL是这样的:

And the SQL I'm running is this:

INSERT INTO `users` (`Username`, `Password`, `PasswordSalt`, `Email`, `IsAdmin`, `LoginAttempts`, `LastLogin`, `LastLoginAttempt`, `Created`) VALUES (:username, :password, :passwordsalt, :email, :isadmin, :loginattempts, :lastlogin, :lastloginattempt, :created); SELECT LAST_INSERT_ID() as 'id'

已创建用户，并且该用户位于users表中，但此后会出错.

The user is created and is there in the users table but it errors after that.

任何人都可以看到做错了什么吗? :)

Can anyone see what am doing wrong? :)

干杯！

推荐答案

我很确定PDO的mysql驱动程序(也许是mysql本身?)不支持多查询预处理语句.

I'm pretty sure the mysql driver for PDO (maybe mysql itself?) does not support multi-query prepared statements.

代替查询中的SELECT LAST_INSERT_ID()，在$query->execute()

这篇关于PDO多查询"SQLSTATE [HY000]:一般错误"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！