PHP PDO Update: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Question

I know this problem came multiple times, but i cannot find the mistake in my code. In my update-branch every $stmt->bindValue(...) returns TRUE, but i catch the pdo exception

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Inserting a new entry works fine.

In my mysql-database the structure of table 'system' is:

id -> int(11), primary key
computer_name -> varchar(255)
cpu_speed -> int(11)
ram_size -> int(11)
mac_address -> varchar(255)
operating_system -> varchar(255)

My error-throwing code:

// Search for mac_address.
// If an entry with the same MAC exists update the entry.
// Else, create a new entry
$stmt = $pdo->prepare("SELECT * FROM system WHERE mac_address=:mac");
$stmt->bindValue(":mac", $mac_address, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// If no rows are returned, no entry exists => create a new one
if(empty($rows))
{  
  // Prepare statement
  $stmt = $pdo->prepare("INSERT INTO
       system(`computer_name`,`cpu_speed`,`ram_size`,`mac_address`, `operating_system`)
       VALUES(:computer_name, :cpu_speed, :ram_size, :mac_address, :operating_system)");
  $stmt->bindValue(":computer_name", $computer_name, PDO::PARAM_STR);
  $stmt->bindValue(":cpu_speed", $cpu_speed, PDO::PARAM_INT);
  $stmt->bindValue(":ram_size", $ram_size, PDO::PARAM_INT);
  $stmt->bindValue(":mac_address", $mac_address, PDO::PARAM_STR);
  $stmt->bindValue(":operating_system", $operating_system, PDO::PARAM_STR);
}
else  // Update existing entry
{
  //computer_name   cpu_speed   ram_size    mac_address     operating_system 
  $stmt = $pdo->prepare("UPDATE system
      SET computer_name=:computer_name,
          cpu_speed=:cpu_speed,
          ram_size=:ram_size,
          operating_system=:operating_sytem,
          mac_address=:mac_address_in
       WHERE mac_address=:mac_address_query");
  echo$stmt->bindValue(":computer_name", $computer_name, PDO::PARAM_STR);
  echo$stmt->bindValue(":cpu_speed", $cpu_speed, PDO::PARAM_INT);
  echo$stmt->bindValue(":ram_size", $ram_size, PDO::PARAM_INT);
  echo$stmt->bindValue(":mac_address_in", $mac_address, PDO::PARAM_STR);
  echo$stmt->bindValue(":operating_system", $operating_system, PDO::PARAM_STR);
  echo$stmt->bindValue(":mac_address_query", $mac_address, PDO::PARAM_STR);
}

// Execute the command
$stmt->execute();

Why don't you do it in a single query with `ON DUPLICATE KEY UPDATE`? — Barmar, Mar 30 '15 at 20:36
Why are you setting `mac_address` to the value it already has? — Barmar, Mar 30 '15 at 20:39
Comment out all queries but one to find out which one of the three throws the error. — Genti Saliu, Mar 30 '15 at 20:39
@GentiSaliu He says he knows which one is causing the error, it's the `UPDATE`. — Barmar, Mar 30 '15 at 20:40
Is this a verbatim copy of the code? There's probably a typo, and if you re-entered it here you may have corrected it in the process. — Barmar, Mar 30 '15 at 20:41
":mac_address_query" is missing ;) see SET ... _Invalid parameter number_ — s.d.a.p.e, Mar 30 '15 at 20:45
@Barmar: im quite new to mysql, so i did not check if mysql could do it for me. Also i did add the key (mac_address), because i wasnt sure if that maybe would have fixed the problem. But thank you for reminding me. — Juarrow, Mar 30 '15 at 21:42

score 6 · Accepted Answer · answered Mar 30 '15 at 20:52

6

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

This mean that you have a parameter that is not defined. It's seams that you have error here operating_system=:operating_sytem, may be it should be operating_system=:operating_system, . This make one missing parameter because it is not set here because you have misspelled a word. Please try to check every word in you prepare statement and the corresponding bindValue.

answered Mar 30 '15 at 20:52

Tchaps

865
7
21

1

Another one of those "sitting too long in front of the computer on your own code"-typo-thing. Thx :) – Juarrow Mar 30 '15 at 21:35
@Incubbus Not just your own code, I didn't see it, either, and I'm usually pretty good at noticing these. But I was right when I said it was probably a typo somewhere. – Barmar Mar 30 '15 at 21:45

PHP PDO Update: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

1 Answers1