PDO error: SQLSTATE[HY000]: General error: 2031

Question

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.

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().

Solution 1

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

Solution 2

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

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

..so, beware.

Solution 3

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

Solution 4

From the manual:

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

• or pass an array of input-only parameter values

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

Solution 5

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

So better use

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

View more solutions

Comments

[Royal Bg]

Try to search in google "PDO binding LIMIT parameters"

[Gromski]

1) It would've been nice to include the human readable error message instead of just the cryptic code, 2) Show your actual query so we can see where the error stems from.

[silkfire]

@deceze If there was any human readable message in there, I'd: a) probably solved it by now, b) if not, then included it here. This was the full error message, trust me.

[david strachan]

Error: 2031 (CR_PARAMS_NOT_BOUND)Message: No data supplied for parameters in prepared statement FROM Documentation dev.mysql.com/doc/refman/5.0/en/error-messages-client.html

[silkfire]

@davidstrachan Do you know why my PHP version does not return that "human readable error message"?

[Gromski]

Sure you're binding :strength as well? Sure $limit is truthy?

[silkfire]

@deceze Yeah, $criteria only contains one value: 'strength' => string '1g' (length=2)

[silkfire]

I can't use bindParam/bindValue for the other values...sigh.

[Álvaro González]

@silkfire Please see stackoverflow.com/questions/10014147/… for other ideas.

[silkfire]

PDO::ATTR_EMULATE_PREPARES, true) - shouldn't that enable emulated prepares? I think it's the other way around though, very misleading.

[Álvaro González]

@silkfire - That's for spotting the typo, I've just fixed it.

[CN1002]

This answer saved my day!

[TuralAsgar]

You saved my time!

[mazend]

You saved my day. I changed the parameter name as.. ':colour0', 'colour1', 'colour2' ... . and the issue was resolved.

[Altimus Prime]

I don't know why someone downvoted Roadowl's answer. The reality for the error is that it's very generic.