Sr. Web Developer
mediabistro.com
US-NY-New York

Justtechjobs.com Post A Job | Post A Resume

Storing Checkbox Data In Your Database
Dan LaFlamme picture of Dan LaFlamme
Introduction
Checkboxes in web forms are useful because they allow the person filling out the form to choose from a wide range of possible choices. Specifically, it allows the user to choose some, all or none of the elements. Although checkboxes are excellent form elements for certain tasks, there is often confusion about how to store checkbox data correctly. This article will demonstrate how to store checkbox data in a database, in a manner that follows good database design principles.
Requirements
In this article, I will demonstrate the method of storing checkbox data on a per user basis. Although there is useable PHP source code included, I'm presenting this in a database design point of view. Therefore, the information here can be used with any database and server side scripting language of choice. The main idea here is to provide you with an explanation of how to do it so you can apply it to the sites you build. If you do want to play with the source code, you'll need php, mysql, and a webserver.
Example: A Job Posting Site
Consider the following situation. You are asked to create a web site that will allow unemployed web developers to post their skills there. Potential employers can then visit this website and search for prospective employees, based on the skills they are looking for. As an aspiring web developer yourself, you know that a web programmer with work is more desirable than one without. Therefore, you decide to write the code for his site.
Each unemployed web developer will presumably visit the site, create an account, and enter his skills in some way or another. Checkboxes immediately come to mind. You envision a page that looks something like this:
  __ PHP     __ MySQL        __ Zope
  __ Perl    __ Javascript   __ JSP

                          [Submit]
Each developer goes through and checks the boxes that match his skills. Obviously, different developers will check different boxes. One may check PHP and MySQL, while another checks only JSP. How will you store this data? It is natural to want to store each developer's list of skills in the user table shown above. After all, each user will have a row in this table and you can just make some fields in each row to hold their skills. This may work at first, but you will almost surely run into trouble at some point when you wish to expand or modify the database. Also, your user table would be unnecessarily wide. There is a better way to do this. Here is how.
We'll have a user table that contains user login information such as username, password, and some other fields that we might use. A simple user table is shown below. I will assume you can create a table like this if you plan to use some of the example code found later in the article.
id username
1 User1
2 User2
3 User3
We first create a table called "const_skills" with the following SQL code: 
SQL> CREATE TABLE const_skills ( 
	id     int  not null primary key, 	 
	value  varchar(20) );
And now we will populate it with skills: 
SQL> INSERT INTO const_skills(id, value) VALUES (1, "PHP");
SQL> INSERT INTO const_skills(id, value) VALUES (2, "MySQL");
SQL> INSERT INTO const_skills(id, value) VALUES (3, "Zope");
SQL> INSERT INTO const_skills(id, value) VALUES (4, "Perl");
SQL> INSERT INTO const_skills(id, value) VALUES (5, "Javascript");
SQL> INSERT INTO const_skills(id, value) VALUES (6, "JSP");
Your const_skills table now looks like this:
id value
1 PHP
2 MySQL
3 Zope
4 Perl
5 Javascript
6 JSP
This table's only purpose is to hold the names of the skills that web developers can select. Now, create a table called "lookup_skills" with the following SQL: 
SQL> CREATE TABLE lookup_skills ( 
	id        int  not null auto_increment primary key,
	uid       int,
	skill_id  int );
The purpose of lookup_skills is to provide a mapping from userids to web devloper skills. In other words, it allows us to store the developers and what skills they have. So, when the unemployed web developer hits the submit button after filling out our form, we will populate this table with the checkbox values that were checked off. For each skill the developer checks off, a row will be added with the user's uid from the user table and the number corresponding to the skill he selected in the const_skills table. Before we look at the code to do the insert, lets first use our design to make creating our form easier. Each time that the user requests the page that has our form on it, we can query the database and get the checkbox labels from the const_skills table and create the checkbox form with the appropriate checkbox choices. Here is the code to do that.

