[RESOLVED] Need to improve Script that show who's on air at station

valeryan

Hey Friendly Peoples, 😉
I Started work at a radio station a few months back and I have been renovating our website. One of my projects is to improve the script that controls a sidebar widget that shows which dj is on air. I would like to find a better way to make this work. Maybe have it run from a database so it can be edited easier. I really haven't dealt with time and date stuff much so I wanted to get some suggestions or ideas on how to go about improving this script. I would like to see about how you guys might go about solving this issue. I have set and brain stormed out some possibilities but I just cant seem to make it work out. My vision for how I would like it to work is kinda like this. Each dj in the database would have the days he is on air and the times that day he is on air. Then I would have a class that takes the current day and time and finds a dj that matches. I can pretty much build the database but I am kinda at a loss as to how I would match the day and time to a dj and how I could protect djs from overlapping. This is What is currently running the widget.

<?php
//set timezone
putenv ('TZ=America/Chicago');
mktime(0,0,0,1,1,1970);
$day = date("N");
$time = date("Hi");
if ($day == 7) 
{
switch($time)
	{
		case ($time >= '0600' && $time <= '0959'):
			echo"<img src='images/onair/haroldgrimmett.png' alt='Harold Grimmett is On-Air Now' class='onair'/>";
			break;
		case ($time >= '1000' && $time <= '1200'):
			echo"<img src='images/onair/kix103.png' alt='Harold Grimmett is On-Air Now' class='onair'/>";
			break;
		default:
			echo"<img src='images/onair/kix103.png' alt='Kix 103 the Country Leader is On-Air Now' class='onair'/>";
			break;
	}
}
elseif($day == 6) 
{
	echo"<img src='images/onair/kix103.png' alt='Kix 103 the Country Leader is On-Air Now' class='onair'/>";
}
else 
{	
	switch($time)
	{
		case ($time >= '0600' && $time <= '0959'):
			echo"<img src='images/onair/steveandboomer.png' alt='Steve &amp; Boomer is On-Air Now' class='onair'/>";
			break;
		case ($time >= '1000' && $time <= '1459'):
			echo"<img src='images/onair/kc.png' alt='K.C. Wright is On-Air Now' class='onair'/>";
			break;
		case ($time >= '1500' && $time <= '1859'):
			echo"<img src='images/onair/jr.png' alt='J.R. Schumann' class='onair'/>";
			break;	 
		case ($time >= '1900' && $time <= '2359'):
			echo"<img src='images/onair/jrandalharvey.png' alt='J Randal Harvey is On-Air Now' class='onair'/>";
			break;		
		case ($time >= '2400' && $time <= '0559'):
			echo"<img src='images/onair/blairgarner.png' alt='Blair Garner is On-Air Now' class='onair'/>";
		default:
			echo"<img src='images/onair/kix103.png' alt='Kix 103 the Country Leader is On-Air Now' class='onair'/>";
			break;		
	}	
}
?>

You can see it in action on www.kix1033.com

Thanks for your time and suggestions.

Vale

johanafm

Instead of relying on the TZ environment variable, you should set your default timezone either using the ini setting date.timezone or with the date_default_timezone_set. See http://www.php.net for more info.
As for what information to put in the database, I'd say it depends on what the schedule looks like over time. If every dj always is on air during the same time every day, I'd just keep dj_id, start_time, end_time as time datatype in the db. If it varies from day to day, but stays the same from week to week, I'd also add a weekday field. If there is no pattern to air times, I'd go back to storing only start and end, but instead of using a time datatype, I'd use datetime.

After that it's pretty straight forward to retrieve the current dj. Example assuming the schedule is the same from week to week

$now = date('H:m:i');
$weekday = date('N');
$qry = 'SELECT dj.name, img, show.name AS show_name
FROM dj
INNER JOIN show ON show.dj_id = dj.id
WHERE weekday = '.$weekday." AND '$now' BETWEEN start_time AND end_time";

Assuming the two tables are dj and show, where dj contains all the djs, and show contains the radio shows, including the id of the dj hosting it.

