Search the Community

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: 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)? Is a prepared statement useful here, or would a regular statement do just as well?

What is the fastest and easiest way to move the fetched data into an array using prepared statements? Everything I do seems ad-hoc. ... we bind results in two variable $col1 and $col2 ... $x = 0; $myarray = array(); while(mysqli_stmt_fetch($stmt)){ $myarray[$x]['col1'] = $col1; $myarray[$x]['col2'] = $col2; $x++; } ... Now we have a multidimensional array $myarray (what I want). However, if I have more variables seems a little inconvenient to repeat over and over again... I wonder if there is any way faster or better.

"The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible)." If this is true, why would anybody ever use something else than prepared statements to handle the SQL queries? 1. Can I have an example when prepared statements are used and SQL injection will occur? 2. Can I have a reasonable technical reason why NOT to use prepared statements every time? When and why not to use prepared statements?

Can regular queries can be mixed with prepared statements? If we decide to use prepared statements should we stick with them for every task, even if can be done faster or easier with regular queries? For example in your book when you check if a user is registred or not or if we populate a drop-down menu from database... The first example assume interaction with the user, the second example does not. I would be interested not only if it is possible (I guess it is possible) but what constitues the best practice? Start with prepared statements stick with them?

On the presentation of prepared statements I've noticed an issue (at least on my machine): I prepare the statement, I bind the parameters, I execute the query, than I check for a match: $stmt = mysqli_prepare($dbc, $q); mysqli_stmt_bind_param($stmt, 's', $name); mysqli_stmt_execute($stmt); if (mysqli_stmt_num_rows($stmt) == 1) { echo "we have a match"; } This does not produce the expected result. However, it will work if I add after I execute the query: mysqli_stmt_store_result($stmt); Is this an issue with my machine or something overlooked at the time of writting the book? Thank you, Greg