Displaying "Now OnAir" information with php/mysql
Posted: Sat Mar 07, 2009 8:04 pm
For a radio station site. I need to display a "Now OnAir" (DJ status) message from a mysql database.
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:
This tells SQL to go and return the first DJ (producer here) he finds in the database whose day is equal to the current weekday and start time is equal or larger than start time and equal or smaller than end time.
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.
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.