PHP PDO Prepared Statements and Value Binding Gives Invalid Parameter Number Error

Question

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".

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

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"

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.

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:

Any help would be much appreciated...

`PolygonFromText(\'POLYGON(:polygonArea))')` Try removing the extra brackets. — Starx, Apr 20 '12 at 23:13
The extra brackets are needed as it's a signifies a LineString. You can have multiple LineStrings within a polygon and they would be defined line POLYGON((ls1),(ls2)). — Garry Welding, Apr 20 '12 at 23:14

score 4 · Accepted Answer · answered Apr 20 '12 at 23:04

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

Holy crap. Thank you very much. Worked! – Garry Welding Apr 20 '12 at 23:10 — Garry Welding, Apr 20 '12 at 23:10

score 3 · Answer 2 · answered Apr 20 '12 at 23:12

It appears that you're trying to use a named parameter inside a string:

PolygonFromText(\'POLYGON((:polygonArea))\')

This would be akin to doing something like this:

UPDATE foo SET bar = 'blah blah :wontwork blah blah'

What you should try instead is binding the whole string in the query:

PolygonFromText(:polygonArea)

And then including the rest of the string in the bound value:

$sth->bindValue(':polygonArea', 'POLYGON((' . $polygon->getPolygonAsText() . '))', \PDO::PARAM_STR);

score 1 · Answer 3 · answered Apr 20 '12 at 23:07

1

Last resort you could do this:

$sql = "INSERT INTO ".POLYGON_TABLE." (user_id, polygon, polygon_type) "
     ."VALUES (:userId, PolygonFromText('POLYGON(". $polygon->$getPolygonAsText
     .")'),:polygonType)";

But I think you should try the ? params first and see how that goes.

$sql = "INSERT INTO ".POLYGON_TABLE." (user_id, polygon, polygon_type) "
     ."VALUES (?, PolygonFromText('POLYGON(?)'), ?);";
$data = array($polygon->getUserId(), $polygon->getPolygonAsText(), $polygon->getPolygonType());
$query->execute($data);

Btw, I also think those single quotes around the POLYGON(?) function are dodgy... usually you don't quote a method call do you?

answered Apr 20 '12 at 23:07

Ozzy

8,244
7
55
95

Sorry, forgot to say I've also tried the ? params and got the same issue. – Garry Welding Apr 20 '12 at 23:08
@GarryWelding Oh ok... did you try to remove the single quotes around `POLYGON(:polygonArea)` ? – Ozzy Apr 20 '12 at 23:09
Single quotes are needed as it's a "FromText" function meaning the POLYGON() bit is actually just a text string. – Garry Welding Apr 20 '12 at 23:16
@GarryWelding Have you tried it? Its from text, but when you have text in a variable, you don't usually put variables in quotes? or do you? :S (I'm just wondering here) – Ozzy Apr 20 '12 at 23:18
an answer has been accepted above, take a look, have also upvoted an answer than came in later that has more details about why... – Garry Welding Apr 20 '12 at 23:21

PHP PDO Prepared Statements and Value Binding Gives Invalid Parameter Number Error

3 Answers3

Linked