Wildcard in drop-down box search

[joesoaper]

I have a drop down box in my search form:

 

search.php

<form action="report.php" method="get">
Service Docket report from : 
<input type="date" name="d1" value="" /> to: <input type="date" name="d2" value="" /> 
<select   size="1" name="status" >
<option value=" ">All</option>
<option value="Open">Open</option>
<option value="Closed">Closed</option>

</select> 

This goes to the php query:

 

report.php

<?php
include('connect.php');




if (isset($_GET["d1"])) { $d1  = $_GET["d1"]; } else { $d1=0; }; 
if (isset($_GET["d2"])) { $d2  = $_GET["d2"]; } else { $d2=0; };
if (isset($_GET["status"])) { $status  = $_GET["status"]; } else { $status=0; };

$result = $db->prepare("SELECT * FROM invoices WHERE date BETWEEN :a AND :b  AND status LIKE :c  ORDER BY s_doc ASC");
$result->bindParam(':a', $d1);
$result->bindParam(':b', $d2);
$result->bindParam(':c', $status);




 
$result->execute();
$count = $result->rowCount();
print("<b> $count\n");
for($i=0; $row = $result->fetch(); $i++)

{
?>

I have been trying to get the query to select all records when the status drop down box is set to "All", but with no success. I have tried setting the value to * and "" and " " as well as many other variations of that but all to no avail. 

 

Thank you in advance for any advice.

[ginerjm]

Eliminate that portion of the where clause with a variable

[joesoaper]

Sorry to be dumb.. but if I do that then how can i select dates.. the to and from?

[mac_gyver]

you need to build the WHERE clause dynamically, so that it only contains the term(s) that you want the query to use to match data. the easiest way of doing this is to add each term to an array, then implode the array using the ' AND ' keyword as the implode separator. this will even work if there is only one term. at the same time you add each term to this array, you would add the corresponding data value to a parameters array, to be used to supply the data to the prepared query.

 

btw - your existing php conditional logic isn't doing what you think. all three of the form fields will be set any time the form has been submitted. you should instead just detect if each form field has a non-empty value. the empty() function will not throw an error if the form field isn't set. if you want your date comparison to work if none, only one, or both dates have values, use individual comparisons in the sql statement, not the BETWEEN comparison. you would add the comparison term to the array i have suggested for each date that has a value. this again will cause the logic to work regardless of how many of the dates have values.

 

also, your search form should be on the same page as your report code and you should re-populate the form fields with any existing get data. this will allow the user to both see and alter the search parameters.

 

if i get a chance i will post some example code.

Edited February 8, 2017 by mac_gyver

[joesoaper]

Thank you very much for that... very informative.

[mac_gyver]

example code - 

// build the dynamic WHERE terms
$and_terms = array(); // an array to hold the AND terms for the sql statement
$params = array();  // an array to hold the data values for the prepared query

if(!empty($_GET['d1']))
{
    // there is a starting date

    // you would validate the submitted data here...
    
    // if the data is valid, add the elements to the two arrays
    $and_terms[] = "date >= ?";
    $params[] = $_GET['d1'];
}

if(!empty($_GET['d2']))
{
    // there is an ending date

    // you would validate the submitted data here...
    
    // if the data is valid, add the elements to the two arrays
    $and_terms[] = "date <= ?";
    $params[] = $_GET['d2'];
}

if(!empty($_GET['status']))
{
    // there is a status (Open/Closed) value

    // you would validate the submitted data here...
    
    // if the data is valid, add the elements to the two arrays
    $and_terms[] = "status = ?";
    $params[] = $_GET['status'];
}

$where_term = ''; // define an empty default where term
if(!empty($and_terms))
{
    $where_term = 'WHERE ' . implode(' AND ',$and_terms);
}

$query = "SELECT * FROM invoices $where_term ORDER BY s_doc ASC";
$result = $db->prepare($query);
$result->execute($params); // note: this works even if $params is an empty array

[joesoaper]

Thank you so much mac_gyver, really appreciate the time you put into this to help me.