可选查询字段和所需查询字段的PHP差异

Question

I have a php search query to run a query on my database. It works great, but I haven't been able to get the fields optional. In order for the search to run (as of now) all fields have to be filled in. I have tried the suggestions from Constructing an SQL query around optional search parameters without having too much luck. The page still loaded, but I received Boolean errors I haven't been able to debug. I have also tried the suggestions from mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given to fix the boolean errors I was receiving.

Here is my original code that is working with the exception of the fields being optional.

The PHP query-

<?php
//The File for the Database Connection
include('login/con.php');

//The SQL Query
$table = "SELECT * FROM milelog ";
if ($_SERVER['REQUEST_METHOD'] == 'POST'){

$input = mysqli_escape_string($dbconn, $_POST['location']);
$carin = mysqli_escape_string($dbconn, $_POST['Carin']);
$sdate = mysqli_escape_string($dbconn, $_POST['SDate']);
$edate = mysqli_escape_string($dbconn, $_POST['EDate']);
$miles = mysqli_escape_string($dbconn, $_POST['Miles']);
if(isset($carin)){
$table .= "WHERE Car = '$carin'";

if (isset($input))
$table .= "AND Location LIKE '%$input%'";

if (isset($sdate))
$table .= "AND Date BETWEEN '$sdate' AND '$edate'";

if (isset($miles))
$table .= "AND Miles = '$miles'";

}
}

$show = mysqli_query($dbconn, $table);

?>

Here is the table that displays correctly.

<table class="table">
<tr>
<td>ID</td><td>Car</td><td>Date</td><td>Location</td><td>Miles</td><td></td><td></td>
 </tr> 
<?php while ($row = mysqli_fetch_assoc($show)) {
echo "<tr><td>".$row['id']."</td>";
echo "<td>".$row['Car']."</td>";
echo "<td>".$row['Date']."</td>";
echo "<td>".$row['Location']."</td>";
echo "<td>".$row['Miles']."</td>";
echo "<td><a href='editentry/delete2.php?id=".$row['id']."'>Delete</a></td>    <tr>";
 }
?>
</table>

Ideally I should be able to fill in any combination of these fields and still be able to perform the query. I have spent 5 days searching the forms and have not been able to find a successful answer that has worked for me. Im sure the answer is simple and something I've just been missing. A point in the right direction would be greatly appreciated. Also, just let me know if I need to change or add anything to my question to help make it a reasonable question in the community.

Answer 1

The only thing that could be figured out from the code is that the blank space was missing in concatenation

 if(isset($carin)){
$table .= "WHERE Car = '$carin'";

if (isset($input))
$table .= " AND Location LIKE '%$input%'";

if (isset($sdate))
$table .= " AND Date BETWEEN '$sdate' AND '$edate'";

if (isset($miles))
$table .= " AND Miles = '$miles'";

}

below code is modified to do the required job replace the above code with this one

$flag=false;
if(isset($carin) or isset($input) or isset($sdate) or issset($miles))
    $table .= "WHERE ";
if(isset($carin)){
  if(!$flag)
    $table .= " Car = '$carin'";
  else
    $table .= " AND Car = '$carin'";
  $flag = true;
}
if (isset($input)){
  if(!$flag)
    $table .= " Location LIKE '%$input%'";
  else
    $table .= " AND Location LIKE '%$input%'";
  $flag = true;
}

if (isset($sdate)){
  if(!$flag)
    $table .= " Date BETWEEN '$sdate' AND '$edate'";
  else
    $table .= " AND Date BETWEEN '$sdate' AND '$edate'";
  $flag = true;
}

if (isset($miles)){
  if(!$flag)
    $table .= " Miles = '$miles'";
  else
    $table .= " AND Miles = '$miles'";
  $flag = true;
}