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 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Oct 2012
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make SQL ignore a specific search value if clicked in a dropdown?

    I am creating a multi-form search box that searches a Meat packaging database. The search works only if users are specific, I want them to be able to search the database without having to choose a specific option from the dropdown box, limiting the number of searches.

    Is it possible to add an option into the dropdown boxes such as "Any" and have the SQL query ignore it if the user chooses it? So then it will only search for results that match those of the ones within the text forms?

    Here is the HTML and PHP:

    Code:
    <body>
    
      <form action="form3.php" method="post"> 
        <label for ="description">Description:</label>
        <input type="text" name="descrip" /><br />
    
        <label for ="trayheight">Trayheight:</label>
        <input type="text" name="height" /> <br /> 
    
        <label for ="traywidth">Traywidth:</label>
        <input type="text" name="width" /> <br />
    
        <label for ="traydepth">Traydepth:</label>
        <input type="text" name="depth" /> <br />
    
             <label for="trayrange">Trayrange: </label>
                    <select name="trayrange">
                        <option value="Other">Any</option>
    
                        <option value="BBQ">BBQ</option>
    
                        <option value="Dessert">Dessert</option>
    
                        <option value="Display">Display</option>
    
                        <option value="Meat">Meat</option>  
    
                        <option value="Microwave">Microwave</option>
    
                        <option value="Party">Party</option>
    
                        <option value="Salad/Wet Pasta">Salad/Wet Pasta</option>
    
                        <option value="Snacks">Snacks</option>
    
                        <option value="Standard">Standard</option>
    
                    </select>
    
            <label for ="traytype">Traytype: </label> 
                <select name="traytype">
                        <option value="Other">Any</option>
    
                <option value="Open">Open</option>
    
                <option value="Cavitised">Cavitised</option>
    
                        <option value="Lid">Lid</option>
    
                        <option value="Tray">Tray</option>
    
                        <option value="Coallition">Coallition</option>
    
                        <option value="Bowl">Bowl</option>
    
                        <option value="Hinge pack">Open</option>
    
                        <option value="Pot">Pot</option>
    
                        <option value="Base & Lid">Base and Lid</option>
    
                        <option value="Rectangular">Rectangular</option>
    
                        <option value="Specalist">Specialist</option>
                    </select><br />
    
            <label for="trayshape">Trayshape: </label>
                <select name="trayshape">
                        <option value="Other">Any</option>
    
                <option value="Rectangular">Rectangular</option>
    
                    <option value="Oval">Oval</option>
    
                    <option value="Square">Square</option>
    
                        <option value="Insert">Insert</option>
    
                        <option value="Round">Round</option>
    
                        <option value="Open">Open</option>
            </select><br />
            <input type="submit" value="Submit" /> 
        </form> 
    
            </body>
    PHP Code:
    <body>

        <?php
             $con 
    mysql_connect ("localhost""root""");
             
    mysql_select_db ("delyn_db"$con);

        if (!
    $con)
            { 
            die (
    "Could not connect: " mysql_error());
            }

                
    $descrip mysql_real_escape_string($_POST['descrip']); 
                
    $height mysql_real_escape_string($_POST['height']);
                
    $width mysql_real_escape_string($_POST['width']);
                
    $depth mysql_real_escape_string($_POST['depth']);

                
    $varRange $_POST['trayrange'];
                
    $varType $_POST['traytype'];
                
    $varShape $_POST['trayshape'];

        
    $sql "SELECT * FROM delyn WHERE description LIKE '%".$descrip."%' AND trayheight
                        LIKE '%"
    .$height."%'AND traywidth LIKE '%".$width."%' AND traydepth LIKE
                        '%"
    .$depth."%' AND trayrange LIKE '%".$varRange."%' AND 
                        traytype LIKE '%"
    .$varType."%' AND trayshape LIKE '%".$varShape."%' ";


        
    $r_query mysql_query($sql);

        while (
    $row mysql_fetch_array($r_query))
                { 
                echo 
    '<br /> Tool Code:   '.$row['toolcode'];
                echo 
    '<br /> Description: '.$row['description']; 
                echo 
    '<br /> Tray range:  '.$row['trayrange']; 
                echo 
    '<br /> Tray type:   '.$row['traytype'];
                    echo 
    '<br /> Tray size:   '.$row['traysize']; 
                echo 
    '<br /> Tray shape:  '.$row['trayshape'] . '<br />' 
                                
    '<br />'; ;  
                        }

            if (
    mysql_num_rows($r_query) <= 0){
                echo 
    'No results match your search, please try again';
                   }
            
    ?>
          </body>
    Any ideas?
    Last edited by LiamHorizon; 10-09-2012 at 02:02 PM.

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,610
    Thanks
    24
    Thanked 647 Times in 646 Posts
    Sure one example only. If "any" is chosen for <select name="trayrange"> you can add this to the php file just before you do your query.

    if($varRange == 'any'){
    $sql= 'code that does not restrict the search'
    }else{
    $sql = 'your old code'
    }

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,211
    Thanks
    80
    Thanked 4,571 Times in 4,535 Posts
    An easier way: Since you are using LIKE '%xxx%' in all your tests, just use <option value="">Any</option> in the HTML.

    That will cause your query to become (for example)
    Code:
    SELECT * FROM delyn WHERE description LIKE '%%' AND ...
    and so of course it will match ANY description.
    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.


  •  

    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
    •