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

问题描述：

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.

答

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;
}

答

I believe the problem is that isset will return true on a string of '';

$var = '';
if(isset($var)){
    echo 'True'; // This is the expected behavior
}

What you should use is empty.

For your example above:

if(!empty($carin))
    $table .= "WHERE Car = '$carin'";

Also, considering the multiple cases you have, you're going to want to put a space before each "AND" in your concatenated query.

An easier method might be to push your optional terms into an array, and then implode the array to get the properly concatenated query.

For example, if $carin is empty, and $input isn't, you're going to end up with a query that has no WHERE, but is expecting it.

So, perhaps do something like this.

$terms = array();
if(!empty($carin)){
    array_push($terms, " Car = ".$carin);
}
if(!empty($input)){
    array_push($terms, " Location LIKE '%".$input."%');
}

....

And then, after doing that for all the other terms:

if(count($terms) > 0){
    $table .= $table.' WHERE '.implode(' AND ', $terms);
}

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

相关推荐