Invalid parameter number: number of bound variable

Question

I have a table: 'objects' with few columns: object_id:int, object_type:int, object_status:int, object_lati:float, object_long:float My query is :

$stmt = $db->query('SELECT o.object_id, o.object_type, o.object_status, o.object_lati, o.object_long FROM objects o WHERE o.object_id = 1');
$res = $stmt->fetch();

PDO throws an error:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

When I remove column object_lati or object_long query is work fine.

Answer 1

Although this particular question is not a real one, as the code provided will never produce an error like this, it seems that Google is taking delight in sending visitors to this page. For whom is the answer that follows:

This problem can never be caused by query() method call, as it essentially belongs to prepared statement.

The error itself is pretty clear: "number of tokens" stands for the number of ? or :name tokens in the query (which also called "placeholders"), while "number of variables bound" stands for the variables that were ether bound via bindValue or `bindParam, or sent via execute (which is technically the same). So, when one tries to bind more variables than there were tokens defined in the query, PDO raises this error.

For example if there is a query prepared with no tokens defined, but we are trying to bind a variable to it:

$stmt = $db->prepare('SELECT * FROM objects o WHERE o.object_id = 1');
$stmt->execute(array($id));

then it will result in the very error message in question, as the number of bound variables (1) doesn't match the number of tokens (0).

To solve this problem one just have to carefully compare the number of tokens in the prepared query with the number of variables that were bound to the statement.

Answer 2

try this instead

$row = $db->fetchRow('SELECT o.object_id, o.object_type, o.object_status, o.object_lati, o.object_long FROM objects o WHERE o.object_id = 1');
// return false if no result found

or to get record set

$rs = $db->fetchAll('SELECT o.object_id, o.object_type, o.object_status, o.object_lati, o.object_long FROM objects o WHERE o.object_id = 1');
// returns array with results

Answer 3

Try the statement like:

$stmt = $db->query('SELECT object_id, object_type, object_status, object_lati, object_long FROM objects o WHERE object_id = ? ', 1);