querying using two drop boxes in PHP

I have two drop down boxes using the code below:

<form method="post" action="">      
            <select name='defect' value=''>
                <option>Rotten</option>
                <option>Split</option>
                <option>WheelCut</option>
                <option>Broken</option>
                <option>Quality</option>
            </select>开发者_如何学Go;
                      is equal to:
            <select name='quality' value=''></option>
                <option>1</option>
                <option>2</option>
                <option>3</option>
                <option>4</option>
            </select>
        <input type="submit" name="query" value="Submit">
    </form>

I am trying to make it so that after clicking on the submit button it changes the following query...

SELECT   id, mile, yard, gps_lat, gps_long, rotten, split, wheel_cut, broken, quality
FROM database WHERE gps_lat IS NOT NULL AND ????? (e.g. broken=2) ORDER BY mile, yard

• how do I combine the two user inputs (e.g. broken value=2)
• Do I need to use a switch statement to change the query for each possibly?

Any advice would be greatly appreciated!

Cheers,

Neil

---

Something like this?

<?php
$sql = 'SELECT id, mile, yard, gps_lat, gps_long, rotten, split, wheel_cut, broken, quality
FROM database WHERE gps_lat IS NOT NULL';

switch($_POST['defect'])
{
        case 'Rotten': $defect = 'rotten'; break;
        case 'Split': $defect = 'split'; break;
        case 'WheelCut': $defect = 'wheel_cut'; break;
        case 'Broken': $defect = 'broken'; break;
        case 'Quality': $defect = 'quality'; break;
        default: $defect = ''; break;
}

switch($_POST['quality'])
{
        case 1: $quality = 1; break;
        case 2: $quality = 2; break;
        case 3: $quality = 3; break;
        case 4: $quality = 4; break;
        default: $quality = 0; break;
}

if($defect != '' && $quality != 0)
{
    $sql .= ' AND '.$defect.' = '.$quality;
}

$sql .= ' ORDER BY mile, yard';
?>

Edited to make a bit better

---

To prevent hardcoded logic, you can move it to server side. Good solution is to use dynamic SQL to build filter query. You can write stored procedure with XML which will contain column name and filtered value. Here is your example:

    DECLARE @xml XML= '<root><select name="defect" value="">
                <option>Rotten</option>
                <option>Split</option>
                <option>WheelCut</option>
                <option>Broken</option>
                <option>Quality</option>
            </select>
            <select name="quality" value="" type="int">
                <option>2</option>
            </select></root>'
DECLARE @sSQL NVARCHAR(MAX)   = 'SELECT   id, mile, yard, gps_lat, gps_long, rotten, split, wheel_cut, broken, quality
FROM database WHERE gps_lat IS NOT NULL' ;
WITH    c AS ( SELECT   a.b.value('../@name[1]', 'nvarchar(128)') ColumnName ,
                        a.b.value('.', 'nvarchar(128)') VALUE ,
                        a.b.value('../@type[1]', 'nvarchar(128)') [Type]
               FROM     @xml.nodes('root/select/*') a ( b )
             )
    SELECT  @sSQl = @sSQL + ' AND ' + c.ColumnName + '='
            + CASE WHEN ISNULL(c.TYPE, '') != 'int' THEN CHAR(39)
                   ELSE ''
              END + c.VALUE
            + CASE WHEN ISNULL(c.TYPE, '') != 'int' THEN CHAR(39)
                   ELSE ''
              END
    FROM    c
SET @sSQL = @sSQL + ' ORDER BY mile, yard'
SELECT  @sSQL

Result will be:

SELECT   id, mile, yard, gps_lat, gps_long, rotten, split, wheel_cut, broken, quality  FROM database WHERE gps_lat IS NOT NULL AND defect='Rotten' AND defect='Split' AND defect='WheelCut' AND defect='Broken' AND defect='Quality' AND quality=2 ORDER BY mile, yard

Note that you can build filters where user can check several checkboxes.(In example user checks all checkboxes in "defect"). In that way you can prepare your application for future enhancement.