CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
Dyin
Dyin

Reputation: 5376

Changed PDO::ATTR_EMULATE_PREPARES to FALSE and getting "Invalid parameter number" error

I have the following code forexample:

$dbStatement=$this->dbObject->prepare("SELECT AVG(quality) as quality,
                                              AVG(adequacy) as adequacy,
                                              AVG(friendliness) as friendliness,
                                              SUM(overall) as overall,
                                              SUM(completed) as completed,
                                              type
                                       FROM   (SELECT AVG(quality) as quality,
                                                      AVG(adequacy) as adequacy,
                                                      AVG(friendliness) as friendliness,
                                                      COUNT(id) as overall,
                                                      SUM(is_completed) as completed,
                                                      category_id, type
                                               FROM valuation a
                                               WHERE status       =1
                                                 AND type         =:01
                                                 AND ((type='employer' AND owner_id=:02)
                                                      OR (type='employee' AND winner_id=:02))
                                               GROUP BY category_id
                                               HAVING COUNT(id)<=:03) b
                                       GROUP BY type");
$dbStatement->bindParam(':01',$Type);
$dbStatement->bindParam(':02',$UserID);
$dbStatement->bindParam(':03',$Most);
$dbStatement->execute();

This code throws an exception from execute() when I set PDO::ATTR_EMULATE_PREPARES to FALSE. The following message is included in the exception object:

SQLSTATE[HY093]: Invalid parameter number

Couldn't realize the problem so far, though read the corresponding manuals.

Upvotes: 8

Views: 792

Answers (1)

Jonathan Spiller
Jonathan Spiller

Reputation: 1895

The error is due to repetition of a placeholder. Each placeholder must be unique, even if you are binding the same parameter to it.

AND ((type='employer' AND owner_id=:02)
OR (type='employee' AND winner_id=:02))

Should be:

AND ((type='employer' AND owner_id=:02)
OR (type='employee' AND winner_id=:another02))

And then bind to it:

$dbStatement->bindParam(':01',$Type);
$dbStatement->bindParam(':02',$UserID);
$dbStatement->bindParam(':another02',$UserID);
$dbStatement->bindParam(':03',$Most);

Upvotes: 10

Related Questions