Displaying "Now OnAir" information with php/mysql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Displaying "Now OnAir" information with php/mysql

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

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

Post by Benjamin »

I think your 24 is supposed to be a 12.
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

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

Post 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...
user333
Forum Newbie
Posts: 8
Joined: Tue Mar 03, 2009 12:02 pm

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

Post 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.
JasonDFR
Forum Commoner
Posts: 40
Joined: Wed Jan 07, 2009 3:51 am

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

Post 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.
user333
Forum Newbie
Posts: 8
Joined: Tue Mar 03, 2009 12:02 pm

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

Post 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.
JasonDFR
Forum Commoner
Posts: 40
Joined: Wed Jan 07, 2009 3:51 am

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

Post 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
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

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

Post 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.
JasonDFR
Forum Commoner
Posts: 40
Joined: Wed Jan 07, 2009 3:51 am

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

Post 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() ;
Post Reply