Translating Checkboxes to MySQL / PHP Code

Posted on 2005-04-20
Last Modified: 2008-02-01
Hello Experts,

I have a search form used to locate missing persons reports matching certain criteria, and am adding a section with checkboxes to examine field "eyecolor". I am using a shorter version for this question.

The boxes on the form are:
1> All
2> Black
3> Blue
4> Brown

In the database, black is stored as "1", blue as "2", and Brown as "3"

How can I write the PHP and SQL query to handle this on the backend? When I tried to write it, I can't account for how to add the "OR" between multiple selction in the SQL, or how to not list "OR" if only one item is chosed.

The added twist is  - I need for "all" to somehow negate any selections and not add specification to the query.

Thanks in advance for any help or guidance you can provide,


Question by:epaschal
    LVL 6

    Expert Comment

    for negation, you can either turn to javascript (insecure, unreliable) or server side testing (would work fine) where you test if all is set, and if so, ignore everything else.

    i'm not sure how your database stores requests that have multiple answers.  I personally, since there is a very limited amount of responses, would set up a single character value to store say blue and black as 5 so you can store it as char(1) in your database.

    also, you have to make sure you're using different names for your checkboxes or else the server side script will only see the last value to be set.  you could loop through the variables using a foreach loop or manually set them.

    You could also chose to write out all the choices and just use a radio button.  then only one choice could be selected at a time and you would always know the value and not need to do much strange checking, etc.
    LVL 32

    Accepted Solution

    Use IN operator in the query to select the "eyecolor" column. Here's an example:

    // DB connection.
    $eyecolor_arr = array(1=>'Black', 2=>'Blue', 3=>'Brown');
    $res = mysql_query("SELECT * FROM table_name") or die("Sql error: " . mysql_error());
    while($row = mysql_fetch_array($res)) {
     $eyecolor_dbarr[] = $row['eyecolor'];
    $eyecolor_dbarr = array_unique($eyecolor_dbarr);
    function selectAll(cState) {
     var elem = document.getElementsByName("myeyecolor[]");
     for(i=0; i<elem.length; i++) {
      elem[i].checked = (cState == true) ? true : false;
    <form action="submit_page.php" method="POST">
    <input type="checkbox" id="selectall_eyecolor" onclick="selectAll(this.checked);"/>All <br/>
    foreach($eyecolor_dbarr as $value) {
        echo "<input type='checkbox' name='myeyecolor[]' value='$value'/> {$eyecolor_arr[$value]} <br/>";
    <input type="submit" name="submit_form" value="Submit"/>

    and in the submit_page.php:

    if(isset($_POST["submit_form"]) && !empty($_POST["submit_form"]))
        if(array_key_exists('myeyecolor', $_POST) && is_array($_POST['myeyecolor'])) {
            $where_query = implode(',', array_values($_POST['myeyecolor']));
            $query = "SELECT * FROM table_name WHERE eyecolor IN ($where_query)";
            // your stuff ...

    Author Comment

    I apologize for not noting this in my original message, but each record has only one eye color listed.

    The purpose of the checkboxes is to allow people to return records having any of the selected eye colors, or if they select "all" - to not apply the eye color filter at all.

    LVL 32

    Expert Comment

    >> The purpose of the checkboxes is to allow people to return records having any of the selected eye colors, or if they select "all" - to not apply the eye color filter at all.

    Thats what my code does, Have you tried it ?
    LVL 7

    Assisted Solution

    Hi there

    Assuming the following database stucture,

    eyecolor ->


    A simple query would look like this

      missingpersons.height as eyecolor  
    FROM missingpersons
    LEFT JOIN eyecolors ON
      creationdate DESC

    If you have a specified eyecolor that you want, you need to add a where clause like etc or alternatively'blue' if you prefer (make sure you index your columns properly)

    Do your checkboxes like this (the [] tells PHP to treat the returned values as an array):
    <input type="checkbox" name="eyecolors[]" value="-1"> all
    <input type="checkbox" name="eyecolors[]" value="4"> blue
    <input type="checkbox" name="eyecolors[]" value="5"> black

    Then you can do this on at the server side with
    // all search 'where' terms will go here:

    // get the list of eyecolor choices:
    else $eyecolor=array();

    // clear out list if 'all' was selected:

    // create a 'WHERE' clause:
    foreach($eyecolors as $ec){
    $where[]="(". join(" OR ",$eyewhere).")";

    $sql="SELECT ... as above ... WHERE ".join(" AND ",$where)." ORDER BY creationdate DESC";


    Hope that helps. This isn't that much different from ldbkutty's suggestion except I'd certainly suggest you keep your eyecolors in a separate table in the database and use a left join to connect with these.

    One thing to think about is the possibility that not all records will have an eyecolor. In that case, you want records with unknown eye color probably not to be eliminated when the searcher has specified a specific eyecolor. To do that, you'd just add $eyewhere[]=" IS NULL"; before the $where[]=... line above.




    Author Comment

    ldbkutty and jdpipe,

    Thank you for providing such helpful suggestions.

    I am going to work with this now and will post back soon.



    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
    Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now