PDO错误:SQLSTATE [HY000]:常规错误:2031

时间:2022-10-14 13:16:23

I'm getting this annoying error and although I have an idea of why I'm getting it, I can't for the life of me find a solution to it.

我收到了这个恼人的错误,虽然我知道为什么我得到它,但我不能为我的生活找到解决方案。

if ($limit) {
   $sth->bindValue(':page', $page - 1, PDO::PARAM_INT);
   $sth->bindValue(':entries_per_page', $page * $entries_per_page, PDO::PARAM_INT);
}

$sth->execute($criteria);

Query contains placeholders (:placeholder). But to add those LIMIT placeholders, I need to use the manual method (bindValue) because otherwise the engine will turn them into strings.

查询包含占位符(:占位符)。但是要添加那些LIMIT占位符,我需要使用手动方法(bindValue),否则引擎会将它们转换为字符串。

I'm not getting the Invalid number of parameters error, so all placeholders have been bound correctly (I assume).

我没有得到无效的参数数量错误,因此所有占位符都已正确绑定(我假设)。

Query:

查询:

SELECT `articles`.*, `regional_municipalities`.`name` AS `regional_municipality_name`, 
       `_atc_codes`.`code` AS `atc_code`, `_atc_codes`.`name` AS `substance`
FROM `articles`
LEFT JOIN `_atc_codes`
ON (`_atc_codes`.`id` = `articles`.`atc_code`)
JOIN `regional_municipalities`
ON (`regional_municipalities`.`id` = `articles`.`regional_municipality`)
WHERE TRUE AND `articles`.`strength` = :strength
GROUP BY `articles`.`id`
ORDER BY `articles`.`id`
LIMIT :page, :entries_per_page

All placeholder values reside in $criteria, except for the last two LIMIT, which I manually bind with bindValue().

除最后两个LIMIT外,所有占位符值都位于$ criteria中,我使用bindValue()手动绑定。

7 个解决方案

#1


17  

You cannot use ->bind* and ->execute($params). Use either or; if you pass parameters to execute(), those will make PDO forget the parameters already bound via ->bind*.

你不能使用 - > bind *和 - > execute($ params)。使用或;如果你将参数传递给execute(),那些将使PDO忘记已经绑定的参数 - > bind *。

#2


18  

This same error 2031 can be issued when one bind two values with the same parameter name, like in:

当一个绑定具有相同参数名称的两个值时,可以发出同样的错误2031,如:

  • $sth->bindValue(':colour', 'blue');
  • $ sth-> bindValue(':color','blue');
  • $sth->bindValue(':colour', 'red');
  • $ sth-> bindValue(':color','red');

..so, beware.

..所以,要小心。

#3


4  

This exception also appears if you try to run a query with placeholders instead of preparing a statment such as

如果您尝试使用占位符运行查询而不是准备诸如的语句,则也会出现此异常

$stmt = $db->query('SELECT * FROM tbl WHERE ID > ?');

instead of

代替

$stmt = $db->prepare('SELECT * FROM tbl WHERE ID > ?');

#4


3  

From the manual:

从手册:

public bool PDOStatement::execute ([ array $input_parameters ] )

public bool PDOStatement :: execute([array $ input_parameters])

Execute the prepared statement. If the prepared statement included parameter markers, you must either:

执行准备好的声明。如果准备好的语句包含参数标记,则必须:

  • call PDOStatement::bindParam() to bind PHP variables to the parameter markers: bound variables pass their value as input and receive the output value, if any, of their associated parameter markers

    调用PDOStatement :: bindParam()将PHP变量绑定到参数标记:绑定变量将其值作为输入传递,并接收其相关参数标记的输出值(如果有)

  • or pass an array of input-only parameter values

    或传递一组仅输入参数值

You need to pick a method. You cannot mix both.

你需要选择一种方法。你不能混合两者。

#5


2  

It's not exactly an answer, but this error also happens if you try to use a word with a hyphen as placeholders, for example:

这不完全是一个答案,但如果您尝试使用带有连字符的单词作为占位符,也会发生此错误,例如:

$sth->bindValue(':page-1', $page1);

$ sth-> bindValue(':page-1',$ page1);

So better use

所以更好用

$sth->bindValue(':page_1', $page1);

$ sth-> bindValue(':page_1',$ page1);

#6


0  

This happens if you have mismatching parameters. For example:

如果您的参数不匹配,就会发生这种情况。例如:

$q = $db->prepare("select :a, :b");
$q->execute([":a"=>"a"]);

#7


-1  

The exception also happens (at least in MySQL/PDO) when your SQL tries to UPDATE an AUTO_INCREMENT field.

当您的SQL尝试更新AUTO_INCREMENT字段时,也会发生异常(至少在MySQL / PDO中)。

#1


17  

You cannot use ->bind* and ->execute($params). Use either or; if you pass parameters to execute(), those will make PDO forget the parameters already bound via ->bind*.

你不能使用 - > bind *和 - > execute($ params)。使用或;如果你将参数传递给execute(),那些将使PDO忘记已经绑定的参数 - > bind *。

#2


18  

This same error 2031 can be issued when one bind two values with the same parameter name, like in:

当一个绑定具有相同参数名称的两个值时,可以发出同样的错误2031,如:

  • $sth->bindValue(':colour', 'blue');
  • $ sth-> bindValue(':color','blue');
  • $sth->bindValue(':colour', 'red');
  • $ sth-> bindValue(':color','red');

..so, beware.

..所以,要小心。

#3


4  

This exception also appears if you try to run a query with placeholders instead of preparing a statment such as

如果您尝试使用占位符运行查询而不是准备诸如的语句,则也会出现此异常

$stmt = $db->query('SELECT * FROM tbl WHERE ID > ?');

instead of

代替

$stmt = $db->prepare('SELECT * FROM tbl WHERE ID > ?');

#4


3  

From the manual:

从手册:

public bool PDOStatement::execute ([ array $input_parameters ] )

public bool PDOStatement :: execute([array $ input_parameters])

Execute the prepared statement. If the prepared statement included parameter markers, you must either:

执行准备好的声明。如果准备好的语句包含参数标记,则必须:

  • call PDOStatement::bindParam() to bind PHP variables to the parameter markers: bound variables pass their value as input and receive the output value, if any, of their associated parameter markers

    调用PDOStatement :: bindParam()将PHP变量绑定到参数标记:绑定变量将其值作为输入传递,并接收其相关参数标记的输出值(如果有)

  • or pass an array of input-only parameter values

    或传递一组仅输入参数值

You need to pick a method. You cannot mix both.

你需要选择一种方法。你不能混合两者。

#5


2  

It's not exactly an answer, but this error also happens if you try to use a word with a hyphen as placeholders, for example:

这不完全是一个答案,但如果您尝试使用带有连字符的单词作为占位符,也会发生此错误,例如:

$sth->bindValue(':page-1', $page1);

$ sth-> bindValue(':page-1',$ page1);

So better use

所以更好用

$sth->bindValue(':page_1', $page1);

$ sth-> bindValue(':page_1',$ page1);

#6


0  

This happens if you have mismatching parameters. For example:

如果您的参数不匹配,就会发生这种情况。例如:

$q = $db->prepare("select :a, :b");
$q->execute([":a"=>"a"]);

#7


-1  

The exception also happens (at least in MySQL/PDO) when your SQL tries to UPDATE an AUTO_INCREMENT field.

当您的SQL尝试更新AUTO_INCREMENT字段时,也会发生异常(至少在MySQL / PDO中)。