如何调试SQLSTATE [HY000]:常规错误:2031在准备好的语句中 [英] 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"
);

如果我设置一个数组来绑定值

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

然后执行

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

它不会工作，我得到

注意:数组到字符串的转换referring to $ stmt-> execute(array($ query_array));

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

和Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number'引用同一行.

然后我尝试使用 bindParam绑定值

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

工作正常

如果我尝试使用替代bindParam语法

If I try an alternative bindParam syntax

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

它不会工作.给予

警告:PDOStatement :: bindParam()期望最多5个参数，给定7个referring to $ stmt-> bindParam("sssiii"，$ a，$ b，$ c，$ d1，$ d2，$ d3) ;

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

和

未捕获的异常'PDOException'，消息为'SQLSTATE [HY000]:一般错误:2031'referring to $ stmt-> execute();

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

1.是因为默认情况下已准备好的语句将所有值转换为字符串而导致的错误，所以我必须手动定义int吗?那就是为什么bindParam可以正常工作吗?还是我想念的其他东西?

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..为什么bindParam("sssiii"...语法不起作用?

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

3..我希望此查询动态获取其值，因此不能手动使用bindParam.我想使用关联数组或bindParam("sssiii"...语法.

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.

我该怎么做?谢谢

推荐答案

$query_array已经是一个数组，因此当您运行$stmt->execute(array($query_array))时，您正在制作一个多维数组.这个:

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

应该是您所需要的.

关于bind问题，您没有使用PDO语法

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

• http://php.net/manual/en/pdostatement.bindparam.php

您正在将mysqli语法与sssiii一起使用.

You are using the mysqli syntax with sssiii.

• http://php.net/manual/en/mysqli- stmt.bind-param.php .

这篇关于如何调试SQLSTATE [HY000]:常规错误:2031在准备好的语句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！