From an administrative viewpoint, there may still be a point to storing datetimes, rather than just times as above. Otherwise, it's not possible to make changes more than one week ahead of time. If you do go with this approach, remove the start/end times from the show table and create a new table, air_time, containing id, show_id, start_time, end_time (which are now datetime types). This way you still only need to keep show info in one single place, but there will be on row in air_time for each time the show is airing.
And to make it easier to administrate this, you could go with something along the lines of letting the user specify start and end times, start and end dates and have checkboxes for weekdays. Then you let your script figure out what datetimes that correspond to and insert one row for each.

valeryan

Thanks for the help. I never seem to be able to get the inner join thing to work out right in my head. I usually just give in and do two queries to get to the same place a join would get me. Anyway you guessed at my database structure almost perfectly the only difference is that I don't have a show.name field and a few of the table names. I think I may add show.name later because it would allow for more versatility. I have the show names built in to the graphic currently. Your query helped me get the info in one step and it works well. This is my class. its a lot less code and way more efficient then a complex set of switches. Thank you again for you time

<?php
class onair extends mysql{
//----------------------------------<VAIRABLES>------------------------------------//
	protected $connect = array('host'=>DSN_HOST,
						'db'=>DSN_DB,
						'user'=>DSN_USER,
						'password'=>DSN_PASSWORD);
//----------------------------------</VAIRABLES>------------------------------------//

public function __construct() 
{
	date_default_timezone_set('America/Chicago');
	$weekday = date("N");
	$now = date("H:m:i");
	$this->dsn($this->connect);
	$query = 'SELECT name, image 
            FROM dj 
            INNER JOIN `show` ON show.dj_id = dj.id
            WHERE weekday = ? 
            AND ? BETWEEN start_time AND end_time 
            LIMIT 1';
	$params = array($weekday, $now);
	$this->query($query, $params, true);
	if ($this->rowcount > 0) {
		echo '<img class="onair" alt="' . $this->results[0]['name'] . ' is On-Air Now" src="' . $this->results[0]['image'] . '"/>';
	} else {
		echo '<img class="onair" alt="Kix 103.3 - the Country Leader is On-Air Now" src="images/onair/kix103.png"/>';
	}		
}		
}
?>

Weedpacket

Instead of writing the JOIN explicitly you could write it as

Select dj.name, dj.image
From dj,`show`
Where weekday=?
and ? between start_time and end_time
and show.dj_id=dj.id
Limit 1

The query optimiser would compile the same execution plan either way.

strawbs89

this might be a very old post but valeryan could you please help me with this script

<?php
class onair extends mysql{
//----------------------------------<VAIRABLES>------------------------------------//
protected $connect = array('host'=>DSN_HOST,
'db'=>DSN_DB,
'user'=>DSN_USER,
'password'=>DSN_PASSWORD);
//----------------------------------</VAIRABLES>------------------------------------//

public function __construct()  
{ 
    date_default_timezone_set('America/Chicago'); 
    $weekday = date("N"); 
    $now = date("H:m:i"); 
    $this->dsn($this->connect); 
    $query = 'SELECT name, image  
            FROM dj  
            INNER JOIN `show` ON show.dj_id = dj.id 
            WHERE weekday = ?  
            AND ? BETWEEN start_time AND end_time  
            LIMIT 1'; 
    $params = array($weekday, $now); 
    $this->query($query, $params, true); 
    if ($this->rowcount > 0) { 
        echo '<img class="onair" alt="' . $this->results[0]['name'] . ' is On-Air Now" src="' . $this->results[0]['image'] . '"/>'; 
    } else { 
        echo '<img class="onair" alt="Kix 103.3 - the Country Leader is On-Air Now" src="images/onair/kix103.png"/>'; 
    }         
}

}
?>

Weedpacket

It is a very old post, yes. But you have successfully copied-and-pasted the code. What is the problem?

valeryan

[ATTACH]5471[/ATTACH]

You provided very little information about what you are asking, but I am going to take a wild guess and say you are trying to copy and past this code and use it. This is not a complete example. You will notice this class is extending a mysql class. As this is an 8ish year old post I don't have any idea what was in the mysql class but I would also hazard to guess that it was my own PDO class that handled things like setting up the database connection.

Maybe provide some details on what you are trying to accomplish... and we could then help.

zombie_thread.jpg