Date/time based PHP MySQL query sometimes failing?

[stevet70]

Below is the code for a home page that should include a particular template from a date and time based schedule.

The client is able to choose up to 3 different templates (from a longer list) and give each one a date and time to start, so that the home page changes accordingly. Most of the time this works perfectly, but every so often it shows the wrong one, then suddenly shows the correct one again later in the day.

Is there something obviously wrong with the code or the approach?

So far I haven’t been able to run a date/time check on the server while it’s showing the wrong template to rule out that as the issue.

Any help would be greatly appreciated, thanks

<?php
include("inc/db_connect.php");

date_default_timezone_set("Europe/London");

try
	{
		$sql = "SELECT template_id FROM home_schedule 
		WHERE start_date<= CURRENT_DATE() AND start_time<= CURRENT_TIME()
		ORDER BY start_date DESC, start_time DESC LIMIT 1";
		$result = $pdo->query($sql);
	}
catch (PDOException $e)
	{
		$error = 'Error fetching list: ' . $e->getMessage();
		include 'inc/error.html.php';
		exit();
	}

while ($row = $result->fetch())
	{
		$template_id = $row['template_id'];
	}

if(!empty($template_id)) 
	{
		include('inc/home_template_'.$template_id.'.php'); 
	}

?>

[DarthGuido]

One thing that you probably didn’t intend that way is the separate checking of date and time. For example, if you have start date 30-11-2014 and start time 11:00:00 then your query will give the correct result only after 11:00:00 EACH DAY! Because every day the time check will be true only after 11:00:00

[stevet70]

Thank you, that makes perfect sense. So obvious, can’t quite believe logic failed me there.