Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
margaux

Prepared Statements For Multiple Updates/Inserts

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...