Php - Working with checkboxes and a database

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:

1. CREATE TABLE `tutorial_users` (
2. 
   
3. `id` INT( 10 ) NOT NULL AUTO_INCREMENT ,
4. 
   
5. `username` VARCHAR( 50 ) NOT NULL ,
6. 
   
7. `admin` TINYINT( 1 ) NOT NULL DEFAULT '0',
8. 
   
9. PRIMARY KEY ( `id` )
10. 
    
11. ) ENGINE = MYISAM

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

1. INSERT INTO `tutorial_users` (
2. 
   
3. `id` ,
4. 
   
5. `username` ,
6. 
   
7. `admin`
8. 
   
9. )
10. 
    
11. VALUES (
12. 
    
13. NULL , 'Stewie', '0'
14. 
    
15. ), (
16. 
    
17. NULL , 'Peter', '0'
18. 
    
19. ), (
20. 
    
21. NULL , 'Brian', '0'
22. 
    
23. ), (
24. 
    
25. NULL , 'Meg', '0'
26. 
    
27. ), (
28. 
    
29. NULL , 'Lois', '0'
30. 
    
31. ), (
32. 
    
33. NULL , 'Chris', '0'
34. 
    
35. ), (
36. 
    
37. NULL , 'Greased Up Deaf Guy', '0'
38. 
    
39. ), (
40. 
    
41. NULL , 'Quagmire', '0'
42. 
    
43. );

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 is on the next page. Otherwise, we'll be breaking it down on pages 4 and 5.

The Code

2. include("connect.php");
3. $updated = FALSE;
4. if(count($_POST) > 0){
5. $admin = $_POST['admin'];
6. array_map('intval',$admin);
7. $admin = implode(',',$admin);
8. mysql_query("UPDATE tutorial_users SET admin=0") or trigger_error(mysql_error(),E_USER_ERROR);
9. mysql_query("UPDATE tutorial_users SET admin=1 WHERE id IN ($admin)") or trigger_error(mysql_error(),E_USER_ERROR);
10. $updated=TRUE;
11. }
12. ?>
20. " method="post">
22. if($updated===TRUE){
23. echo '
    Privileges Updated!
    ';
24. }
25. ?>
26. $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 '
    '."\n";
    }
    ?>

    Username	Admin Privileges
    '.$username.'

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.

1. " method="post">
3. if($updated===TRUE){
4. echo '
   Privileges Updated!
   ';
5. }

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.

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

   1. $sql = "SELECT id,username,admin FROM tutorial_users ORDER by id ASC";
   2. $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.

   1. 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.

   1. $checked = ($admin==1) ? 'checked="checked"' : '';
   2. echo '
   '."\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.

   Username	Admin Privileges
   '.$username.'

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.

On the next page, we'll take a look at the processing.

Processing the form

1. include("connect.php");
2. $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.

1. if(count($_POST) > 0){
2. $admin = $_POST['admin'];
3. 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:

1. 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.

1. $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.

1. mysql_query("UPDATE users SET admin=0") or trigger_error(mysql_error(),E_USER_ERROR);
2. mysql_query("UPDATE users SET admin=1 WHERE id IN ($admin)") or trigger_error(mysql_error(),E_USER_ERROR);
3. $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:

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

And your query like this:

1. 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.

We'll sum up on the last page

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.