CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
silkfire
silkfire

Reputation: 25935

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

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

Upvotes: 24

Views: 55265

Answers (7)

AbcAeffchen
AbcAeffchen

Reputation: 14977

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

Upvotes: 20

Charlie
Charlie

Reputation: 9108

This happens if you have mismatching parameters. For example:

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

Upvotes: 1

Martin Schilliger
Martin Schilliger

Reputation: 838

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

Upvotes: 2

user2849202
user2849202

Reputation:

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

Upvotes: 1

deceze
deceze

Reputation: 521995

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

Upvotes: 29

Nowdeen
Nowdeen

Reputation: 1440

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.

Upvotes: 30

Álvaro González
Álvaro González

Reputation: 146340

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.

Upvotes: 3

Related Questions