Translating Checkboxes to MySQL / PHP Code

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,


Who is Participating?
ldbkuttyConnect With a Mentor Commented:
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 ...
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.
epaschalAuthor Commented:
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.

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

>> 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 ?
jdpipeConnect With a Mentor Commented:
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.



epaschalAuthor Commented:
ldbkutty and jdpipe,

Thank you for providing such helpful suggestions.

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


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.