Subscribe to PHP Freaks RSS

Working with checkboxes and a database

Print
by Ben Smithers on Jan 10, 2009 12:47:52 PM - 143,086 views

Introduction

Over in the php help forum, the question of how to deal with checkboxes arises pretty often. Usually, people want to be able to select which rows of a database to update/delete/throw out the window.

The concept is actually pretty simple. It involves naming your checkbox as an array and the use of the IN mysql clause. This tutorial aims to give a simple example in the hope that, next time someone asks, I can point them to a tutorial rather than explaining all over again.

Right, on with the show.

The Database

Seeing as we're going to be updating a database, we'll be needing a table and some data. Here's a table I made earlier:

CREATE TABLE `tutorial_users` (

`id` INT( 10 ) NOT NULL AUTO_INCREMENT ,

`username` VARCHAR( 50 ) NOT NULL ,

`admin` TINYINT( 1 ) NOT NULL DEFAULT '0',

PRIMARY KEY ( `id` )

) ENGINE = MYISAM

And some data to work with. No prizes for guessing what I was just watching on TV:

INSERT INTO `tutorial_users` (

`id` ,

`username` ,

`admin`

)

VALUES (

NULL , 'Stewie', '0'

), (

NULL , 'Peter', '0'

), (

NULL , 'Brian', '0'

), (

NULL , 'Meg', '0'

), (

NULL , 'Lois', '0'

), (

NULL , 'Chris', '0'

), (

NULL , 'Greased Up Deaf Guy', '0'

), (

NULL , 'Quagmire', '0'

);

It's a simple setup. A user's table with a field called admin, which is the field we'll be updating.

If you're the kind of guy (or gal; we're all for equal opportunities here) who likes a quick fix – the full code follows. Otherwise, we'll be breaking it down.

The Code

