Search code examples
phppdoprepared-statementbindparam

How to debug SQLSTATE[HY000]: General error: 2031 in prepared statements

I have this prepared statement query 

$stmt = $conn->prepare("
        UPDATE language 
        SET lang_alias=:lang_alias , lang_name=:lang_name  
        WHERE lang_id=:lang_id"
);

If I set an array to bind the values 

$query_array = array ( ":lang_alias" => "en", ":lang_name" => "English (UK)", ":lang_id" => 1 ) ;

and then execute it

$stmt->execute(array($query_array));

it wont work, I get

Notice: Array to string conversionreferring to$stmt->execute(array($query_array));

and Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number' referring to the same line.

Then I tried to use bindParam to bind the values

$stmt->bindParam(':lang_alias', $a);
$stmt->bindParam(':lang_name', $c);
$stmt->bindParam(':lang_id', $d3, PDO::PARAM_INT);

and works fine

If I try an alternative bindParam syntax

$stmt->bindParam("sssiii", $a, $b, $c, $d1, $d2, $d3);

it wont work. Gives

Warning: PDOStatement::bindParam() expects at most 5 parameters, 7 givenreferring to$stmt->bindParam("sssiii", $a, $b, $c, $d1, $d2, $d3);

and

Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2031 'referring to$stmt->execute();

1. Is the error caused by the fact that by default prepared statements convert to string all the values, so I have to define the int by hand? Thats why the bindParam works fine? Or is it something else that I am missing?

2. Why the bindParam("sssiii"... sytntax wont work?

3. I want this query to get its values dynamically, so using a bindParam by hand is not an option. I would like to use an associative array or a bindParam("sssiii"... syntax.

How can I do this? Thanks

Solution

  • The $query_array is already an array so when you run $stmt->execute(array($query_array)) you are making into a multidimensional array. This:

    $stmt->execute($query_array);

    should be all you need.

    As to the bind issue you aren't using the PDO syntax

    You are using the mysqli syntax with sssiii.