Error when using placeholder in prepared statement

Question

I got this error when using the code below (everything is in a try catch block):

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

$item_q = 4;
$item_no = 12;

$update = $db->prepare("UPDATE stock 
  SET quantity = quantity - :item_q 
  WHERE item_number = :item_no");

$update->execute([':item_q' => $item_q]);
$update->execute([':item_no' => $item_no]);

It is something with the :item_q that is causing problem and i do not what it is. It works when I replace the :item_q in the sql query with a number. I am happy for some help! :)

Answer 1

Issue

Currently you're executing twice with one parameter bound each time.

Solution

You want to either bind the parameters first then execute, or execute with both parameters bound.

Executing with both bound values in PDOStatement::execute() function

$update->execute([":item_q" => $item_q, ":item_num" => $item_no]);

Executing after binding parameters with PDOStatement::bindParam() function

$update->bindParam(":item_q", $item_q, PDO::PARAM_INT);
$update->bindParam(":item_num", $item_no, PDO::PARAM_STR);
$update->execute();

Answer 2

Problem is with executing your statement two times.

$update->execute([':item_q' => $item_q]);
$update->execute([':item_no' => $item_no]);

Combine it into one as

$update->execute([':item_q' => $item_q,':item_no' => $item_no]);