Prepared Statements For Multiple Updates/Inserts

[margaux]

I have a form with 30+ checkboxes which are named sf1, sf2 etc. and they are to be inserted into a table which has a column for each checkbox to store whether it was checked or not (type enum yes or no). I'll be doing various processing with this list of checkboxes and obviously want to use loops.

 

As part of the validation process, I've created an array $sf, which holds the checked (or not) value of each checkbox and looks similar to

$sf = array(1=>'yes','no','yes','no','yes','no','yes','no','no','yes','no','yes','no','yes','no','yes','no','yes','no','no','yes','no','yes','no','yes','no','yes','no','yes','no','no','yes','no');

What is the best way to insert this data into the table? I've been playing around with prepared statements in a for loop.

$numFields=count($sf);
$q="INSERT INTO survey_fields field1 VALUES '$sf[1]'";
$sf_id = mysqli_insert_id($dbc);
//for each sf item except the 1st one
for (i=2; i <=  $numFields; i++) {
$q="UPDATE survey_fields SET field$i VALUES ? WHERE id=$sf_id";
$stmt = mysqli_prepare($dbc,$q);
mysqli_stmt_bind_param($stmt, 's', $sf[$i]);

I"m getting a 1064 error , check the syntax to use near 'field1 VALUES 'yes'' - on the first insert. Is this being rejected because only 1 field is being inserted when there are 33? All the fields except id are set as type enum('yes','no'), NOT NULL - I have also tried setting all the fields (except id) to NULL but that didn't change the result.

 

A couple of other questions:

1. This method whilst it looks neater involves 33 inserts - I could do just one insert, would that be more efficient (though the query looks horrible)?
   
2. Is a prepared statement useful here, or would a regular statement do just as well?
   

[Larry]

If you're inserting or updating one row in one table (i.e., if each item is a column in the same table), then you should only use one query and a prepared statement offers little benefit. If you're inserting or updating 33 rows in a table, then you would need 33 queries and prepared statements would be logical.