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

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. — deceze, Jun 24 '13 at 11:45
@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. — silkfire, Jun 24 '13 at 11:47
Error: 2031 (CR_PARAMS_NOT_BOUND)Message: No data supplied for parameters in prepared statement FROM Documentation http://dev.mysql.com/doc/refman/5.0/en/error-messages-client.html — david strachan, Jun 24 '13 at 11:50
@davidstrachan Do you know why my PHP version does not return that "human readable error message"? — silkfire, Jun 24 '13 at 11:52
Sure you're binding `:strength` as well? Sure `$limit` is truthy? — deceze, Jun 24 '13 at 11:54
@deceze Yeah, `$criteria` only contains one value: `'strength' => string '1g' (length=2)` — silkfire, Jun 24 '13 at 11:56

score 30 · Answer 1 · edited Mar 01 '16 at 19:07

30

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.

edited Mar 01 '16 at 19:07

Mi-Creativity

9,554
10
38
47

answered Mar 01 '16 at 18:42

Nowdeen

1,401
14
20

1

This answer saved my day! – CN1002 Feb 28 '17 at 13:07
You saved my day. I changed the parameter name as.. ':colour0', 'colour1', 'colour2' ... . and the issue was resolved. – mazend Mar 11 '20 at 23:37

deceze · Accepted Answer · 2016-08-30T17:39:27.417

29

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

edited Aug 30 '16 at 17:39

answered Jun 24 '13 at 11:58

deceze

510,633
85
743
889

AbcAeffchen · Answer 3 · 2019-10-11T07:30:08.567

20

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

edited Oct 11 '19 at 07:30

answered Oct 10 '16 at 20:03

AbcAeffchen

14,400
15
47
66

score 3 · Answer 4 · answered Jun 24 '13 at 11:59

3

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.

answered Jun 24 '13 at 11:59

Álvaro González

142,137
41
261
360

I can't use `bindParam`/`bindValue` for the other values...sigh. – silkfire Jun 24 '13 at 12:00
@silkfire Please see http://stackoverflow.com/questions/10014147/limit-keyword-on-mysql-with-prepared-statement-maybe-still-a-bug/10014200#10014200 for other ideas. – Álvaro González Jun 24 '13 at 12:05
`PDO::ATTR_EMULATE_PREPARES, true)` - shouldn't that **enable** emulated prepares? I think it's the other way around though, very misleading. – silkfire Jun 24 '13 at 12:14
@silkfire - That's for spotting the typo, I've just fixed it. – Álvaro González Jun 24 '13 at 12:23

score 2 · Answer 5 · answered Jan 03 '17 at 18:03

2

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

answered Jan 03 '17 at 18:03

Martin Schilliger

829
9
17

score 1 · Answer 6 · answered Jan 02 '17 at 02:09

1

The exception also happens (at least in MySQL/PDO) when your SQL tries to UPDATE an AUTO_INCREMENT field.

answered Jan 02 '17 at 02:09

1

I don't know why someone downvoted Roadowl's answer. The reality for the error is that it's very generic. – Altimus Prime Apr 08 '20 at 21:25
Not only is the error message very generic, I just caused that message with exactly the circumstance described. – Bob Brown Jun 20 '23 at 16:46

score 1 · Answer 7 · answered Jan 24 '18 at 11:41

1

This happens if you have mismatching parameters. For example:

$q = $db->prepare("select :a, :b");
$q->execute([":a"=>"a"]);

answered Jan 24 '18 at 11:41

Charlie

8,530
2
55
53

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

7 Answers7

Linked

Related