Updating Multiple Checkboxes [PHP/MySQL]

[imdead]

Hiya i'm working on a project, which involves a list of checkboxes which are automaticly populated by the database

 

which is currently working at

 

while ($row = mysql_fetch_array($sql)) {
$id = $row['id'];
$hotbox = $row['hot_job'];
?>
<form name='hotbox' action='hot_update.php' method='POST'>
<tr><td><strong><?php echo $id; ?></strong></td>
				    <td><input name="ONOFF<? echo $row['id']; ?>" type="checkbox" id="ONOFF" value="1"
					    <?php if ($row['hot_job'] == 'YES') { echo "checked";} else {} ?>
				    </td></tr>
<?php
}
?>

 

 

however when the user has unchecked/checked the correct boxes and clickes update, nothing changes.

 

Here is my current code

   foreach($_POST['id'] as $id) {
    $onoff = 0;
    if (isset($_POST["ONOFF".$id])) {
	    $onoff = 1;
    }
    if($onoff == 1) {
	    $sql1="UPDATE jobs SET hot_job='".$onoff."' WHERE id='".$id."'";
    } else {
	    $sql1="UPDATE jobs SET hot_job='".$onoff."' WHERE id='".$id."'";
    }
    echo $id;
    echo $onoff;
    $result1=mysql_query($sql1);
   }
?>

 

The mysql setup is

 

id hot_job

1 YES

2 YES

3 NO

4 YES

ect

 

Any help would be greatly appreciated

[Psycho]

I see lots of problems:

 

First of all you should change the values you are storing to 1 or 0 instead of "YES" or "NO". A 1 or 0 can be logically interpreted as Boolean TRUE/FALSE.

 

Second, you should NOT be running queries in loops. Create one query to update the values.

 

Third, there is no error handling on your query - so if there are errors you will never see them

 

Fourth, you appear to be opening a new form for each checkbox - but never closing the form. If you have a closing form tag later in your script you would have only one valid for that would include only the last checkbox.

 

Fifth, you are not creating the form fields as an array, but your processing code is trying to process them as an array

 

Sixth, the values in your DB are YES or NO, but you are trying to update the value to 0 or 1.

 

I'm sure there are other problems I do not see. There is a VERY easy way to do this - will respond momentarily with some code

[requinix]

1a. Fix your HTML. It outputs a

for every single row, and in a place where a

is not allowed. Move it to before you start the table and remember to close it after.

1b. Fix your HTML. The checkbox isn't closed.

2. You don't put any ID information in the form. Your code won't find it because it's not there.

3. Instead of adding it, rewrite the checkboxes to

<input name="ONOFF[]" type="checkbox" value="<?php echo $row['id']; ?>" <?php if($row['hot_job'] == 'YES') { echo "checked='checked' "; } ?>/>

Then

// checkboxes are only sent if checked
// disable everything then re-enable
mysql_query("UPDATE jobs SET hot_job = 'NO'");

if (!empty($_POST["ONOFF"]) && is_array($_POST["ONOFF"])) {
   // sanitize
   $ids = array_map("intval", array_filter($_POST["ONOFF"], "is_scalar"));
   // update all of them at once in batches of 1000
   foreach (array_chunk($ids, 1000) as $batch) {
       mysql_query("UPDATE jobs SET hot_job = 'YES' WHERE id IN (" . implode(", ", $batch) . ")");
   }
}

 

Since you mix-and-match YES/NO with 1/0 I assume the column is an ENUM?

Edited February 15, 2013 by requinix

[Psycho]

This query will update all the values for the column 'hot_job' to be a 1 or 0 based upon whether the current value is "YES" or "NO", respectively. After running that, you should change the field type to an INT.

UPDATE jobs
SET hot_job = (hot_job = 'YES')

 

 

When creating your form, you want to create the fields as an array (use [] at the end of the field name) using the ID of the record as the value.

<?php

$formFields = '';
while ($row = mysql_fetch_array($sql))
{
   $id = $row['id'];
   $checked = ($row['hot_job']) ? ' checked="checked"' : '';
   $formFields .= "<tr>\n";
   $formFields .= "    <td><strong>{$id}</strong></td>\n";
   $formFields .= "    <td><input  type=\"checkbox\" name=\"hot_job[]\" id=\"ONOFF{$id}\" value=\"{$id}\" {$checked} /></td>\n";
   $formFields .= "</tr>\n";
?>

<form name='hotbox' action='hot_update.php' method='POST'>
<table>
<?php echo $formField; ?>
<button type="select">Submit change</button>
</form>

 

To process you can take all the submitted values (the Record IDs) and create a single query to update the entire table

if(isset($_POST['hot_job']))
{
   //Convert all values to integers and remove invalid values
   $hotJobIDs = array_filter(array_map('intval', $_POST['hot_job']));
    //The var $hotJobIDs will be an array of all the records that should be set to 1
    //All other records should be set to 0

   //Create ONE query to update the table
   $hotJobsIDsList = implode(', ', $hotJobIDs);
   $query = "UPDATE jobs
		  SET hot_jobs = (id IN ($hotJobsIDsList))";
   $result = mysql_query($query);

   //The following lines for debugging only
   if(!$result)
   {
    echo "Query Failed<br><br>Query: $query<br><br>Error: " . mysql_error();
   }

}

Edited February 15, 2013 by Psycho

[imdead]

Thankyou Psycho and requinix

 

I've used your tips and corrected everything, cheers