www.webdeveloper.com
Results 1 to 9 of 9

Thread: Search with multiple drop down boxes

  1. #1
    Join Date
    Mar 2012
    Posts
    20

    Red face Search with multiple drop down boxes

    Hi
    I have a search pege in my website.I am developing in PHP/MYSQL.
    search page contains three drop down boxes
    1:Country
    2:Education
    3:age

    Country contains:All,US,Canada etc
    Education Contains:All,Highschool etc
    Age Contains:All,20,21,etc

    How can i write sql statement to satisfy this criteria to get values from database.

    Thank you so much for your help

  2. #2
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    This is really a question for the SQL forum, but..
    Code:
    "SELECT col1, col2, col3, col4, .... col20, col21, col22
    FROM `tableA`
    WHERE col5 = '".$_POST['country']."'
       AND col7 = '".$_POST['education']."'
       AND col9 = ".$_POST['age']." "
    This may not be quite correct - it's been a while since I worked with MySQL.

  3. #3
    Join Date
    Apr 2010
    Posts
    227
    Just a tip, age should also not be hard stored. Should store their birthday and use PHP to calculate their age.

    Oh and I use a search engine but my process is different to yours. I collect all the SQL rows and store in array.

    I then use strpos() to search and display.

    Oh and you shoudl still sanitize POSTs.

    Oh and is the coutnry name education going to be stored within a string?

    If so you will need to use % with your WHERE clause.

  4. #4
    Join Date
    Mar 2012
    Posts
    20
    Hi
    Thanks for your reply,but this query is only if they select all the drop down boxes,what happens if they do not want to select county option,we will get empty rows

    any other ideas

  5. #5
    Join Date
    Apr 2010
    Posts
    227
    PHP Code:
    $search_clause ="";
    $first_clause TRUE;
    if(isset(
    $_POST['country'])){

    if(
    $first_clause){
    $x "WHERE";
    }
    else{
    $x "AND";
    }
    $first_clause FALSE;
    $search_clause .= $x country ='".$_POST['country']."' ";
    }

    //etc etc for all your search possibilities

    $query "SELECT * FROM `yourtable` $search_clause"

    Note if you are submitting a search one drop down select at a time you will need to store past POSTs into a SESSION.
    Last edited by Belrick; 04-12-2012 at 12:13 AM.

  6. #6
    Join Date
    Mar 2012
    Posts
    20
    Hi
    Thank you so much for your reply
    actually i have "ANY" option in all drop down boxes,so if they choose "ANY" they should get all the results from database,suppose they choose "ANY" option for country not for remaning onece,how can i write query in this situcation

  7. #7
    Join Date
    Apr 2010
    Posts
    227
    Instead of checking to see if you receive a country POST with ISSET do a check and see if country POST == "any" else add a country search clause.

    ie:

    PHP Code:
    if($_POST['country'] != "ANY"){ 

    if(
    $first_clause){ 
    $x "WHERE"

    else{ 
    $x "AND"

    $first_clause FALSE
    $search_clause .= $x country ='".$_POST['country']."' "

    This simply assumes that youll always recieve a country POST and that if its not containing ANY then its a country that restricts your SQL select statement.

  8. #8
    Join Date
    Apr 2010
    Posts
    227
    Hmmm

    Altogether

    PHP Code:
    $search_array = array("Country","Education","Age");
    $search_clause =""
    $first_clause TRUE


    foreach(
    $search_array as $search){
    if(
    $_POST[$search] != "ANY"){ 

    if(
    $first_clause){ 
    $x "WHERE"

    else{ 
    $x "AND"

    $first_clause FALSE
    $search_clause .= $x $search ='".$_POST[$search]."' "


    Echo / print out $search_clause to see if it creates a proper SQL syntax. If so your good to use it! (of course there is no sanitization being done here though and nor does it handle the smarts around storing birthdays rather than arbitary ages)
    Last edited by Belrick; 04-12-2012 at 05:38 PM.

  9. #9
    Join Date
    Mar 2012
    Posts
    20
    Hi
    Thank you so much, i solved my problem

Thread Information

Users Browsing this Thread

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

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