PHP PDO准备的语句和值绑定给出无效的参数编号错误 [英] PHP PDO Prepared Statements and Value Binding Gives Invalid Parameter Number Error
问题描述
我在使用PHP PDO库和准备好的语句时遇到了一个小问题.据我所知,下面的准备好的语句应该可以工作,但是不能,相反,我得到:"PDOStatement :: execute():SQLSTATE [HY093]:无效的参数编号:绑定变量的数量与令牌的数量不匹配"
I'm having a slight problem with the PHP PDO library and prepared statements. As far as I can see the prepared statement below should work but it doesn't, instead I get: "PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens".
此部分的PHP代码如下:
My PHP code for this section looks like:
$sql = 'INSERT INTO '.POLYGON_TABLE.' (user_id, polygon, polygon_type) VALUES (:userId, PolygonFromText(\'POLYGON((:polygonArea))\'), :polygonType)';
$sth = $this->pdo->prepare($sql);
$sth->bindValue(':userId', $polygon->getUserId(), \PDO::PARAM_INT);
$sth->bindValue(':polygonArea', $polygon->getPolygonAsText(), \PDO::PARAM_STR);
$sth->bindValue(':polygonType', $polygon->getPolygonType(), \PDO::PARAM_STR);
if($sth->execute()) {
return true;
} else {
return false;
}
我完成了$ polygon-> getUserId(),$ polygon-> getPolygonAsText()和$ polygon-> getPolygonType()的var_dump并获得以下内容:
I have done a var_dump of $polygon->getUserId(), $polygon->getPolygonAsText() and $polygon->getPolygonType() and get the following:
string(1) "1"
string(226) "53.897910476098765 -1.739655277929728, 53.865530797116 -2.080231449804728, 53.67235280490181 -2.006073734960978, 53.68862047002787 -1.621552250585978, 53.89305512284903 -1.539154789648478, 53.897910476098765 -1.739655277929728"
string(7) "commute"
问题在于$ polygon-> getPolygonAsText()注释了此特定的bindValue调用,并且SQL语句中的PolygonFromText(\'POLYGON((:polygonArea))\')导致查询正常工作.
The issue is with $polygon->getPolygonAsText() as commenting out this particular bindValue call and the PolygonFromText(\'POLYGON((:polygonArea))\') from the SQL statement causes the query to work.
我现在完全不知所措.有人知道这是怎么回事吗?我看不到$ polygon-> getPolygonAsText()中包含的文本有任何问题.我一直在寻找解决方案,今天晚上花了几个小时修改代码,但无济于事.
I'm now completely at a loss. Anyone know what's wrong here? I can't see anything wrong with the text contained within $polygon->getPolygonAsText(). I have searched high and low for a solution to this and spent several hours this evening tinkering with the code but to no avail.
我什至尝试了这两个堆栈溢出主题中的建议,但它们也不起作用:
I have even tried the suggestions in these 2 stack overflow topics but they didn't work either:
- Invalid parameter number on PDO Prepared Statement
- PHP PDO prepared statements
任何帮助将不胜感激...
Any help would be much appreciated...
推荐答案
您是否尝试将整个表达式作为绑定值传递?
Did you try passing in the entire expression as the bind value?
$sql = 'INSERT INTO '.POLYGON_TABLE.' (user_id, polygon, polygon_type) VALUES (:userId, PolygonFromText(:polygonArea), :polygonType)';
$sth = $this->pdo->prepare($sql);
$area = sprintf("POLYGON((%s))", $polygon->getPolygonAsText());
$sth->bindValue(':userId', $polygon->getUserId(), \PDO::PARAM_INT);
$sth->bindValue(':polygonArea', $area, \PDO::PARAM_STR);
$sth->bindValue(':polygonType', $polygon->getPolygonType(), \PDO::PARAM_STR);
这篇关于PHP PDO准备的语句和值绑定给出无效的参数编号错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!