<?php
include("connect.php");
$updated = FALSE;
if(count($_POST) > 0){
    $admin = $_POST['admin'];
    array_map('intval',$admin);
    $admin = implode(',',$admin);
    mysql_query("UPDATE tutorial_users SET admin=0") or trigger_error(mysql_error(),E_USER_ERROR);
    mysql_query("UPDATE tutorial_users SET admin=1 WHERE id IN ($admin)") or trigger_error(mysql_error(),E_USER_ERROR);
    $updated=TRUE;
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>phpfreaks checkbox tutorial</title>
</head>
<body>
<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
<?php
if($updated===TRUE){
    echo '<div>Privileges Updated!</div>';
}
?>
<table>
<tr>
<th>Username</th>
<th>Admin Privileges</th>
</tr>
<?php
$sql = "SELECT id,username,admin FROM tutorial_users ORDER by id ASC";
$result = mysql_query($sql) or trigger_error(mysql_error(),E_USER_ERROR);
while(list($id,$username,$admin)=mysql_fetch_row($result)){
    $checked = ($admin==1) ? 'checked="checked"' : '';
    echo '<tr><td>'.$username.'</td><td><input type="checkbox" name="admin[]" value="'.$id.'" '.$checked.'/></td></tr>'."\n";
}
?>
<tr><td colspan="2"><input type="submit" name="submit" value="Update Privileges" /></td></tr>
</table>
</form>
</body>
</html>

Interlude

Thanks for sticking around. Before we take a closer look at that code, I just want to mention a little about checkboxes and how they work.

You're probably aware that with a lot of other form elements, such as text fields, if you leave them blank they'll still appear in the $_POST/$_GET array – just with a blank value.

On the other hand, checkboxes behave a bit more like buttons. If you don't check them, they wont appear in the array. Assuming you do tick them, they'll take the value you give them or 'on' by default. Bear that in mind, it'll be important later.

Breaking it down

I'm going to start the breakdown a little way through the code from the opening form tag, seeing as you'll have to fill in that form before you can process it.

<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
<?php
if($updated===TRUE){
    echo '<div>Privileges Updated!</div>';
}

We have a standard form with a post method, and an action of the same page. We're also going to display a message if the database was updated – because we're nice like that.

<table>
<tr>
<th>Username</th>
<th>Admin Privileges</th>
</tr>

We're going to setup a table with the username and privilege listed in it. If you don't like tables, tough luck.

$sql = "SELECT id,username,admin FROM tutorial_users ORDER by id ASC";
$result = mysql_query($sql) or trigger_error(mysql_error(),E_USER_ERROR);

We select the fields we need from the database (not forgetting the id, as we'll use this to identify each row) and execute the query with a little error checking.

while(list($id,$username,$admin)=mysql_fetch_row($result)){

If you're not familiar with the list() construct, it allows us to assign lots of variables from a numerically indexed array in one go. I get bored of using $row all the time, so it makes a change. It's important to use mysql_fetch_row() so that you get a numerically indexed array (as opposed to an associative one) and that you create the variables with the same order you selected them in your query. See the manual page for a bit more.

$checked = ($admin==1) ? 'checked="checked"' : '';
    echo '<tr><td>'.$username.'</td><td><input type="checkbox" name="admin[]" value="'.$id.'" '.$checked.'/></td></tr>'."\n";

Inside that loop, we first work out if the checkbox should be ticked to start with. That is, if the user currently has admin privileges. Use of the ternary operator keeps this short and sweet. We then echo out a row for each user.

Notice that we name the checkbox admin[]. This means that $_POST['admin'] is going to be an array. An array of all the ticked boxes in fact. Also notice that we set the value to the id of the user.

<tr><td colspan="2"><input type="submit" name="submit" value="Update Privileges" /></td></tr>
</table>
</form>
</body>
</html>

Finally, we make a submit button and finish off our html. I don't know about you, but I find forms without a submit button a little unsatisfactory.

Next, we'll take a look at the processing.

Processing the form

include("connect.php");
$updated = FALSE;

First, we include a file that's going to connect us to our database. I would show you that file, but personally I prefer to keep my username and password to myself. We also create a variable that says we haven't updated the database...yet.

if(count($_POST) > 0){
    $admin = $_POST['admin'];
    array_map('intval',$admin);

Our if statement ensures that this code only runs if the form has been submitted. We create a local variable from the $_POST array. We then do a little bit of validation. We don't want any nasty users modifying any of those values in our form to do some SQL injection, so we make sure every value in that array is an integer with the array_map function. Again, see the manual page if you're not too sure how this function works.

"What if I don't have integers as my values?" Good question. You might like to use mysql_real_escape_string(), like so:

array_map('mysql_real_escape_string',$admin);

Or you might write your own function to make sure the values are the sort of thing you expect.

$admin = implode(',',$admin);

Next, we use the implode() function to turn our array into a string. We separate each value with a comma. This will allow us to use the IN clause in a moment. If your values aren't integers, you'll have to do something different. I'll cover that in a minute.

mysql_query("UPDATE users SET admin=0") or trigger_error(mysql_error(),E_USER_ERROR);
    mysql_query("UPDATE users SET admin=1 WHERE id IN ($admin)") or trigger_error(mysql_error(),E_USER_ERROR);
    $updated=TRUE;

Before we update the new users who have admin privileges, we first update the database so no-one does. Why? Well, our comma-delimited list contains just those people who we do want to have admin rights. So we better make sure no-one else does. Our second query makes use of that IN clause – it allows us to provide a comma-separate list of values that id can be.

Note: An alternative to updating every row to 0 would be to use the NOT IN clause.

Using strings

If your values are strings, you'll need to be a little more careful with your implode and query. Don't forget that all strings in your SQL must be inside quotes. It's a pretty simple adjustment, but it'll cause you an error if you forget. Make your implode look like this:

$admin = implode("','",$admin);

And your query like this:

mysql_query("UPDATE users SET admin=1 WHERE id IN ('$admin')") or trigger_error(mysql_error(),E_USER_ERROR);

Note the extra quotes – after we've imploded our values, they might look a little like this:

one','two','three','four

So we need to add the quote to the front and back.

And that's that. All we've got time for now is a short summary

Summary

So there we have it. The form gets processed and reloaded with the users having the new privileges. I hope that was easy to follow and worth the effort. Next time you work with checkboxes, it should be a little easier – there's really not much more to it.

Remember to name your checkbox as an array and set its value to something that uniquely identifies the row. If that's a string, take care when you implode the data. And don't forget to validate those values too. Most users are evil. Alright, maybe not. But if you think like that, you'll save yourself more than a few headaches.

Lastly, don't forget, you can name other pieces of your form so that they are arrays. Again, this is useful for updating an unknown, large amount of data simultaneously.

Comments

savagenoob Mar 1, 2009 8:34:25 PM

Needed to modify this variable to check the admin box if they were already admins...
$checked = ($admin==1) ? 'checked' : '';
Awesome tutorial man, learned alot, thank you very much...

acctman Mar 10, 2009 7:05:57 PM

is there a demo anywhere of this?

Ben Smithers Mar 11, 2009 4:00:25 AM

acctman, i could probably put this up somewhere, but im not sure what the point would be. What exactly did you want a demo of? There's not a lot of see apart from a few text boxes. The meat of the tutorial is the php code.

Alpho011 Apr 1, 2009 2:06:53 PM

Here are some source files that could help

http://diadde.com/dl/mail.zip

a clay
http://hubpages.com/profile/Alpho011

aviavi Apr 15, 2009 4:25:07 PM

thanks! i realy need that!

xnowandtheworldx May 2, 2009 1:17:02 AM

Awesome, learned some new functions from this tutorial, thanks! :) Great tutorial by the way.

Paprikate Jun 24, 2009 9:56:45 AM

Super! I added a second row of checkboxes and had some problems when no checkbox is ticked in one of both colums. But finaly, I simply droppt the OR command after the mysql_query(UPDATE ...) and so it works!
I learned a lot- thank you!

luvnrocs Jul 28, 2009 4:49:39 PM

Great tutorial! It helped me with a problem I was struggling with.

One question though...

What is meant by "So we need to add the quote to the front and back."?

Should I add the quote after the variable?

Ben Smithers Jul 29, 2009 8:51:51 AM

"What is meant by "So we need to add the quote to the front and back."?"

Perhaps a better phrasing of that sentence would be: So we need to add a quote mark to the beginning and end of the string returned by the implode function.

Basically, we need to make sure all strings in our query are contained within quotes. When we join together the values one, two, three and four with glue which is a comma inside two quote marks, we end up with the string :one','two','three','four

If we put that directly in our query, we'd have something like: "UPDATE users SET admin=1 WHERE id IN (one','two','three','four)", which isn't right -- we must add the quotation marks so we get: "UPDATE users SET admin=1 WHERE id IN ('one','two','three','four')"

Hope that makes things clearer.

Matt John Mar 13, 2010 7:15:48 AM

from the manual:
"A comment for a column can be specified with the COMMENT option. The comment is displayed by the SHOW CREATE TABLE and SHOW FULL COLUMNS statements. This option is operational as of MySQL 4.1. (It is allowed but ignored in earlier versions.)"

So for example

--
-- Table structure for table 'accesslog'
--

CREATE TABLE accesslog (
aid int(10) NOT NULL auto_increment COMMENT 'unique ID for each access entry',
title varchar(255) default NULL COMMENT 'the title of the page being accessed',
path varchar(255) default NULL COMMENT 'the local path of teh page being accessed',
....
) TYPE=MyISAM;

Matt John
----------------
<a href="http://www.fantastic-vacation.net/Key-West-Vacations.html">key west vacation packages</a>

Matt John Mar 13, 2010 7:16:25 AM

I'd prefer if the comments were done with --, so for example:

--
-- Table structure for table 'accesslog'
--
CREATE TABLE accesslog (
aid int(10) NOT NULL auto_increment, -- 'unique ID for each access entry'
title varchar(255) default NULL, -- 'the title of the page being accessed'
path varchar(255) default NULL -- 'the local path of teh page being accessed'
....
) TYPE=MyISAM;

Matt
-----------
key west vacation packages

jim002 Jun 14, 2010 11:41:49 PM

Thanks man. I learn how to make short if/else statement :D

ThunderVike Aug 3, 2010 8:15:14 AM

This is fine for the first time a user checks something, but gets more complicated when you want to give the user the ability to edit the checkboxes themselves. I use WordPress with a classified ads theme. The multiple checkboxes are giving me a problem because when NO checkboxes are selected, or I should say, when ALL the checkboxes are de-selected after having been saved previously with selected values, the checkboxes return after updating with the last values still checked from the previous save.

I need to overwrite the previous array of values for a multiple checkbox if it is updated in a completely unchecked condition.

Everything I try and that people suggest at the phpfreaks forum fails.

This is where I got the function to last night... this part is pulling custom fields associated with a wordpress post that are stored with a "cp_" prefix to differentiate them from "wp" custom fields.

However, my checkbox values are also stored with a "cp_" prefix only extended with such terms as "cp_checkbox_charley" or variants of "cp_checkbox_whatever".

These are the ONLY custom fields that are stored in a comma-delimited string of text. The regular "cp_" fields just return one string value or in the dropdown list also stored there just one Option value.

So, on update, the user needs to use a specific edit page for a post that belongs to them and is only edited by them. Therefore, since this is not WPMU, the user must select his post on this special editing page, open it up there and change it for whatever reason. They will see their previous checkboxes checked for whatever values they selected when the post was last saved.

Right now everything works fine--custom fields get updated just fine, and multiple checkbox values if changed get updated correctly. Except that if the user UNSELECTS all checkboxes for a particular multiple checkbox so that the cp_checkbox_charley[] (example) has no checked or selected values then the array is not updated on submit.

I need to inject a null value or unset the array for just those cp_checkbox $meta_keys and their $meta_value.

Standard phpfreak suggestions do nothing so far.

here is the offending code:

// update the ad and return the ad id
$post_id = wp_update_post($update_ad);
if($post_id) {
// now update all the custom fields
foreach($_POST as $meta_key => $meta_value) {
if (cp_str_starts_with($meta_key, 'cp_'))
if (cp_str_starts_with($meta_key, 'cp_checkbox_charley') && is_array($_POST['cp_checkbox_help']))
$meta_value= implode(',', $_POST['cp_checkbox_charley']);
else if (cp_str_starts_with($meta_key, 'cp_checkbox_charley') && is_null($_POST['cp_checkbox_help']))$meta_value= NULL ;
if (cp_str_starts_with($meta_key, 'cp_checkbox_help'))
$meta_value = implode(',', $_POST['cp_checkbox_help']);
if (cp_str_starts_with($meta_key, 'cp_checkbox_hello'))
$meta_value= implode(',', $_POST['cp_checkbox_hello']);
//echo $meta_key . ' <--metakey <br/>' . $meta_value . ' <--metavalue<br/><br/>'; // for debugging
update_post_meta($post_id, $meta_key, $meta_value);
}

$errmsg = '<div class="box-yellow"><b>' . __('Your ad has been successfully updated.','cp') . '</b> <a href="' . CP_DASHBOARD_URL . '">' . __('Return to my dashboard','cp') . '</a></div>';

} else {
// the ad wasn't updated so throw an error
$errmsg = '<div class="box-red"><b>' . __('There was an error trying to update your ad.','cp') . '</b></div>';

}

ThunderVike Aug 3, 2010 8:18:12 AM

If I could get something that would work on "cp_checkbox_charley" then I would extend the same to the other checkboxes. So the code does not reflect yet what I finally want to do....to allow all checkbox arrays to be "erased" if the corresponding checkboxes are completely deselected or unchecked.

phprocker Nov 6, 2010 12:12:07 AM

I believe the section of code you use that calls the array_map function is a bit wrong. You are not actually using new values when you implode the $admin variable. I tested it. You need to set that array_map call to a new variable and then implode that new variable. Try it. Take the 3 lines of code from "$admin =" to the implode line. Enter strings as the original array and you'll see the don't get changed to integers. You must assign the array_map to a new var and implode that var for it to work.

The tutorial was great. Cheers!

elrobbo6 Sep 10, 2011 9:06:01 AM

I know this is a really old post but Im hoping someone will see this... for some reason everything works fine except it does not actually update the database. Any ideas?

j03 Feb 17, 2012 4:59:13 PM

For me I had to comment out or trigger_error(mysql_error(),E_USER_ERROR); and replace it with: or die(mysql_error());. Not sure if this has been covered in the comments but when all the checkboxes are unselected it shows this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1. Not sure what to do with this..

Great tutorial, I've been working on this for about a month on and off, who knew a couple lines of code could solve this..

markandreson May 8, 2012 2:38:20 PM

I offer none. There are so many conventions in play regarding where to put your code, what tool you are already using, and so forth, that it is really up to the reader to draw conclusions. I only hope there is enough information here to do so CCTV

markandreson Sep 15, 2012 3:23:28 AM

Using MySQL User-Defined Variables:
$database = JFactory::getDBO();
$database->setQuery("SET @num := 0");
$database->query();
$database->setQuery("SET @num := @num + 5");
$database->query();
$database->setQuery("SELECT @num");
$result = $localDB->loadResult();
if you need further assistance log on to juniperdesert, you may also find relevent information on termpapers.ws

markandreson Dec 20, 2012 1:01:59 PM

Take the 3 lines of code from "$admin =" to the implode line. Enter strings as the original array and you'll see the don't get changed to integers. You must assign the array_map to a new var and implode that var for it to work <a href="http://www.hatchriverexpeditions.com/">grand canyon rafting</a>

Add Comment

Login or register to post a comment.