www.webdeveloper.com
Results 1 to 8 of 8

Thread: create multiple drop down menus to filter mysql query?

Hybrid View

  1. #1
    Join Date
    Jun 2009
    Posts
    5

    create multiple drop down menus to filter mysql query?

    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.
    HTML Code:
    <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.

    PHP Code:
                  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.

  2. #2
    Join Date
    Jan 2005
    Location
    Alicante (Spain)
    Posts
    7,742
    I don't really know what you are trying to do, the question isn't too clear. Maybe something like this:
    PHP Code:
    $filter ='';
     
    if(
    $state !== 'all')
    {
        
    $filter .= ", state ='".$state."' ";
    }

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

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

    Quote Originally Posted by nickpalmigiano View Post
    PHP Code:
    elseif(($state 'all'
    That will always be true. You are setting a variable, not checking for equality.

  3. #3
    Join Date
    Jun 2009
    Posts
    5
    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?

    Quote Originally Posted by bokeh View Post
    I don't really know what you are trying to do, the question isn't too clear. Maybe something like this:
    PHP Code:
    $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?

  4. #4
    Join Date
    Jan 2005
    Location
    Alicante (Spain)
    Posts
    7,742

  5. #5
    Join Date
    Jun 2009
    Posts
    5
    PHP Code:
                $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.

  6. #6
    Join Date
    Apr 2010
    Posts
    13

    query help

    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>
    Last edited by ychan623; 03-10-2012 at 12:36 AM.

  7. #7
    Join Date
    Feb 2012
    Location
    chennai
    Posts
    4
    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' ");

  8. #8
    Join Date
    Oct 2012
    Posts
    2
    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>

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles