php timestamp search script help

[will35010]

I wrote this script to query the database and get the count between timestamps. I'm using strtotime to convert the user selected date and the pre-selected times into a usable timestamp to search with. I've echo the first one to ensure that it's creating the correct timestamp with time and it is. The problem is that it's returning data that's outside of the time. I don't know if it's my php or mysql, so I was hoping one of you could help. Thanks!

 

<?php

/**
* @author William Morris
* @copyright 2010
*/

include('inc/db.php');

$startdate = "2010-07-01";
$enddate = "2010-08-05";

echo "Start Date: ".$startdate."<br>";
echo "End Date: ".$enddate."<br>";


//convert dates into timestamp
$startdate1 = strtotime($startdate."00:00:00");
$enddate1 = strtotime($enddate."11:59:59");

echo "Start Date 1: ".$startdate1."<br>";
echo "End Date 1: ".$enddate1."<br>";

//convert dates into morning timestamp
$startdate1 = strtotime($startdate."06:00:00");
$enddate1 = strtotime($enddate."11:59:59");

$sql = mysqli_query($conn, "SELECT COUNT(event_time) FROM `event` WHERE event = 'Registration' 
AND event_time BETWEEN '$startdate1' AND '$enddate1'");

$row = mysqli_fetch_array($sql);

//convert dates into afternoon timestamp
$startdate2 = strtotime($startdate."12:00:00");
$enddate2 = strtotime($enddate."17:59:59");

$sql1 = mysqli_query($conn, "SELECT COUNT(event_time) FROM `event` WHERE event = 'Registration' 
AND event_time BETWEEN '$startdate2' AND '$enddate2'");

$row1 = mysqli_fetch_array($sql1);

//convert dates into Evening timestamp
$startdate3 = strtotime($startdate."18:00:00");
$enddate3 = strtotime($enddate."23:59:59");

$sql2 = mysqli_query($conn, "SELECT COUNT(event_time) FROM `event` WHERE event = 'Registration' 
AND event_time BETWEEN '$startdate3' AND '$enddate3'");

$row2 = mysqli_fetch_array($sql2);

//convert dates into night timestamp
$startdate4 = strtotime($startdate."00:00:00");
$enddate4 = strtotime($enddate."05:59:59");

$sql3 = mysqli_query($conn, "SELECT COUNT(event_time) FROM `event` WHERE event = 'Registration' 
AND event_time BETWEEN '$startdate4' AND '$enddate4'");

$row3 = mysqli_fetch_array($sql3);




echo "<table width='50%' border='0'>
  <tr>
    <td bgcolor='#d3d3d3'><div align='center'>Morning (6am-12pm)</div></td>
    <td bgcolor='#d3d3d3'><div align='center'>Afternoon (12pm-6pm)</div></td>
    <td bgcolor='#d3d3d3'><div align='center'>Evening (6pm-12am)</div></td>
    <td bgcolor='#d3d3d3'><div align='center'>Night (12am-6am)</div></td>
  </tr>";

echo "<tr>
    <td><div align='center'>".$row['COUNT(event_time)']."</div></td>
    <td><div align='center'>".$row1['COUNT(event_time)']."</div></td>
    <td><div align='center'>".$row2['COUNT(event_time)']."</div></td>
    <td><div align='center'>".$row3['COUNT(event_time)']."</div></td>
  </tr>";

echo "</table>";

?>

[PFMaBiSmAd]

it's returning data that's outside of the time.

 

You would need to be a lot more specific about what results you did get. Which query is doing this and what are the actual data values in your table that are being matched (you might want to actually return and display the matching rows before you attempt to do a count on them)?

 

Are you sure the data in your table is what you expect?  What code was used to produce and insert the Unix Timestamps in the table?

 

Edit: And in looking at your code more closely, the $enddate is the next day so most of the $enddateX calculations are not right. You should actually be using the $startdate + the time values and only use the $enddate where the time is past midnight.

[will35010]

I just figured out the problem. It's returning exactly what I asked. I'm not parsing the data and converting that into a count.

[PFMaBiSmAd]

Actually, I edited my post above. The calculations are not correctly using the right day(s).

 

And if you would store your data using a DATETIME data type, it would be much clearer and would result in simpler and cleaner code.