mysql_query in while loop

[perky416]

Hi everyone,

Im using code similar to that below to update or delete database entries based on checked checkboxes when a form is submitted.

Would I be right in saying that, say if the user ticks 10 check-boxes and then clicks submit, 10 queries are executed?

 

If so is there a way to make it so that only 1 query is executed but updates or deletes multiple entries?

 

$i = 0;
while ($row = mysql_fetch_assoc($query)) {

// if checkbox is checked, validate data			
if ($_POST['check'][$i]){			

// validation code here

}

// if no errors are present update or delete each checked entry
if (empty($error)){
if ($_POST['action'] == "Save Changes"){
mysql_query("UPDATE example SET example='$example' WHERE value='$checked'");
}

if ($_POST['action'] == "Delete"){
mysql_query("DELETE FROM example WHERE value='$checked'");
}
}			
$i++;

 

Thanks

[gristoi]

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in

use the IN function in mysql. example:

 

<?php
$exampleValues = array(1,2,3,4,5,6,7,;
$sql ="update table1 set field1 = 1 where field2 IN($exampleValues)";
?>

[perky416]

Hi gristoi,

 

Iv had a read of the IN function and used the example you have given me. I have modified it a bit to try and integrate it with my code however I cant get it to work. My latest code is:

 

$i = 0;
while($row = mysql_fetch_assoc($query)){
	$value1 = $_POST['value1'][$i];
	$exampleValues = $_POST['checkbox_value'][$i];
	$i++; 
}

if (empty($error)){
	mysql_query("UPDATE table1 SET field1='$value1' WHERE field2 IN ($exampleValues)");
}

 

Can you see where im going wrong?

 

Thanks mate.

[premiso]

Remove the while loop.

 

Then replace this:

 

        if (empty($error)){
	mysql_query("UPDATE table1 SET field1='$value1' WHERE field2 IN ('" . implode("','", $_POST['checkbox_value']) . "')");
}

 

For security reasons, you may want to run mysql_real_escape_string on the $_POST['checkbox_value'] through an array_map to help prevent SQL injection, but that should get you to where you want to be.

 

IE:

 

       $_POST['checkbox_values'] = array_map('mysql_real_escape_string', $_POST['checkbox_values']);
        if (empty($error)){
	mysql_query("UPDATE table1 SET field1='$value1' WHERE field2 IN ('" . implode("','", $_POST['checkbox_value']) . "')");
}

 

Pending any errors I may have.

[perky416]

Hi mate,

 

Thanks for the sql injection tip, iv been meaning to do a bit of research in how to make my site more secure . Iv took your example and now I have the code below, however it is writing a blank value to the database.

 

$value1 = $_POST['value1'][$i];

$_POST['checkbox_values'] = array_map('mysql_real_escape_string', $_POST['checkbox_values']);
if (empty($error)){
	mysql_query("UPDATE table1 SET field1='$value1' WHERE field2 IN ('" . implode("','", $_POST['checkbox_value']) . "')");
}

 

Do you know why this could be?

 

Also, wouldnt I still have to have the while loop around the "$value1 = $_POST['value1'][$i];"? I tried this also and this was writing a 0 to the database.

 

Thanks mate I appreciate your help

[perky416]

Iv just read my initial post, I think it may be a little confusing. I wrote the initial example code out quickly and bodged it up. Im trying to achieve the following:

 

if ($_POST['action'] == "Save Changes"){
mysql_query("UPDATE table1 SET field1='$value1' WHERE field2='$checkbox_value'");
}