<?php

  /* insert code to connect to your database here */  
  
  /* get the checkbox labels */
  $skills = get_checkbox_labels("const_skills");
  
  /* create the html code for a formatted set of
     checkboxes */
  $html_skills = make_checkbox_html($skills, 3, 400, "skills[]");

?>
 
<html>
<body>
<br>
<form name="skills" method="POST" action="insertskills.php">
   Check off your web development skills:

   <? echo "$html_skills"; ?>
   <br>
   <input type="submit" value="Submit">
</form>
</body>
</html>
<?php

function get_checkbox_labels($table_name) {

  /* make an array */
  $arr = array();
  
  /* construct the query */
  $query = "SELECT * FROM $table_name";
  
  /* execute the query */
  $qid = mysql_query($query);

  /* each row in the result set will be packaged as
     an object and put in an array */
  while($row= mysql_fetch_object($qid)) {
    array_push($arr, $row);
  }
  
  return $arr;
}

/* Prints a nicely formatted table of checkbox choices.
   
   $arr is an array of objects that contain the choices
   $num is the number of elements wide we display in the table
   $width is the value of the width parameter to the table tag
   $name is the name of the checkbox array
   $checked is an array of element names that should be checked
*/   


function make_checkbox_html($arr, $num, $width, $name, $checked) {
  
  /* create string to hold out html */
  $str = "";
  
  /* make it */
  $str .= "<table width=\"$width\" border=\"0\">\n";
  $str .= "<tr>\n";

  /* determine if we will have to close add
     a closing tr tag at the end of our table */
  if (count($arr) % $num != 0) {
    $closingTR = true;
  }
  
  $i = 1;
  if (isset($checked)) {
    /* if we passed in an array of the checkboxes we want
       to be displayed as checked */
    foreach ($arr as $ele) {
      $str .= "<td><input type=\"checkbox\" name=\"$name\" value=\"$ele->id\"";
      foreach ($checked as $entry) {
    if ($entry == $ele->value) {
      $str .= "checked";
          continue;
        }
      }
      $str .= ">";
      $str .= "$ele->value";

      if ($i % $num == 0) {
        $str .= "</tr>\n<tr>";
      } else {
        $str .= "</td>\n";
      }
      $i++;
    }
  
  } else {
    /* we just want to print the checkboxes. none will have checks */
    foreach ($arr as $ele) {
      $str .= "<td><input type=\"checkbox\" name=\"$name\" value=\"$ele->id\">";
      $str .= "$ele->value";
      
      if ($i % $num == 0) {
        $str .= "</tr>\n<tr>";
      } else {
        $str .= "</td>\n";
      }
      $i++;
    }
  
  }

  /* tack on a closing tr tag if necessary */
  if ($closingTR == true) {
    $str .= "</tr></table>\n";
  } else {
    $str .= "</table>\n";
  }

  return $str;
}


?>
That was quite a bit of code, so here is a quick overview of what is going on. The majority of the work is done in two functions, get_checkbox_labels and make_checkbox_html. get_checkbox_labels queries our const_skills table and returns an array of objects, each object having an id and the name of the skill that corresponds with that number. We then pass this array to make_checkbox_html along with a few other parameters and it returns a string with the html code for the checkboxes. We now just insert the string into our html form code to include the skill checkboxes in our form. Notice that I didn't pass the $checked argument to make_checkbox_html. This argument is an array of the objects we want to appear checked. If a user learned a new web development skill after filling out the form for the first time, we could provide an "edit skills" page that would present the checkboxes with his previously stored skills already checked off.
What is the benefit of creating the form in this way instead of simply hard coding the skill choices in the html code of our form? Well, suppose we want to allow the web developers to be able to choose a skill that we don't already have in our const_skills table, DHTML for instance. All we would need to do is insert a row for DHTML into the const_skills table. Then, when the developers go to the form page, DHTML will be there. No modifications to the html form code is necessary.
Inserting into lookup_skills
Now that we have the code to create the form, we need to be able to store the skills that the user checks off. In the make_checkbox_html function above, we called the name of each checkbox element skills[]. The "[]" specify that we will get access to the checked elements in an array. So, all we have to do is run through the array and insert rows into the lookup_skills table. If the person filling out the form clicks 5 skills, we insert 5 rows into the lookup_skills table. Remember that each row in the lookup_skills table has a user id number and a skill number. In the example of the site I gave, users log in and then they can create/edit their profile. You would probably register their userid number as a session variable when they log in, but how you manage userids is beyond the scope of this article. In the code below, we will just assume that we have access to the userid in a variable called $uid. So here are some functions to do the insert:

<?php

/* the function we call to insert.
   the $skills argument is the skills array that
   is sent to the script when the user hits the submit button
*/
function insert_skills($uid, $skills) {
   
   /* first, we'll delete any entries this user already has
      in the table */
   purge_lookup("lookup_skills", $uid);
   
   /* now create the sql insert query */
   $query = create_checkbox_query($skills, "lookup_skills", $uid);
   
   /* execute the query */
   mysql_query($query);
}

/* helper function for insert_skills().
   removes all rows in $table with $uid */
function purge_lookup($table, $uid) {
  $q = "DELETE FROM $table, WHERE uid = '$uid'";
  mysql_query($q);
}

/* helper function for insert_skills().
   generates the sctual SQL query */
function create_checkbox_query($arr, $table, $uid) {
   $q = "INSERT INTO $table (uid, skill_id) VALUES";
  
   foreach ($arr as $check) {
     $q .=  " ( $uid , $check )" . ",";
   }
   
   /* remove the last comma and return */  
   return substr($q, 0, -1);
}

?>
That is pretty much all there is to inserting. You now know how to create your form easily by reading from your const_skills table and you also know how to store the users skill selections by simply inserting rows into the lookup_skills table. So now what do you do? Lets look at how to search.
Searching
When someone looking to hire web developers comes along and goes to your search page, you can show the same form and allow that person to check the qualifications he is looking for. You'll get the array of his choices, then you can just loop through that array, building a query to select web developers with those qualifications. You can then display a list or results and allow the searcher to click on an entry in the list to see a more detailed profile. Here is a function demonstrating how to build the query:

<?php

/* builds a query to search for the skills
   checked off in the $skills array */

function skill_search($skills) {
   if (!empty($skills)) {
     $query = "SELECT DISTINCT user.username
                 FROM user, const_skills, lookup_skills
                WHERE lookup_skills.uid = user.id  
                  AND lookup_skills.skill_id = const_skills.id ";

     $query .= " AND (";
     foreach ($skills as $check) {
       $query .= " const_skills.id = $check OR";
     }

     /* remove the final OR */
     $query = substr($query, 0, -2);
     $query .= ")";
   
     $count = count($skills);
     $query .= " GROUP BY user.username HAVING count(user.username) >= $count";
    
     $query .= ";";
     return $query;
   }
}

?>
If the person performing the search checked "PHP" and "Javascript", this function would return the query: 
SELECT DISTINCT user.username FROM user, const_skills, lookup_skills 
 WHERE lookup_skills.uid = user.id AND lookup_skills.skill_id = const_skills.id 
   AND ( const_skills.id = 3 OR const_skills.id = 5 ) 
 GROUP BY user.username HAVING count(user.username) >= 2;
Note that this function returns the logical "AND" of the boxes you check. That is, if both PHP and Javascript are checked as in the above example, we will only get usernames of those web developers who know *BOTH* PHP and Javascript. If you wanted the usernames of those who know either PHP *OR* Javascript (or both), everything would be the same except that you would remove the line that appends the SQL code "GROUP BY..." to the query.
Conclusion
Well, that's it for this article. Checkboxes are excellent for tasks like the one mentioned here and I hope this helps people wondering how to work with them on their data driven web sites.
-- Dan