Php过滤系统

Question

I want to make a php filter system like this with 4 variables:

echo"<form action='' method='GET' class='form-inline' role='form'>";
$query = "SELECT Naam FROM Soortmaaltijd"; //Alle soortmaaltijden ophalen
$result= mysql_query($query) or die(mysql_error());
echo"<div class='row'>";
    echo"<div class='form-group' >";
    echo"<label for='soortmaaltijd'>Soort maaltijd</label></br>";
    echo"<select name='Soortmaaltijd' class='form-control' id='soortmaaltijd'>";
        echo"<option value=''>Alle</option>";
    while($row=mysql_fetch_array($result)) {        
        echo"<option value='$row[SoortmaaltijdID]'>$row[Naam]</option>";
    }
    echo"</select>";
    echo"</div>";

    $query = "SELECT * FROM Soortgerecht"; //Alle soortgerechten ophalen
    $result= mysql_query($query) or die(mysql_error());
    echo"<div class='form-group' >";
    echo"<label for='soortgerecht'>Soort gerecht</label></br>";
    echo"<select name='soortgerecht' class='form-control' id='soortgerecht'>";
        echo"<option value=''>Alle</option>";
    while($row=mysql_fetch_array($result)) {        
        echo"<option value='$row[SoortgerechtID]'>$row[Naam]</option>";
    }
    echo"</select>";
    echo"</div>";

    echo"<div class='form-group' >";
    echo"<label for='moeilijkheid'>Moeilijkheid</label></br>";//Moeilijkheid
    echo"<select name='moeilijkheid' class='form-control' id='moeilijkheid'>";
        echo"<option value=''>Alle</option>";       
        echo"<option value='1'>1</option>";
        echo"<option value='2'>2</option>";
        echo"<option value='3'>3</option>";
    echo"</select>";
    echo"</div>";

    echo"<div class='form-group' >";
    echo"<label for='tijd'>Max bereidingstijd</label></br>";//Max bereidingstijd
    echo"<select name='tijd' class='form-control' id='tijd'>";
        echo"<option value=''>Alle</option>";       
        echo"<option value='5'><5</option>";
        echo"<option value='10'><10</option>";
        echo"<option value='15'><15</option>";
        echo"<option value='20'><20</option>";
        echo"<option value='25'><25</option>";
        echo"<option value='30'><30</option>";
    echo"</select>";
    echo" <button type='submit' name='filter' class='btn btn-primary btn-lg-2'>Filter</button>";
    echo"</div>";
echo"</div>";
echo"</form>"; ?>

But how can I contruct a query that uses all the variables even when some filter settings aren't changed. It is possible to create 20 queries but that costs too much time. Is it possible to create something like this:

WHERE Tijd = $tijd AND Soortmaaltijd = $soortmaaltijd AND Soortgerecht = $soortmaaltijd AND Moeilijkheid = $moeilijkheid

But if some value is not set in the filter like 'Tijd', 'Tijd' has a standard value?

Answer 1

You probably just need to construct your full WHERE in advance so that instead of sending 4 vars to your query you send a single WHERE statement.

Example 1 (constructing a $where var which concatenates your conditions):

$where = "WHERE ";
$count = 0;
if ( !empty($tijd) ) {

    $where .= "`Tijd` = " . $tijd . " ";
    ++$count;

} elseif( !empty($soortmaaltijd) ) {

    if ($count == 0)
        $where .= "`Soortmaaltijd` = " . $soortmaaltijd . " ";
    else
        $where .= "AND `Soortmaaltijd` = " . $soortmaaltijd . " ";

    ++$count;

} elseif( !empty($soortgerecht) ) {

    if ($count == 0)
        $where .= "`Soortgerecht` = " . $soortgerecht . " ";
    else
        $where .= "AND `Soortgerecht` = " . $soortgerecht . " ";

    ++$count;

} elseif( !empty($moeilijkheid) ) {

    if ($count == 0)
        $where .= "`Moeilijkheid` = " . $moeilijkheid . " ";
    else
        $where .= "AND `Moeilijkheid` = " . $moeilijkheid . " ";

    ++$count;

} else {
    $where = null; // if none of the conditions are met we null 
                   // the entire `WHERE` statement so we can safely
                   // send to our SQL query regardless of no conditions
                   // being met
}

// then your sql statement could be something like:
$sql = "SELECT *
        FROM tablename
        $where";  // remember: if ($where == null)
                  // that means no filters are set and
                  // all records from table are returned

This example assumes that you want to handle your empty vars as null queries. It's a bit unclear in your question whether you want to skip querying for null values or if you want to set your null vars to default values. As mentioned in comments, setting vars to toggle on default values is really easy using a ternary operator.

Example 2 (setting a default value on empty vars):

$default_tijd = "whatever you want";
$tijd = ($tijd) ? $tijd : $default_tijd; // if $tijd is already set take
                                         // that value, else use $default_tijd

However, I'm almost positive you don't want to return records for filters which aren't set to anything so setting a default value to your vars is just going filter records when you don't want to. What you probably want is the first example.