Page 1 of 1

Displaying "Now OnAir" information with php/mysql

Posted: Sat Mar 07, 2009 8:04 pm
by Sindarin
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:

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;
        }
        
}
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.

Re: Displaying "Now OnAir" information with php/mysql

Posted: Sun Mar 08, 2009 12:25 am
by Benjamin
I think your 24 is supposed to be a 12.

Re: Displaying "Now OnAir" information with php/mysql

Posted: Sun Mar 08, 2009 11:57 am
by Sindarin
No different effect, it seems the problem is:

There is a DJ that has a broadcast from e.g. Saturday 23:00 to Sunday 3:00, so when it's Sunday 1:00 he still is on but he is registered for Saturday not Sunday, mysql searches for programme_day = " . $select_weekday which $select_weekday= 3 at Saturday and 4 at Sunday, so I get no result back.
I am really confused, I don't know how to get past this problem...

Re: Displaying "Now OnAir" information with php/mysql

Posted: Sun Mar 08, 2009 1:28 pm
by user333
The first thing that came to my mind is:
start_time(UNIX_TIMESTAMP),
end_time(UNIX_TIMESTAMP)

Now you check start_time>now()<end_time. The switch you can make it like this:

Code: Select all

<?php
$now = time();
?>

Code: Select all

 
<select name="day">
<option value="<?php echo $now;?>"><?php echo date("Arguments you need", $now);?></option>
</select>
 
Keep on unifying your time formats.
Something of the kind will do the job.

Re: Displaying "Now OnAir" information with php/mysql

Posted: Sun Mar 08, 2009 2:43 pm
by JasonDFR

Code: Select all

SELECT *
FROM `time`
WHERE current_time( ) > `start_time`
AND current_time( ) < `end_time`
Is the SQL query that would work, however, 03:00:00 is less than 23:00:00.

The previous example will only work if the date is stored. The problem is, there is no date associated with the times in question.

I don't know the answer, but I am sure it is possible. I'll keep looking.

And btw, do not get in the habit of using php to manipulate dates stored in MySQL. Generally speaking, you can do all date and time manipulation in the SQL query.

Re: Displaying "Now OnAir" information with php/mysql

Posted: Sun Mar 08, 2009 4:31 pm
by user333
I totally agree about the SQL(Using SQL), but what I meant is that in start_time a UNIX timestamp is inserted. It contains all the time data that is needed. I did not get the question(For which I am sorry). I supposed that each date is inserted.

Anyway, in this situation here is a hint:
We have days on which there is a show like:
On Wednesday from 23.00 until 3.00 the DJ is Joe
Now we can take for the current time SQL now()(gives us the timestamp)
Get the start timestamp with mktime(23,00,00,date("m"), date("d"), date("Y"));
Get the end timestamp with mktime(03,00,00,date("m"), date("d"), date("Y"));
Now there is a problem: Timestamps will not give us a correct result so
subtract the second by the first like
if(3-23<0)
increment the day with one
if max date(incremented)>max_possible_date
increment the month by one
if(month>max_possible_month)
increment the year by one
So now it would look like this
First find the subtraction of end hour time and the start hour time

if(result<0) {
increment the day with one
if max date(incremented)>max_possible_date
increment the month by one
if(month>max_possible_month)
increment the year by one
}
//Get the start timestamp with mktime(23,00,00,date("m"), date("d"), date("Y"));
//Get the end timestamp with mktime(03,00,00,date("m"), date("d"), date("Y"));
$start = mktime(23,00,00,date("m"), date("d"), date("Y"));
$end = mktime(03,00,00,$month, $date, $year);//Variables come from the prebious check
//Now we have a valid start date and a valid end date
$sql = "Here we check for $start>now()<$end";

I hope this would help, although it is a little woody, but it so late in the timezone I live in, so I will make something better tomorrow.

Re: Displaying "Now OnAir" information with php/mysql

Posted: Mon Mar 09, 2009 11:46 am
by JasonDFR
I like it.

Can we assume that if x time - y time < 0, x time is the next day? I think this is true, if so, this is the key.

I'll try to work on this some more too.

J

Re: Displaying "Now OnAir" information with php/mysql

Posted: Mon Mar 09, 2009 11:48 am
by Sindarin
My current workaround is to create two entries of the same DJ, e.g. from 23:00 to 23:59 and 00:00 to 01:00. This actually works but requires a bit more hassle to update the program.
The other method I thought is to create user accounts for the DJs so they will login from their terminal when they're on so they will be able to get user messages as well and display their status as onAir, the client likes the idea but thinks many DJs will neglect logging in due to many being not too familiar with computers.
I'll try working on the suggestions posted above, this is a nice challenge.

Re: Displaying "Now OnAir" information with php/mysql

Posted: Mon Mar 09, 2009 1:40 pm
by JasonDFR
This definitely doesn't work. I'm just thinking it through...

SELECT * FROM table WHERE ( if end_time < start_time then end_time = end_time + 24:00:00 ) start_time < NOW() AND end_time > NOW() ;