Insert Single Checkbox value into mySQL database

drewrockshard
drewrockshard used Ask the Experts™
on
I am trying to insert a single checkbox value into a mySQL database field.

I thought this would be pretty easy, however, I'm finding that is not true.  All I want it to do is, if the checkbox is checked, insert a "0" into a mysql table, and if the checkbox IS checked, to insert a "1".  It's a single checkbox.  I'm using checkboxes because i don't want to use two radio buttons just to portray "yes or no".

Here's the HTML snippet:

<input type="checkbox" id="active" name="active" value="0" />

The above is outputted as a result of a function that checks the database to see if there is a "1" or a "0".  So then this is displayed to the user.  So in this case, the checkbox un unchecked with a value of "0".  What I want to be able to do is check the checkbox, and for it to insert into the database a "1".  I want to do this and vise versa.  If it is a "1", and I uncheck it, I want to be able to place a "0" into the database.

Getting the current database results is not the problem, it's pushing the changes to the database that are.

Please let me know if you guys need additional info.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2016

Commented:
The value of a checked checkbox is "on"

The value of an unchecked checkbox is simply not there - nothing comes through in $_POST.

Please post the code you're using now to process the checkbox.  Maybe we can help revise it.

Best, ~Ray

Author

Commented:
Hmmm.

Is there any way to push the "0" and "1" - or should I go about this another way?

Here's some code:

// Checks to see if the customer is active or not.
function isActiveCustomer() {
        global $db;

        if ($_POST['customerid'] == "") {
                $custID = $_GET['customerid'];
        }
        else {
                $custID = $_POST['customerid'];
        }
        if ($_POST['customerid'] == "" && $_GET['customerid'] == "") {
                $query  = "SELECT CustomerActive FROM customers LIMIT 0,1";
        }
        else {
                $query  = "SELECT CustomerActive FROM customers WHERE CustomerID='$custID'";
        }
        $activeCheck = $db->get_row("$query",ARRAY_A);

        if($activeCheck['CustomerActive'] == "1") {
                echo "value=\"1\" checked";
        }
        elseif ($activeCheck['CustomerActive'] == "0") {
                echo "value=\"0\"";
        }
}

Previous code snippet actually fills out the rest of the html input element previously shown.  In the for, the code is actually this:

<input type="checkbox" id="active" name="active" <?php echo isActiveCustomer(); ?> />

So the function isActiveCustomer() will just add the additional information from the database results and append (or not) the "checked" text.

Now, what I have been doing to update the database is the following, however, it doesn't appear to be working:

function updateBasicInfo() {
        global $db;

        $active = $_POST['active'];
$query = "UPDATE customers SET CustomerMapsco='$mapsco', CustomerActive='$active', CustomerFirstName='$custFirstName', CustomerLastName='$custLastName', Customer2FirstName='$cust2FirstName', Customer2LastName='$cust2LastName', CustomerServiceStreet='$serviceAddr', CustomerServiceCity='$serviceAddrCity', CustomerServiceState='$serviceAddrState', CustomerServiceZip='$serviceAddrZip', CustomerBillingStreet='$billingAddr', CustomerBillingCity='$billingAddrCity', CustomerBillingState='$billingAddrState', CustomerBillingZip='$billingAddrZip', CustomerHomePhone='$homePhone', CustomerCellPhone='$cellPhone', CustomerWorkPhone='$workPhone', CustomerWorkExt='$workPhoneExt', CustomerFax='$faxPhone', CustomerEmail='$emailAddr', CustomerDog='$hasDogs', CrewID='$assignedCrew', ScheduleID='$scheduleNum', AssignedCompanyID='$assignedCompany', FuelSurcharge='$fuelSurcharge' WHERE CustomerID='$custID'";

        $updateDatabase = $db->query($query);
        if ($updateDatabase) { ?>
                <p class="success">Customer information was successfully updated.</p>
<?php
        }  else { ?>
                <p class="failed">Failed to update customer information.</p>
<?php   }
}

Pay no attention to the other variables reference.  Those have been taken out to save space.  They all work, I just posted you the code to show what I use to "update" the database - but it doesn't work.  It does exactly what you said - it places "on" into the database, and it places nothing (null) if I uncheck it.
Most Valuable Expert 2011
Top Expert 2016

Commented:
It's really helpful to have the code posted in the "attach code snippet" feature - makes it easy to read and gives us line numbers we can all use to refer to the lines.

I'm sure we can translate the values into something useful, but rather than try to go through all of that, let me show you an example that you can install and run.  You can watch the way the checkboxes work and then decide if your strategy makes sense for your application.
<?php // RAY_checkboxes.php DEMONSTRATE HOW CHECKBOXES WORK IN FORM AND ACTION SCRIPTS
error_reporting(E_ALL);
 
