Nodoid Nodoid - 1 year ago 80
MySQL Question

Returning data rows using a date range

I have a simple PHP function sitting on my server that looks at the database and returns table rows that sit between the dates passed in. The code looks like this

if(isset($_GET['startDate'])) {
$startDateRaw = $_GET['startDate'];
$startSplit = explode("_", $startDateRaw);
$startDate = date('d/m/Y', mktime(0,0,0, $startSplit[1], $startSplit[0], $startSplit[2]));
$endDate = date('d/m/Y');

$endDateRaw = $_GET['endDate'];
$endDateSplit = explode("_", $endDateRaw);
$endDate = date('d/m/Y', mktime(0,0,0,$endDateSplit[1], $endDateSplit[0], $endDateSplit[2]));

It then connects to the database and then runs this query and encodes the result

$query = "SELECT * FROM AppointmentList WHERE (DateCreated >= STR_TO_DATE($startDate, '%d%m%Y') AND DateDue <= STR_TO_DATE($endDate, '%d%m%Y'));";
$result = mysql_query($query,$link) or die('Errant query: '.$query);

$posts = array();
if(mysql_num_rows($result)) {
while($post = mysql_fetch_assoc($result)) {
$posts[] = array('Appointments'=>$post);

header('Content-type: application/json');
echo json_encode(array('Appointments'=>$posts));

The query is executed, but no data is returned (there is a shed load of data between the date ranges).

The code can be seen running at appointments code

I'm guessing something is wrong between the conversion in STR_TO_DATE and the format $startDate and $endDate are in, but can't see what it is.

Answer Source

It looks like your dates in your php program look like this: '12/31/2015'

If that is the case, you need to use STR_TO_DATE() like this to get successful conversions to MySQL's internal date format. Notice the / characters in the format string.

 STR_TO_DATE($startDate, '%d/%m/%Y')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download