notaverygoodprogrammer notaverygoodprogrammer -4 years ago 150
MySQL Question

Solved : Can't find cause of HY093 Invalid parameter number: number of bound variables does not match num ber of tokens

I have a page with 8 checkboxes on it. Depending on how many checkboxes are checked, an sql statement is created. E.g.: if two checkboxes are checked, an array of 24 names (out of a maximum of 32) has to update a table. When 3 are checked, 20 rows need to be updated.

In my PHP logic I check how many checkboxes are checked, let's say 3 are checked. I then create the PDO update statement using placeholders, like so :

.
.
case 20:
$sstring = "left1 = :n1, left2 = :n2, left3 = :n3, left4 = :n4,
left5 = :n5, left6 = :n6, left7 = :n7, left8 = :n8,
left9 = :n9, left10 = :n10, left11 = :n11, left12 = :n12,
left13 = :n13, left14 = :n14, left15 = :n15, left16 = :n16,
left17 = :n17, left18 = :n18, left19 = :n19, left20 = :n20";
break;
.
.


Then, I create the sql statement, like this (remember: PHP):

$sql = "UPDATE drawleft SET " . $sstring . " WHERE year = :year";


Next, I do the prepare :

$stmt1=$db->prepare($sql);


And finally, I complete the update sending the data :

.
.
case 20:
$qstring = "':year' => $year, ':n1' => $nm_array[0], ':n2' => $nm_array[1],
':n3' => $nm_array[2], ':n4' => $nm_array[3], ':n5' => $nm_array[4],
':n6' => $nm_array[5], ':n7' => $nm_array[6], ':n8' => $nm_array[7],
':n9' => $nm_array[8], ':n10' => $nm_array[9], ':n11' => $nm_array[10],
':n12' => $nm_array[11], ':n13' => $nm_array[12], ':n14' => $nm_array[13],
':n15' => $nm_array[14], ':n16' => $nm_array[15], ':n17' => $nm_array[16],
':n18' => $nm_array[17], ':n19' => $nm_array[18], ':n20' => $nm_array[19]";
break;
.
.


Now when I do
$result = $stmt1->execute(array($qstring));
, I get an error 500 back, and I see in the apache error_log the HY093 error:
Invalid parameter number: number of bound variables does not match number of tokens


When I do the following (without
array
) :

$result = $stmt1->execute($qstring);


I get a PDO warning :
PDOStatement::execute() expects parameter 1 to be array, string given


Comparing the number of placeholders against the number of values submitted during the execute(), I can only conclude that they are the same.

So, please, can anyone tell me what I'm doing wrong here ? (apart from not coding too well...)

Thanks.

Answer Source
$qstring = "':year' => $year, ':n1' => $nm_array[0], ':n2' => $nm_array[1],
              ':n3' => $nm_array[2], ':n4' => $nm_array[3], ':n5' => $nm_array[4],
              ':n6' => $nm_array[5], ':n7' => $nm_array[6], ':n8' => $nm_array[7],
              ':n9' => $nm_array[8], ':n10' => $nm_array[9], ':n11' => $nm_array[10],
              ':n12' => $nm_array[11], ':n13' => $nm_array[12], ':n14' => $nm_array[13],
              ':n15' => $nm_array[14], ':n16' => $nm_array[15], ':n17' => $nm_array[16],
              ':n18' => $nm_array[17], ':n19' => $nm_array[18], ':n20' => $nm_array[19]";

needs to be

$qstring = array(':year' => $year, ':n1' => $nm_array[0], ':n2' => $nm_array[1],
              ':n3' => $nm_array[2], ':n4' => $nm_array[3], ':n5' => $nm_array[4],
              ':n6' => $nm_array[5], ':n7' => $nm_array[6], ':n8' => $nm_array[7],
              ':n9' => $nm_array[8], ':n10' => $nm_array[9], ':n11' => $nm_array[10],
              ':n12' => $nm_array[11], ':n13' => $nm_array[12], ':n14' => $nm_array[13],
              ':n15' => $nm_array[14], ':n16' => $nm_array[15], ':n17' => $nm_array[16],
              ':n18' => $nm_array[17], ':n19' => $nm_array[18], ':n20' => $nm_array[19]);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download