Schedule script.

[daveyc]

Hello guys and gals,

Right, basically im looking to write a script to show a radio schedule on my website. I've already created my database with the following fields:

showday (The day)
timestart (The time the show starts)
timeend (The time the show ends)
showname (The show for this timeslot)

What im looking to do is to pull the current time and day, then display the next 4 upcoming shows on this day.

So for example:

1. PHP finds the day is Monday and the time is 18:00.

2. The script queries the database and returns the next 4 shows:

   21:00 - 00:00 -- "Show name"
   00:00 - 06:00 -- "Show name"
   06:00 - 09:00 -- "Show name"
   09:00 - 12:00 -- "Show name"

The part im thinking may be a problem is that there are different schedules for each day.

I just need some kind of starting point 🙂

Thanks in advance for any help.

Kindest Regards,

Dave

[msound]

You should create a table field that stores the date and stand in standard mysql/unix format:
yyyy-mm-dd
00:00:00

Then just include the showday field to easily store the day of the week.
Add this field to your table:
showdate set its type to DATE and the default value 0000-00-00

$Link = mysql_connect('localhost', 'db username', 'db pass');
$DBName = 'your database';
$TableName = 'your db table';
$html = '<table>';
$Query = 'Select * from $TableName WHERE showdate >= ' .date('Y-m-d'). ' AND timestart >= ' .date('H:i'). ' ORDER BY showdate, startime ASC LIMIT 4';
$Result = mysql_db_query($DBName, $Query, $Link) or die(mysql_error());
while ($Row = mysql_fetch_array($Result)) {
$html .= '<tr><td>
' .$Row[timestart]. ' - 
' .$Row[timeend]. ' -- 
"' .$Row[showname]. '"
</td></tr>';
}
$html .= '</table>';
echo $html;
mysql_close($Link);

[Thora_Fan]

I would recommend familiarizing yourself with MySQL's Date and Time functions. They are nice and powerful and will save you a lot of jumping back and forth.

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

[daveyc]

Thanks for that starting point msound.

I modified the code a little bit, and sure enough, it's pulling the current show out of the database.. For some reason I couldnt get it to enter into a table 🙁 But I'll tackle that later.. For now, I just want to get the current show (Not display it) and figure out the next four shows.

This is the code I currently have, I'm just a bit lost on where to go from here, I'm hoping somebody can point me in the right direction.

I was thinking of maybe getting the current show ID (Primary Key) and incrementing it by one each time the script finds a record, but im not sure whether this would work. (I'm a noob! :rolleyes: )

Here is my code, and I must add that it's sloppy at the moment:

<?php
// The server is 6 hours behind GMT, so I added the offset below.
$offset = date('H') + 6;
$minutes = date('i');
$newtime = "$offset:$minutes";
$day = date('D');
echo $newtime; // I used these to make sure i was getting the right time.
echo "<br><Br>";
echo $day; // again, another debug line for the right day.

$Link = mysql_connect('localhost', 'MYDATABASE_USER', 'MYPASSWORD'); 
$DBName = 'MY_DATABASE_NAME'; 


$Query = "Select * from shows WHERE showday = '$day' AND showstart <= '$newtime' ORDER BY `showend` ASC"; 
$Result = mysql_db_query($DBName, $Query, $Link) or die(mysql_error()); 
$row['id']+1;

echo "<br><Br>";

while ($row = mysql_fetch_array($Result)) { 
echo $row['showstart'];
echo "-";
echo $row['showend'];
echo " -- ";
echo $row['showtitle'];
echo " ";

}

mysql_close($Link); 
?>

Thanks for any help in advance guys 🙂

Dave..

p.s. Thora_Fan, I think you may be right 🙂 It would certianly help me.. Thanks for the link 🙂

---EDIT---
I've also just realised that the script is pulling all shows before the current time. I've looked at the query and realised why, im just wondering if there is a way around this?

[ONiX]

hy msound! You have one example of DataBase ?? Thank's