PHP PDO: SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

Question

I get the error:

SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

when I try to run the following code:

     public function getScore($matchID,$setone,$settwo,$getChallengerScore,$getOpponentScore,$fileOpponentData,$fileChallengerData)
{
   try
   {
       $stmt = $this->db->prepare("UPDATE matches SET `winner` = $setone 
                                        AND `looser` = $settwo 
                                        AND `winner_score` = $getChallengerScore 
                                        AND `looser_score` = $getOpponentScore 
                                        AND `opponent_blob` = '".$fileOpponentData."' 
                                        AND `challenger_blob` = '".$fileChallengerData."' 
                                   WHERE `id` = $matchID");
       #var_dump($stmt);
       $stmt->execute(); 

       return $stmt; 
   }
   catch(PDOException $e)
   {
       echo $e->getMessage();
   }    
}

I'm not great with PDO, haven't had many issues but this I cannot solve on my own. Any help would be much appreciated.

I also wonder why you sometimes use variable interpolation, and other times use concatenation, in the same string. — Barmar, Jan 12 '17 at 23:16
___SO is not here to replace a simple look at the manual___ Look at the syntax for an UPDATE. Its in the manual http://dev.mysql.com/doc/refman/5.7/en/update.html _HINT:_ `AND` ???? — RiggsFolly, Jan 12 '17 at 23:16
All the different assignments should be separated by `,`, not `AND`. — Barmar, Jan 12 '17 at 23:17
@RiggsFolly I wonder why this mistake is causing that error message. It should just be treating all the `AND` as part of the expression being assigned. to `winner`. — Barmar, Jan 12 '17 at 23:19
Indeed, I suspect the variables contain something that looks like a PDO parameter, e.g. `?` or `:xxx` — Barmar, Jan 12 '17 at 23:23
Please RTM on UPDATE http://dev.mysql.com/doc/refman/5.7/en/update.html — Funk Forty Niner, Jan 12 '17 at 23:31
Possible duplicate of [PDO: Invalid parameter number: mixed named and positional parameters](http://stackoverflow.com/questions/15888198/pdo-invalid-parameter-number-mixed-named-and-positional-parameters) — Sᴀᴍ Onᴇᴌᴀ, Jan 13 '17 at 03:47

score 0 · Accepted Answer · answered Jan 12 '17 at 23:26

Use a properly parametrized query. And the assignments in an UPDATE statement must be separated by ,, not AND.

$stmt = $this->db->prepare("UPDATE matches SET `winner` = :setone 
                            , `looser` = :settwo 
                            , `winner_score` = :getChallengerScore 
                            , `looser_score` = :getOpponentScore 
                            , `opponent_blob` = :fileOpponentData
                            , `challenger_blob` = :fileChallengerData
                            WHERE `id` = :matchID");
$stmt->execute(array(
    ':setone' => $setone,
    ':settwo' => $settwo,
    ':getChallengerScore' => $getChallengerScore,
    ':getOpponentScore' => $getOpponentScore,
    ':fileOpponentData' => $fileOpponentData,
    ':fileChallengerData' => $fileChallengerData,
    ':matchID' => $matchID
));

Thank you, this was the exact solution. – Xamber Jan 13 '17 at 21:31 — Xamber, Jan 13 '17 at 21:31

PHP PDO: SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

1 Answers1