PHP difference for optional query fields and required query fields

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.

Rajesh Jangid · Accepted Answer · 2015-12-28T22:52:20.700

0

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

edited Dec 28 '15 at 22:52

answered Dec 28 '15 at 21:26

Rajesh Jangid

724
6
13

Awesome! Almost completely fixed my problem. Now, the only thing that I still need optional is the Car field. How can I get the car field still optional so it doesnt have to be selected? – Matthew Davis Dec 28 '15 at 22:33
i have modified some code that need to be replaced with original one @MatthewDavis – Rajesh Jangid Dec 28 '15 at 22:54
Sweet! My script is working. I still had to modify what you game me, but it worked. Im more than happy to accept this answer now. I have one other quick question for you, if you don't mind. I have the start date and end date as $sdate and $edate. In my database I have one column named date. The variables are so I can search between the date range. Is it possible to make it so I can still run the query if only the start date is set, but still search between the dates if both are set? – Matthew Davis Dec 28 '15 at 23:13
i don't think in your case it will be possible with only start date but if the end date is not passed it could be set default to current date... – Rajesh Jangid Dec 28 '15 at 23:22
Ok. Thanks. For now, I can be happy with where its at. I will look into that in the future when I come out with v.2 of my program. – Matthew Davis Dec 28 '15 at 23:30

P. Gearman · Answer 2 · 2015-12-28T21:30:04.233

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

Thanks for the answer. Will be trying it out now. With the array, will I still be calling it out in my table the same way? — Matthew Davis, Dec 28 '15 at 21:46
Thats interesting.. It seems however I do it, this line has a problem. It does not even end up color coded, even when I copy and paste the original for you. if(!empty($sdate)){ array_push($terms, " Date BETWEEN '$sdate' AND '$edate'); The error I get is unexpected Date on line 36 @P. Gearman — Matthew Davis, Dec 28 '15 at 22:13

PHP difference for optional query fields and required query fields

2 Answers2