create multiple drop down menus to filter mysql query?

[nickpalmigiano]

Hello all,
I've been using webdeveloper.com for a while now but I never needed to ask my own question since I usually found it in search, but this time its different

Here is my problem,

I am currently building a page that uses five separate drop down menus (static content) that when selected the combination of what is selected will filter my mysql query and display the correct results using those parameters.

I have everything set up to work when one or another variable is sent, however I am having trouble creating a way to set up the query string where non of the filter options are required.

here is the code that I have so far.

This is my menu code, this will always be static.

<form name="campsite_search" action="page.html" method="get">
    <select name="state" class="input">
        <option value="all">Select a State</option>
        <option value="MD">Maryland</option>
        <option value="VA">Virginia</option>
        <option value="WV">West Virginia</option>                                                    
        <option value="PA">Pennsylvania</option>
        <option value="DC">Washington D.C.</option>
    </select>
    <select name="radius" class="input">
    	<option value="all">Radius from Council</option>
        <option value="25">25 miles</option>
        <option value="50">50 miles</option>
        <option value="100">100 miles</option>
        <option value="250">250 miles</option>
        <option value="500">500 miles</option>    
    </select>
    <select name="size" class="input">
        <option value="all">Please select Camp Size</option>
        <option value="25">25 Campers</option>
        <option value="50">50 Campers</option>
        <option value="100">100 Campers</option>
        <option value="250">250 Campers</option>
        <option value="500">500 Campers</option>
    </select>
    <select name="type" class="input">
	    <option value="all">Structure type</option>
	    <option value="tent">Tents</option>
	    <option value="cabin">Cabins</option>    
    </select>
    <select name="activity" class="input">
    	<option value="all">High Adventure Activities</option>
        <option value="0">No</option>
        <option value="1">Yes</option>
    </select>
<input type="submit" name="submit" class="input" value="Continue" />
</form>

and here is my php code that deals with the query, it is far from finished, not even using all the options in the drop down. I started trying to build a big if/elseif tree but that isn't working out. I am also using the smarty and pear libraries, so I apologize if it looks non-standard.

              if(isset($_GET['state'])){$state = addslashes($_GET['state']);}
              $radius = addslashes($_GET['radius']);
              $size = addslashes($_GET['size']);
              
              //dealing with the single filter options
              if(($state = 'all')&&($radius = 'all')&&($size = 'all')){
                 $filter ='';
              
              }elseif(($state != 'all')&&($radius = 'all')&&($size = 'all')){                  
                $filter = "state ='".$state."' ";
              
              }elseif(($state = 'all')&&($radius != 'all')&&($size = 'all')){                  
                  $filter = "radius <='".$radius."' ";              
              
              }elseif(($state = 'all')&&($radius = 'all')&&($size != 'all')){
                $filter = "camp_size <='".$size."' ";
                $order = "camp_size ASC";
                
              }

            print_r($filter);
            $rs = $a->select($view,$filter,$order,$offset,$limit); 

what I need help with is a way to craft the filtering string so that the options are not required, a catch all way to select any combination of filters and for the query to work.

Any help would be appreciated, and Thank you very much for your time.

[bokeh]

I don't really know what you are trying to do, the question isn't too clear. Maybe something like this:

$filter ='';
 
if($state !== 'all')
{
    $filter .= ", state ='".$state."' ";
}

if($radius !== 'all')
{
    $filter .= ", radius ='".$radius."' ";
}

if($size !== 'all')
{
    $filter .= ", camp_size ='".$size."' ";
} 

