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

Answer 1

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.

Answer 2

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

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