retrieve data based on date range using mysql ,php [on hold]
- by preethi
I am working on WPF where I have two datepickers when I try to retrieve the information on date range it displays only one record on all dates(same record displaying multiple times eg : date chosen from 01/10/2013 - 3/10/2013) where I have 3 different records on each day but my output is the first record displayed 3 times with same date and time.
function cpWhitelistStats() {
$startDate = $_POST['startDate'];
$startDateTime = "$startDate 00:00:00";
$endDate = $_POST['endDate'];
$endDateTime = "$endDate 23:59:59";
$cpId = $_POST['id'];
$cpName = etCommonCpNameById($cpId);
print "<h2 style=\"text-align: center;\">Permitted Vehicle Summary</h2>";
print "<h2 style=\"text-align: center;\">for $cpName</h2>";
$tmpDate = explode("/", $startDate);
$startYear = $tmpDate[2];
$startMonth= $tmpDate[1];
$startDay = $tmpDate[0];
$tmpDate = explode("/", $endDate);
$endYear = $tmpDate[2];
$endMonth= $tmpDate[1];
$endDay = $tmpDate[0];
$startDateTime = "$startYear-$startMonth-$startDay 00:00:00";
$endDateTime = "$endYear-$endMonth-$endDay 23:59:59";
$custId = $_SESSION['customerID'];
$realCustomerId = $_SESSION['realCustomerId'];
$maxVal = 0;
if ($custId != "") {
$conn = &newEtConn($custId);
// Get the whitelist plates
$staticWhitelistArray = etCommonMkWhitelist($conn, $cpId);
array_shift($staticWhitelistArray);
$startLoopDate = strtotime($startDateTime);
$endLoopDate = strtotime($endDateTime);
$oneDay = 60 * 60 * 24;
// Get the entries
$plateList = array_keys($staticWhitelistArray);
$plate_lookup = implode('","', $plateList);
$sql = "SELECT plate, entry_datetime, exit_datetime FROM stats WHERE plate IN (\"$plate_lookup\") AND entry_datetime > \"$startDateTime\" AND entry_datetime < \"$endDateTime\" AND carpark_id=\"$cpId\" ";
$result = $conn->Execute($sql);
if (!$result) {
print $conn->ErrorMsg();
exit;
}
$rows = $result->fields;
if ($rows != "") {
unset($myArray);
foreach($result as $values) {
$plate = $values['plate'];
$new_platelist[] = $plate;
$inDateTime = $values['entry_datetime'];
$outDateTime = $values['exit_datetime'];
$tmp = explode(' ', $inDateTime);
$inDate = $tmp[0];
$in_ts = strtotime($inDateTime);
$out_ts = strtotime($outDateTime);
$duration = $out_ts - $in_ts;
$dur_array = intToDateArray($duration);
$dur_string = '';
if ($dur_array['days'] > 0) {
$dur_string .= $dur_array['days'] . ' days ';
}
if ($dur_array['hours'] > 0) {
$dur_string .= $dur_array['hours'] . ' hours ';
}
if ($dur_array['mins'] > 0) {
$dur_string .= $dur_array['mins'] . ' minutes ';
}
if ($dur_array['secs'] > 0) {
$dur_string .= $dur_array['secs'] . ' secs ';
}
$myArray[$plate][] = array($inDateTime, $outDateTime, $inDate, $dur_string);
}
}
while ($startLoopDate < $endLoopDate) {
$dayString = strftime("%a, %d %B %Y", $startLoopDate);
$dayCheck = strftime("%Y-%m-%d", $startLoopDate);
print "<h2>$dayString</h2>";
print "<table width=\"100%\">";
print " <tr>";
print " <th>VRM</th>";
print " <th>Permit Group</th>";
print " <th>Entry Time</th>";
print " <th>Exit Time</th>";
print " <th>Duration</th>";
print " </tr>";
foreach($new_platelist as $wlPlate) {
if ($myArray[$wlPlate][0][2] == $dayCheck) {
print "<tr>";
print "<td>$wlPlate</td>";
if (isset($myArray[$wlPlate])) {
print "<td>".$staticWhitelistArray[$wlPlate]['groupname']."</td>";
print "<td>".$myArray[$wlPlate][0][0]."</td>";
print "<td>".$myArray[$wlPlate][0][1]."</td>";
print "<td>".$myArray[$wlPlate][0][3]."</td>";
}
else {
print "<td>Vehicle Not Seen</td>";
print "<td>Vehicle Not Seen</td>";
print "<td>Vehicle Not Seen</td>";
}
print "</tr>";
}
}
print "</table>";
$startLoopDate = $startLoopDate + $oneDay;
}
}
}
