• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

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,

Eric

0
epaschal
Asked:
epaschal
2 Solutions
 
quad341Commented:
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.
0
 
ldbkuttyCommented:
Use IN operator in the query to select the "eyecolor" column. Here's an example:

<?php
// 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);
?>
<html>
<head>
<script>
function selectAll(cState) {
 var elem = document.getElementsByName("myeyecolor[]");
 for(i=0; i<elem.length; i++) {
  elem[i].checked = (cState == true) ? true : false;
 }
}
</script>
<body>
<form action="submit_page.php" method="POST">
<input type="checkbox" id="selectall_eyecolor" onclick="selectAll(this.checked);"/>All <br/>
<?php
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"/>
</form>

and in the submit_page.php:
==================

<?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 ...
    }
}
?>
0
 
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.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ldbkuttyCommented:
>> 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 ?
0
 
jdpipeCommented:
Hi there

Assuming the following database stucture,

missingpersons
==========
id
name
eyecolor -> eyecolors.id
height
creationdate

eyecolors
==========
id
name


A simple query would look like this

--------------8<---------
SELECT
  missingpersons.id
  missingpersons.name
  missingpersons.height
  eyecolors.name as eyecolor  
FROM missingpersons
LEFT JOIN eyecolors ON missingpersons.eyecolor=eyecolors.id
WHERE
  ...
ORDER BY
  creationdate DESC
--------------8<----------------

If you have a specified eyecolor that you want, you need to add a where clause like eyecolors.id=4 etc or alternatively eyecolors.name='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
-------------8<-----------
// all search 'where' terms will go here:
$where=array();

// get the list of eyecolor choices:
if(isset($_GET['eyecolor']))$eyecolors=$_GET['eyecolor'];
else $eyecolor=array();

// clear out list if 'all' was selected:
if(in_array(-1,$eyecolors))$eyecolors=array();

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

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

mysqlquery(...)
------------8<-------------

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[]="eyecolors.id IS NULL"; before the $where[]=... line above.

JP


 





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

Eric

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now