Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Jun 2008
    Posts
    30
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Help with filter script

    Hello, I want to make a filter script of 3 options. I have 3 choices that a user can select (name, age, country) and I want to make a query that will do it. But I can't figure out how to make it if for example age is not selected, then it should only search for that name and country or if all 3 are selected it will search for all three....

    For example if my options are like this....

    Name: John
    Age: Not Selected
    Country: USA

    How will I write my Query?
    Select * from table WHERE name = $name AND age = ???? and Country = $country
    Last edited by QueenZ; 06-23-2013 at 08:37 PM.

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,026
    Thanks
    23
    Thanked 588 Times in 587 Posts
    you have 7 options. 3 if only one option is picked, 3 if two options are picked, and 1 if all are picked. You will need 7 queries and put them in a PHP: switch.

    Do you need code?
    Evolution - The non-random survival of random variants.

  • #3
    New Coder
    Join Date
    Jun 2008
    Posts
    30
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sunfighter View Post
    you have 7 options. 3 if only one option is picked, 3 if two options are picked, and 1 if all are picked. You will need 7 queries and put them in a PHP: switch.

    Do you need code?
    Hmm, I was thinking I would need a lot of queries too but I was Googling a little bit and found that one guy built a query like this...

    http://stackoverflow.com/questions/6...-a-mysql-query

    A code example would be great

  • #4
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,026
    Thanks
    23
    Thanked 588 Times in 587 Posts
    The form:
    Code:
    <!DOCTYPE html>
    <html>
    <head>
    </head>
    <body>
    <form id="myform" method="post" action="test.php">
    	<table>
    		<tr><td>NAME</td><td><input type="text" name="name"></td></tr>
    		<tr><td>AGE</td><td><input type="text" name="age"></td></tr>
    		<tr><td>COUNTRY</td><td><input type="text" name="country"></td></tr>
    		<tr><td colspan="2"><input type="submit" value="submit"></td></tr>
    	</table>
    </form>
    </body>
    </html>
    Calls the php as test.php:
    PHP Code:
    <?php
    if($_POST['name'] <> ''$name $_POST['name'];
    if(
    $_POST['age']<> ''$age $_POST['age'];
    if(
    $_POST['country']<> ''$country $_POST['country'];

    if(isset(
    $name)){$a 1;}else{$a 0;}
    if(isset(
    $age)){$b 2;}else{$b 0;}
    if(isset(
    $country)){$c 4;}else{$c 0;}
    $i $a $b $c;

    switch(
    $i) {
        case 
    0:
            
    $query "Nothing Selected, Where is the javascript validation?";
            break;
        case 
    1:
             
    $query "Select * from table WHERE name = $name";
             break;
        case 
    2:
            
    $query "Select * from table WHERE age = $age";
            break;
        case 
    3:
            
    $query "Select * from table WHERE name = $name and age = $age";
            break;
        case 
    4:
            
    $query "Select * from table WHERE country = $country";
            break;
        case 
    5:
            
    $query "Select * from table WHERE name = $name and country = $country";;
            break;
        case 
    6:
            
    $query "Select * from table WHERE age = $age and country = $country";
            break;
        case 
    7:
            
    $query "Select * from table WHERE name = $name and age = $age and country = $country";
            break;
    }
    echo 
    $query;   // FOR CHECKING -- DO THE QUERY HERE IF YOU LIKE
    ?>
    Evolution - The non-random survival of random variants.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,021
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    That is truly ugly code Sunfighter.

    And, on top of that, it doesn't scale at all if, for example, there were *TEN* possible fields involved.

    I completely disagree with that approach.

    Instead:
    Code:
    <?php
    
    $sql = "SELECT * FROM table WHERE 1=1 ";
    
    $name = $_POST["name"];
    if ( $name != "" ) $sql .= " AND name = '" . mysql_real_escape_string(name) . "'";
    
    $age = $_POST["age"];
    if ( $age != "" ) $sql .= " AND age = " . ( (int) $age );
    
    $country = $_POST["country"];
    if ( $country != "" ) $sql .= " AND country = '" . mysql_real_escape_string(country) . "'";
    
    ...
    With a tiny bit more thought, you coulde extend this to allow you to easily handle any number of form and database fields.

    For example:
    Code:
    $textfields = array( "firstname", "lastname", "city", "state", "country" );
    $numfields = array( "age", "salary", "children" );
    
    $sql = "SELECT * FROM table WHERE 1=1";
    
    foreach ( $textfields as $f ) 
    {
        $value = $_POST[$f];
        if ( $value != "" ) $sql .= " AND $f = '" . mysql_real_escape_string($value) . "' ";
    }
    foreach ( $numfields as $f ) 
    {
        $value = $_POST[$f];
        if ( $value != "" ) $sql .= " AND $f = " . ( (double) $value );
    }
    ...
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,021
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Oh, and by the way, Sunfighter omitted the required '...' around text values and didn't include any SQL injection protection.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,026
    Thanks
    23
    Thanked 588 Times in 587 Posts
    Quote Originally Posted by Old Pedant View Post
    Oh, and by the way, Sunfighter omitted the required '...' around text values and didn't include any SQL injection protection.
    Yeah I know. I always do that and catch it when I run the query. And I did remember it last night and would have changed this morning, but now there's no reason to.
    Evolution - The non-random survival of random variants.


  •  

    Posting Permissions

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