实时AJAX搜索同时具有多个输入PHP / MySQL

Question

I'm working on a live search in AJAX (for a database of powder horns) with two inputs, the first for the year, and the second for the conflict. The problem I'm having is when one of the inputs is blank, it only returns elements in the table that have no date or conflict name listed. Instead I want blank inputs to behave as null, and show everything from the table. I can fix the date section using a conditional to check if (!$date), but I can't get the select input to behave the same way if the user chooses blank again.

Any ideas on how to fix this? Especially in one query without a ton of PHP if/else?

HTML

<h1> Powder Horn Search Engine </h1>




Date <input id="date" type="text" name="variable">

To <select id="conflict">
        <option>  </option>
        <option value="French & Indian War">French & Indian War</option>
        <option value="Revolutionary War">Revolutionary War</option>
        <option value="War of 1812">War of 1812</option>    
    </select>

<div id="result">
</div>

Javascript

        var date="";
var conflict=""

    $(document).ready( function () {    
//Send date to PHP
$("#date").keyup(function(){
    date = $("#date").val();
    conflict=$("#conflict").val();

    $.ajax({
          type: "POST",
          data: {date: date, conflict:conflict},
          url: "powderhornsearch.php",
            success: function(data){ //response param
            $("#result").html(data);
            }

        });
});

//Send conflict to PHP
$("#conflict").change(function(){
conflict=$("#conflict").val();
date = $("#date").val();

$.ajax({
          type: "POST",
          data: {conflict: conflict, date:date},
          url: "powderhornsearch.php",
            success: function(data){ //response param
            $("#result").html(data);
            }

        });


}); 
    });

PHP

    $date = $_POST['date'];
$conflict=$_POST['conflict'];
    $result = mysql_query ("SELECT * FROM powderhorns WHERE Date LIKE $date AND Conflict LIKE '$conflict'  ", $connection);
    if (!$result) {
            die("Database query failed:" . mysql_error());
        }
    echo "<table>";
    while ($row = mysql_fetch_array($result)) {
    echo "<tr>";
        echo "<td>".$row[0]."</td>".
            "<td>".$row[1]."</td>".
            "<td>".$row[2]."</td>".
            "<td>".$row[3]."</td>".
            "<td>".$row[4]."</td>";     
    echo "</tr>";   
    }
    echo "</table>";

EDIT

I changed the value of the blank select to "1" because it seemed like MySQL was having trouble with the empty string. I got everything to work by using the following code. As I am new to programming, if anyone has a suggestion on how to make it more condensed or elegant, I'd love your ideas. Thanks so much!

    if (!$date) {
$date="";
}

if ($conflict=="1" && !$date)
{
$result = mysql_query ("SELECT * FROM powderhorns", $connection);
}

else if ($conflict==1){
    $result = mysql_query ("SELECT * FROM powderhorns WHERE Date LIKE $date  ", $connection);
}

else if (!$date && $conflict){
    $result = mysql_query ("SELECT * FROM powderhorns WHERE Conflict LIKE '$conflict'  ", $connection);
}

else{
    $result = mysql_query ("SELECT * FROM powderhorns WHERE Conflict LIKE '$conflict' and Date LIKE $date  ", $connection);
}   

Answer 1

change it to:

$condition=1;
if(trim($date)) $condition .=" and Date LIKE '$date'";
if(trim($conflict)) $condition .="  and Conflict LIKE '$conflict'";

$result = mysql_query ("SELECT * FROM powderhorns WHERE $condition ", $connection);