elseif(($state = 'all') 

That will always be true. You are setting a variable, not checking for equality.

[nickpalmigiano]

http://www.webdeveloper.com/forum/sh...ad.php?t=95024

This thread and post number four explain it a little better than I did. I am trying to write the tree so that any combination of selections in the drop down will result in a successful query. ie. state only or state, size and activity, or radius and activity.

is that any better?

I don't really know what you are trying to do, the question isn't too clear. Maybe something like this:

$filter ='';
 
if($state !== 'all')
{
    $filter .= ", state ='".$state."' ";
}

if($radius !== 'all')
{
    $filter .= ", radius ='".$radius."' ";
}

if($size !== 'all')
{
    $filter .= ", camp_size ='".$size."' ";
} 

That will always be true. You are setting a variable, not checking for equality.

AHH! I feel silly, thank you! that did fix the tree for all the single drop down selections. Like, if someone only picks state or radius and leaves the rest alone. But what about the multiple selections?

[bokeh]

It would work with multiples because it concatenates.

[nickpalmigiano]

            $state = $db->escapeSimple($_GET['state']);
            $radius = $db->escapeSimple($_GET['radius']);
            $size = $db->escapeSimple($_GET['size']);
            $type = $db->escapeSimple($_GET['type']);
            $activity = $db->escapeSimple($_GET['activity']);
            
            $filter = "";
            
            if($state !== 'all'){                  
              $filter .= "state ='".$state."'";
              $order = "state ASC";
            }
            
            if($radius !== 'all'){                  
              if(isset($state)&&($state !== 'all')){
                  $filter .= " AND ";
              }
              $filter .= "radius <='".$radius."'";
              $order = "radius ASC";
            }
            
            if($size !== 'all'){
              if(isset($radius)&&($radius !== 'all')||isset($state)&&($state !== 'all')){
                  $filter .= " AND ";
              }                  
              $filter .= "camp_size <='".$size."'";
              $order = "camp_size ASC";
            }
            
            if($type !== 'all'){
              if(isset($radius)&&($radius !== 'all')||isset($state)&&($state !== 'all')||isset($size)&&($size !== 'all')){
                  $filter .= " AND ";
              }                  
              $filter .= "type LIKE '%".$type."%'";
              $order = "type ASC";
            }
            
            if($activity !== 'all'){
              if(isset($radius)&&($radius !== 'all')||isset($state)&&($state !== 'all')||isset($size)&&($size !== 'all')||isset($type)&&($type !== 'all')){
                  $filter .= " AND ";
              }                      
              $filter .= "canoe ='".$activity."'";
              $order = "lastmod ASC";
            }

            //print_r($filter);
            $rs = $a->select($view,$filter,$order,$offset,$limit); 

I was able to get it all to work with some tweaking and the direction you pointed me in. Thank you very much for all your help. Might be a little overkill but it works haha just have to work on optimizing my code.

[ychan623]

Hello, sorry to reply for and old thread. But I been google around and this is the same thing that i needed help with. I have 2 drop down menu and a submit button. I try to us nickpalmigiano's if condition to filter result, but where do I apply the $filter and $order variables on the Mysql_query. Does this code look right...

$sql = mysql_query("SELECT * FROM mytable WHERE Category='".$filter."' && SubCat='".$filter."' ORDER BY $order");

--below is my 2 drop down menu populated using javascript--
<FORM name="drop_list" action="joboutput2.php" method="POST" >
<label>
<SELECT NAME="Category" size="1" id="Category" onChange="SelectSubCat();" >
<Option value="All">所有</option>
</SELECT>&nbsp;
<SELECT NAME="SubCat" id="SubCat" >
<Option value="All">所有</option>
</SELECT>
<input type="Submit" name="Form3" value="搜索"/>
</label>
</form></div>

[insidioustrance]

you can't use same variable for both category and sub-category as i think, both will be having different values..in this case, you just need to use following code:
$category=$_POST['Category'];
$subcategory=$_POST['SubCat'];
$sql = mysql_query("SELECT * FROM mytable WHERE Category='$category' and SubCat='$subcategory' ");

[kumar73]

hi there. I created HTML and PHP file to filter web form data based on multiple drop down filters. Here goes the form. when i run the form and PHP, i don't see any results in the browser. There is no error either. I am working on a example posted by another forum member.Pls. help. Thanks in advance.

<form action="showJobs_new.php" method="post">
<select name="Locations">
<option value="" selected="selected">All Locations</option>
<option value="arizona">Arizona</option>
<option value="alaska">Alaska</option>
</select>
<select name="Jobs">
<option value="" selected="selected">All jobs</option>
<option value="Carpenter">Carpenters</option>
<option value="Plumbers">Plumbers</option>
</select>
<input type="submit" value="search jobs" />
</form>

showJobs_new.php:

<html>
<body>

<?php

$username="root";
$password="password";
$database="test";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$whereClauses = array();
if (! empty($_POST['Locations'])) $whereClauses[] ="Locations='".mysql_real_escape_string($_POST['Locations'])."'";
if (! empty($_POST['Jobs'])) $whereClauses[] ="Jobs='".mysql_real_escape_string($_POST['Jobs'])."'";
$where = '';
if (count($whereClauses) > 0) { $where = ' WHERE '.implode(' AND ',$whereClauses); }

$sql = mysql_query("SELECT * FROM mytable ORDER BY id DESC $limit" .$where);

$result=mysql_query($sql);

while ($row = mysql_fetch_assoc($result)) {
echo $row['Locations'];
echo $row['Jobs'];
}

?>
</body>
</html>