PHP-PDO_PGSQL - SQLSTATE[HY093]: Invalid parameter number

Question

It's all the day that I'm stuck with this simple prepared statement:

// $conn it's my PDO Object
// and $intervention my params'array

$s = $conn->prepare("INSERT INTO intervention(firm_id,category,subject,amount,start_date,end_date) VALUES(:firm_id,':category',':subject',:amount,':start_date',':end_date')");
$result = $s->execute(array(
    'firm_id' => $firm_id ,
    'category' => $intervention["category"] ,
    'subject' => $intervention["subject"] ,
    'amount'=> $intervention["amount"] ,
    'start_date'=> $intervention["start_date"],
    'end_date'=>$intervention["end_date"] 
));

The execute will give me:

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: :category

Can someone help me understand what is wrong with this simple code?

Answer 1

In this part of the query: VALUES(:firm_id,':category',

:category is taken as a literal string and not as a parameter name, because of the quotes enclosing it.

There should be no quotes around parameter names, as in:

...VALUES(:firm_id, :category,...

There is the same mistake for the other non-numeric parameters of the rest of the query.

Answer 2

Parameters name should not have a quotes. The prepared statement will do the replacement properly. Pay attention too at the number of parameters you write in the query and what will you bind on execute method.