// IF ANYTHING HAS BEEN POSTED, SHOW THE CONTENTS
if (!empty($_POST))
{
   echo "<pre>";
   echo "\$_POST CONTAINS:\n";
   var_dump($_POST);
   echo "</pre> \n";
}
?>
<form method="POST">
 
These checkboxes have names...<br/>
Check or Uncheck Some Boxes Here<br/>
<input name=box1 type="checkbox" value="Box1"><br/>
<input name=box2 type="checkbox" value="Box2"><br/>
<input name=box3 type="checkbox"><br/>
<input name=box4 type="checkbox"><br/>
<br />
 
These checkboxes are part of the "box" array...<br/>
Check or Uncheck Some Boxes Here<br/>
<input name=box[] type="checkbox" value="ABox1"><br/>
<input name=box[] type="checkbox" value="ABox2"><br/>
<input name=box[] type="checkbox" value="ABox3"><br/>
<input name=box[] type="checkbox" value="ABox4"><br/>
<input type="submit" value="go" />
</form>

Open in new window

Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Author

Commented:
Ray:

So does this mean I am unable to do what I want with this?  Please let me know.  I understand how this works, however, it doesn't appear that it will do what I want it to do.

Let me know.

Author

Commented:
Nevermind.  Using radio buttons now.
Most Valuable Expert 2011
Top Expert 2016

Commented:
I've been tinkering around with this some, and I think you can do exactly what you want, but the PHP script needs to be aware of the checkbox names.

Author

Commented:
Ray,

You can paste the code and I will test it since you worked on it.  I would love to use checkboxes, but if not, I can easily use radio buttons.  If you decide to post the code and I get it working, then you can reject my solution (as myself) and I'll award the points.
Most Valuable Expert 2011
Top Expert 2016
Commented:
In a more perfect world (or when I was working for pay) I would derive this from the DB tables, but for now, you can do this.  Install this script and run it.  It teaches how to translate the POST array of 'on' values into the ones and zeros in the $checkboxes array.

Going forward, if you don't really care enough about the answer to a question to work though it with the EE community, it might be better to save your points for something you care about.  I'm not particularly worried about points - I have enough points to orbit Saturn.  But I hate to feel like my time was wasted on a task that didn't really matter.  I could have spent the time helping someone else, y' know?
<?php // RAY_temp_drew.php
error_reporting(E_ALL);
echo "<pre>\n";
 
// DEFAULT VALUES FOR THE checkboxesES - ALL OFF
$checkboxes[1] = 0;
$checkboxes[2] = 0;
$checkboxes[3] = 0;
 
// IF ANYTHING HAS BEEN POSTED DISPLAY IT
if (!empty($_POST))
{
    echo "\n_POST: ";
    var_dump($_POST);
}
 
// ITERATE OVER THE POST ARRAY TO SET THE VALUES OF THE checkboxes ARRAY
foreach ($_POST as $key => $value)
{
    $checkboxes[$key] = 1;
}
 
// SHOW THE CHECKBOXES
echo "\nCHECKBOXES: ";
print_r($checkboxes);
 
// END OF PHP - PUT UP THE FORM
?>
<form method="post">
<input type="checkbox" name="1" /> ONE
<input type="checkbox" name="2" /> TWO
<input type="checkbox" name="3" /> TRE
<input type="submit" />
</form>

Open in new window

Author

Commented:
I'm on a time crunch.  From your previous update stating the following:

"The value of an unchecked checkbox is simply not there - nothing comes through in $_POST."

Then you posting demostrating how checkboxes work - I figured you were basically saying that it wasn't possible.  So thats when I tried something else, and after posting, you said it was possible if you changed some code around.  So, thats when I stated I would award points.  So, I wanted to get this to work, however, I was under the impression that it wasn't possible.  No offence to you.

I'll implement this code as this is what I really wanted.  Don't just to conclusions as to how I don't want to put my time to something - I figured you were a PHP guru and by stated your initial thoughts, it sounded like it wasn't possible.

Thanks.

Author

Commented:
This code worked perfectly as to what I was trying to do.
Most Valuable Expert 2011
Top Expert 2016

Commented:
"sounded like it wasn't possible...."  For the most part, things are always "possible" in PHP - it's just a matter of how much time you want to put into crafting the solution and whether the design is suitable for a particular purpose.

PHP has over 5,000 functions.  One of them is bound to work ;-)
http://us2.php.net/quickref.php

Thanks for the points and good luck with your project, ~Ray

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial