Date Range (complete) with other variables

[brodie_r]

I current have a date range report, that allows the user to select 2 dates, and it will show all the jobs between those 2 dates. I would like them to be able to filter further so be able to search jobs between 2 dates, but also when status = something, or client = something, etc

My current DATE_RANGE.php page:

<form action="date_range_report_search.php" target="_self" style="display: inline; margin: 0;" method="post" name="formdate" id="formdate">
<table width="21%" border="0" align="center">
 <tr>
 <td colspan="2"><div align="center"><span class="style1">Date Range Report </span></div></td>
 </tr>
 <tr>
 <td width="39%"><div align="right"><span class="style2">Between:</span></div></td>
 <td width="61%"><span style="display: inline; margin: 0;">
 <input type="text" name="date1" size="12" maxlength="25" value="" />
 <a href="#" onclick="cal.select(document.forms['formdate'].date1,'anchor1','yyyy-MM-dd'); return false;"
 name="anchor1" id="anchor1"><img src="cal_icon.gif" width="24" height="14" border="0" /></a></span></td>
 </tr>
 <tr>
 <td><div align="right"><span class="style2">And:</span></div></td>
 <td>
 <input type="text" name="date2" size="12" maxlength="25" value="" />
 <a href="#" onclick="cal.select(document.forms['formdate'].date2,'anchor2','yyyy-MM-dd'); return false;"
 name="anchor2" id="anchor2"><img src="cal_icon.gif" width="24" height="14" border="0" /></a> </td>
 </tr>
 <tr>
 <td>&nbsp;</td>
 <td><span style="display: inline; margin: 0;">
 <input type="submit" name="submit2332" value="View Report" />
 </span></td>
 </tr>
</table>
</form>

My current DATE_RANGE_SEARCH.php page:

<?php
include ('result_top_detail.inc');
require_once ('mysql_connect.php');
$date1 = $_POST['date1'];
$date2 = $_POST['date2'];
$query = "SELECT *, DATE_FORMAT(date, '%W (%d/%m/%y)') as dateFormatted
FROM `jobs` WHERE `date` BETWEEN '" . $date1 . "' AND '" . $date2 . "'";
$result = mysql_query($query);
while ($r = mysql_fetch_assoc($result)) { 
extract($r);?>
<?php include('result_detail.inc');?>
<?php
$i++;
}
echo "</table>";
?>

[coopster]

You merely append to your query statement:

$query = "SELECT *, DATE_FORMAT(date, '%W (%d/%m/%y)') as dateFormatted 
FROM `jobs` WHERE `date` BETWEEN '" . $date1 . "' AND '" . $date2 . "' AND 
 myStatus = 'Active'";

[brodie_r]

Thanks coop. Only problem is on this page i wont know what other variable (ie, status, location, operator).

I would need the standard date range boxes as i do now, but say 3 search boxes underneath (status, location, operator) and if they enter something in the status box it then searches for date range & status, and say they enter something in status and location, it then searches for date range + status + location.

[brodie_r]

10 hours later i finally figure out a 1/2 decent way to do it.

Had a bunch of radio buttons on my original form page, like:

<input type="radio" name="checkbox" value="Date Range w/ Operator" />

Then the actual code on the search page was like this:

if ($_POST["checkbox"] == "Date Range w/ Operator") 
{ 
$query = "SELECT *, DATE_FORMAT(date, '%W (%d/%m/%y)') as dateFormatted
FROM `jobs` WHERE `date` BETWEEN '" . $date1 . "' AND '" . $date2 . "' AND operator = '" . $operator . "' ";
$result = mysql_query($query);
}

[coopster]

Precisely. You check to see if there is anything in the POST value for the additional operations and append them to your WHERE clause. Another way to do this is to build the WHERE clause separately with the concatenating assignment operator. Something like this:

// initialize: 
$whereClause = "WHERE `date` BETWEEN '" . $date1 . "' AND '" . $date2 . "'; 
// First Checkbox: 
if ($_POST["checkbox"] == "Date Range w/ Operator") { 
 $whereClause .= [php.net] " AND operator = '" . $operator . "' "; 
} 
// Second Checkbox: 
if ($_POST["someOtherCheckbox"] == "Some other checkbox") { 
 $whereClause .= [php.net] " AND operator = '" . $operator . "' "; 
} 
$query = "SELECT *, DATE_FORMAT(date, '%W (%d/%m/%y)') as dateFormatted 
FROM `jobs` $whereClause";