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:

• PDO准备语句上的无效参数号
• PHP PDO准备的语句

• 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屋！