PDO Multi-query "SQLSTATE[HY000]: General error"

Question

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.

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

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'

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

Can anyone see what am doing wrong? :)

Cheers!

score 1 · Accepted Answer · answered Dec 15 '11 at 23:20

1

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

Instead of SELECT LAST_INSERT_ID() in your query, use Conexion::$cn->lastInsertId() after your $query->execute()

answered Dec 15 '11 at 23:20

Francis Avila

31,233
6
58
96

I do think it was because the pdo driver was using mysql_query underneath which doesnt support multiple queries, i've since gone back to using mysqli's multi_query and its working fine :) – Richard Adnams Dec 16 '11 at 13:53
with pdo_mysql, to use multiple queries you have to setAttribute (PDO::ATTR_EMULATE_PREPARES, true) on you PDO or PDOStatement. also pdo does not use mysql_query underneath... it's a way of replacing mysql_* extension – Félix Adriyel Gagnon-Grenier Jan 19 '14 at 01:24

score 0 · Answer 2 · edited Nov 04 '12 at 08:09

I think this is correct:

function ExecuteQuery($sql, $params = array())
{
    if(Conexion::$cn== null)
        Conexion::Connect();
    $paramString="";

    foreach($params as $k=>$v)
    {
        $param = " :".$k." ,";
        $paramString .= $param;
    }
    $sql.=substr($paramString,0,-2);
    $query = Conexion::$cn->prepare($sql);
    foreach($params as $key => $value)
    {
        echo "entro";
        $query->bindParam(":".$key, $value);
    }

    $query->execute();
    $x = $query->fetchAll(\PDO::FETCH_ASSOC);
    var_dump($x);
    return $x;
}

public function Connect()
{
    try {
        $dns='dblib:host='.Conexion::$server.";dbname=".Conexion::$db.";";
        Conexion::$cn = new \PDO($dns, Conexion::$user, Conexion::$passw);
        Conexion::$cn->setAttribute( \PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION );
    }
    catch(PDOException $e) 
    {
        echo $e->getMessage();
    }
}

PDO Multi-query "SQLSTATE[HY000]: General error"

2 Answers2

Linked