PHP/MySQL Update checkbox selection to database

Question

I'm trying to update a database field with a 1 or 0, depending on whether a checkbox is checked or not.

It seems to work when the checkbox is checked but not when empty.

Any ideas how to solve this? Code below. Many Thanks, S.

Here's the code within my form:

$query  = "SELECT * FROM istable WHERE assocProp = '".$_POST['id']."'";
    $result = mysql_query($query);              
    while($row = mysql_fetch_array($result, MYSQL_ASSOC))
        {                                           
        echo '<li>                          
                <label for="changePP'.$row['id'].'"><input id="changePP'.$row['id'].'" type="checkbox" name="showPP_ids[]" value="'.$row['id'].'"'.($row['showPP']=='1' ? ' checked="checked"':NULL).' /> Display</label>
                </li>'. PHP_EOL;                                        
        }

And the php process code (updated):

$newQuery=mysql_query("select * from ispdfs where assocProp = '".$_POST['id']."'");
    $newResult=mysql_fetch_array($newQuery);    

    foreach ($newResult as $showPP_ids) {
      $val = (int) isset($_POST['showPP_ids']); 
        mysql_query("UPDATE ispdfs SET showPP = $val WHERE id = ".mysql_real_escape_string($showPP_ids));
    }

awm · Accepted Answer · 2011-02-16 14:20:14Z

3

You have nothing here that sets the values to zero. Boxes that are not checked will simply be absent from the $_POST array.

You'll need to make a separate list of the names of all of the checkboxes and cycle through those, comparing them against the $_POST array.

Edit: Wasn't going to write any code, but:

$allids = array('id1','id2','id3');

foreach ($allids as $oneid) {
  $val = (int) isset($_POST[$oneid]);  // will be 0 or 1
  mysql_query("UPDATE istable SET showPP = $val WHERE id = ".mysql_real_escape_string($oneid));
}

Note that we don't really need the mysql_real_escape_string here since we know that all the id values are safe, but it's good practice just in case someone comes along later and carelessly changes the $allids array.

Edit again: Suppose we don't know what ids to look for.

mysql_query("UPDATE istable SET showPP = 0");
foreach ($_POST as $oneid=>$nothing) {
  mysql_query("UPDATE istable SET showPP = 1 WHERE id = ".mysql_real_escape_string($oneid));
}

edited Feb 16, 2011 at 14:20

answered Feb 16, 2011 at 13:40

awm

6,58027 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Martin Hennings Over a year ago

This works if you know the fieldnames beforehand. Combine this method with your SQL query to create your list of fieldnames on the fly.

awm Over a year ago

Yup! On the other hand, if you don't know them beforehand, then you're necessarily updating the entire table... in which case you may as well set them all to zero first and then loop through the $_POST array.

ss888 Over a year ago

Hi, many thanks for the replies: Have kind of got it working using your code, but it only updates the first result, no others. Code below:

$newQuery=mysql_query("select * from ispdfs where assocProp = '".$_POST['id']."'"); 		$newResult=mysql_fetch_array($newQuery);	  		foreach ($newResult as $showPP_ids) { 		  $val = (int) isset($_POST['showPP_ids']);  		 	mysql_query("UPDATE ispdfs SET showPP = $val WHERE id = ".mysql_real_escape_string($showPP_ids)); 		}

Martin Hennings Over a year ago

Yes, because awm's solution doesn't go through the fields of $_POST['showPP_ids']. @awm: You can make your code example perfect by testing if $nothing is an array -> if yes, loop it. @all: This is still not ideal. I would collect the necessary updates (e.g. with concat into the query string) and perform the mysql_query not in the loop but afterwards = only once.

ss888 Over a year ago

Not sure I'm following... Are you saying that I need to wrap my above code in if (isset($_POST['linkTitle_ids'])) { or just if ($_POST['linkTitle_ids']) {?

|

Martin Hennings · Accepted Answer · 2011-02-16 14:19:03Z

2

Probably your checkbox value doesn't get sent when it's not checked. Confirm this by print_r($_POST) in your php code.
Because of this, your condition
```
if (isset($_POST['showPP_ids'])) {  
    foreach ($_POST['showPP_ids'] as $showPPId) {   
```
will never find a checkbox that wasn't checked.
As awm says correctly (+1 to that), you don't set the field showPP to 0. Do this:
```
$ppsql=mysql_query("UPDATE istable SET showPP = '0' WHERE id = ".mysql_real_escape_string($showPPId));
```
when you want to set your sql field to 0.

Since your checkbox won't be sent when it's not checked, you need to have some more information on which checkboxes to expect.

You could do (reusing your query code)

$query  = "SELECT * FROM istable WHERE assocProp = '".$_POST['id']."'";
$result = mysql_query($query);              
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    $found = false;
    foreach($_POST['showPP_ids'] as $showPPId)
    {
        if($showPPId == $row['id'])
        {
            // checkbox found
            $found = true;
            break;
        }
    }
    $ppsql=mysql_query("UPDATE istable SET showPP = '"
                       .($found ? '1' : '0')
                       ."' WHERE id = ".mysql_real_escape_string($showPPId));                          
}

An alternative would be JavaScript: Provide either two checkboxes and set the "not"-checkbox to true if the other one gets unchecked or use an <input type="hidden"> and change its value when the checkbox gets changed. If you have questions to this method, leave a comment - I've used that some times before...

another approach
Staying with your code, you could use this simple approach (but please don't...):

// first, set all fields to zero
/* long version
$query  = "SELECT * FROM istable WHERE assocProp = '".$_POST['id']."'";
$result = mysql_query($query);              
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
    {                                           
         $ppsql=mysql_query("UPDATE istable SET showPP = '0' WHERE id = ".$row['id']);
    } 
*/
// or short version:
$ppsql=mysql_query("UPDATE istable SET showPP = 0");

// now, update these rows that appear in `$_POST['showPP_ids']`
// (using the code you provided)
if (isset($_POST['showPP_ids'])) {  
    foreach ($_POST['showPP_ids'] as $showPPId) {                   
        $ppsql=mysql_query("UPDATE istable SET showPP = '1' WHERE id = ".mysql_real_escape_string($showPPId));                          
    }
}

edited Feb 16, 2011 at 14:19

answered Feb 16, 2011 at 13:41

Martin Hennings

17k9 gold badges51 silver badges70 bronze badges

3 Comments

awm Over a year ago

Cheers, but not quite... isset($_POST['showPP_ids']) will always evaluate to true.

awm Over a year ago

The first six lines of "another approach" do this: update istable set showPP=0. No need for a loop.

ss888 Over a year ago

Many thanks for the replies, much appreciated. Had a go with the code you provided but couldn't get any of it to work : ( Have kind of got awm's working, (see edit). S

Collectives™ on Stack Overflow

PHP/MySQL Update checkbox selection to database

2 Answers 2

8 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related