My mySQL table structure is as follows:
programme_id <primary key
programme_day < weekday ( 2=friday 3=saturday 4=sunday 1=all other days )
programme_start_time < a broadcast's starting time in h:i:s format
programme_end_time < a broadcast's starting time in h:i:s format
programme_producer < the DJ name
I made the following function:
Code: Select all
/* onAir */
function onAir()
{
//GET THE CURRENT WEEKDAY
$select_weekday = "";
switch(date('w')){
case 5:
//friday
$select_weekday = 2;
break;
case 6:
//saturday
$select_weekday = 3;
break;
case 0:
//sunday
$select_weekday = 4;
break;
default:
//all week
$select_weekday = 1;
break;
}
$onair_query = "
SELECT programme_producer
FROM programme
WHERE TIME_TO_SEC(TIME(NOW())) > TIME_TO_SEC(programme_start_time)
AND TIME_TO_SEC(TIME(NOW())) < IF (TIME_TO_SEC(programme_start_time)>TIME_TO_SEC(programme_end_time),
TIME_TO_SEC(programme_end_time) + 60*60*24,
TIME_TO_SEC(programme_end_time)) AND programme_day = " . $select_weekday . " LIMIT 1";
$onair_result = mysql_query($onair_query) or die ('Error: ' . mysql_error());
while($row = mysql_fetch_array($onair_result))
{
$get_prod=$row['programme_producer'];
//show who's broadcasting
echo $get_prod;
}
}It works, however when a DJ has to broadcast from e.g. 11 in the evening to 3 in the morning next day, mysql returns nothing. In the database this is recorded as 23:00:00 - 03:00:00.