SQLSTATE[HY093]: All parameters are filled properly and the syntax is right?

Question

I am getting the following error:

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

Here's the code:

$transactions_sql = "INSERT INTO transactions (usr, service, txn_id, orig_amount, currency, date, description, fee_amt, 
  fee_currency, fee_descr, fee_type, net_amt, status) VALUES ";
$transactions_sql_data = array_fill(0, count($transactions), "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$transactions_sql .= implode(",", $transactions_sql_data);
$stmt = $conn->prepare($transactions_sql);
$i = 1;
foreach ($transactions["data"] as $tr) {
  $stmt->bindValue($i++, $id, PDO::PARAM_INT);
  $stmt->bindValue($i++, $tr["service"], PDO::PARAM_STR);
  $stmt->bindValue($i++, $tr["id"], PDO::PARAM_INT);
  $stmt->bindValue($i++, $tr["amount"], PDO::PARAM_INT);
  $stmt->bindValue($i++, $tr["currency"], PDO::PARAM_STR);
  $stmt->bindValue($i++, $tr["created"], PDO::PARAM_STR);
  $stmt->bindValue($i++, $tr["description"], PDO::PARAM_STR);
  $stmt->bindValue($i++, $tr["fee_details"][0]["amount"], PDO::PARAM_INT);
  $stmt->bindValue($i++, $tr["fee_details"][0]["currency"], PDO::PARAM_STR);
  $stmt->bindValue($i++, $tr["fee_details"][0]["description"], PDO::PARAM_STR);
  $stmt->bindValue($i++, $tr["fee_details"][0]["type"], PDO::PARAM_STR);
  $stmt->bindValue($i++, $tr["net"], PDO::PARAM_INT);
  $stmt->bindValue($i++, $tr["status"], PDO::PARAM_STR);
}
$stmt->execute();

Doing var_dump($transactions_sql) prints "INSERT INTO balance_transactions (usr, service, txn_id, orig_amount, currency, date, description, fee_amt, fee_currency, fee_descr, fee_type, net_amt, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", which is correct (in terms of number of question marks - there will only be three arrays inside the $tr array).

The first argument to `bindValue` is constant across loop iterations, so it looks like the next set of values replaces the previous one instead of being added as new parameters — mzulch, Feb 16 '16 at 03:04
That would be weird, because I used [this answer](http://stackoverflow.com/a/10060755/1079641) for reference. — DemCodeLines, Feb 16 '16 at 03:14
When he uses constant parameter names, he executes the statement once for each loop iteration, so the parameters don't collide. In the single query example, he uses `$i++` instead of constant integers so that the same index doesn't get reused — mzulch, Feb 16 '16 at 03:17
Can you check this ? `$stmt->bindValue(2, tr["service"], PDO::PARAM_STR);` . isn't it a `$tr["service"]` ? — Ralph John Galindo, Feb 16 '16 at 03:22
@RalphJohnGalindo that was a type when copying over. Still the same result. — DemCodeLines, Feb 16 '16 at 03:27
@mzulch I updated my question with that code and it still throws the same warning/error. — DemCodeLines, Feb 16 '16 at 03:28
@bwoebi Looks like I needed to add that `-1` when counting the `$transactions` array. Otherwise, it was adding 1 too many set of parameters. — DemCodeLines, Feb 16 '16 at 03:39

score 0 · Answer 1 · answered Feb 16 '16 at 03:37

Turns out I had to add a -1 to count($transactions) for it to work. Looks like it was adding 1 too many set of parameters.

Here's what worked: $transactions_sql_data = array_fill(0, count($transactions), "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

Thanks to everyone else for helping out!

SQLSTATE[HY093]: All parameters are filled properly and the syntax is right?

1 Answers1