TV guide written in PHP - problems with datetime() and database functions

Question

I'm creating a TV Guide which lists programmes coming up (and on some listings, previous airings from the past), with all data stored in a database. It runs in PHP, my version being 5.28 (upgrading to 5.30 or 6 soon).

Below is a script which works (note the field airdate is stored as DATETIME in the database):

[Disclaimer: The script isn't mine, but a generic one I downloaded, and modified to suit my own needs.]

---

<? //connect to mysql //change user and password to your mySQL name and password
mysql_connect("localhost","root","PASSWORD"); 
//select which database you want to edit
mysql_select_db("tvguide1"); 
//select the table
$result = mysql_query("select * from epdata3 order by airdate LIMIT 20;");
//grab all the content
while($r=mysql_fetch_array($result))
{ 
 //the format is $variable = $r["nameofmysqlcolumn"];
 //modify these to match your mysql table columns
$programme=$r["programme"];
$channel=$r["channel"];
#$airdate = strtotime($r['airdate']);
  $airdate = strtotime($r['airdate']);
  $now = strtotime("NOW");
 $currentYear = date("Y", $now);
$yearOfDateFromDatabase = date("Y", $airdate);
 if($yearOfDateFromDatabase == $currentYear)
$dateFormat = "F jS - g:ia"; // dateFormat = 24 December
else
$dateFormat = "F jS, Y - g:ia"; // dateFormat = 01 January 2010
$currentTime = date("g:ia", $airdate); // format of "Y" gives four digit year ie 
2009 not 09
$airdateFormatted = date($dateFormat, $airdate);
$sDate = date("F dS, Y - g:ia",$airdate);
$episode=$r["episode"];
$setreminder=$r["setreminder"];
echo "<tr><td><b>$programme</b></td><td>showing on $channel</td>";
echo "<td>$airdateFormatted</td><td>$episode</td><td>$setreminder</td></tr>";
}
?>

That displays all the episodes coming up, and if there's any coming up the next year, it displays them with the year, like this:

TV Programme showing next on Channel1 December 30th, 2009 - 6:00pm "Episode 1 - Photosynthesis" Set Reminder
TV Programme showing next on Channel1 January 6th - 2:45pm "Episode 2 - Behind the Music" Set Reminder
TV Programme showing next on Channel1 January 7th - 8:00pm "Ultimate Car Crimes" Set Reminder

However, what I would like it to do is remove certain records after a period of time has expired (but that would have to be set somewhere in the script, since programme lengths vary) rather than me manually deleting them from the database. Some programmes are 30 minutes long, others 60 minutes... lengths vary, basically.

What I would like it to do is this (notice that the first listing does not show the date as it is the current date.):

TV Programme showing next on Channel1 6:00pm "CCTV Cities - Wigan" Set Reminder
TV Programme showing next on Channel1 January 9th - 2:45pm "Roman Empire - A History of its People" Set Reminder
TV Programme showing next on Channel1 January 10th - 8:00pm "Celebrity 100 Worst Moments" Set Reminder

but I don't know how to configure it to do this with PHP or the date() function. It works fine with the dates, and showing them. I don't have access to cron jobs since this is on a localhost Apache installation on Windows Vista Home Edition.

If anyone could help me figure this out it would be much appreciated - all help is much appreciated.

I haven't put this as a live site, since it's "in development hell" right now, and I want to get things right as much as possible.

Answer 1

MySQL can literally handle millions of records - why bother deleting when you can archive..? Just don't show the archived records.

for listing future records instead of this:

$result = mysql_query("select * from epdata3 order by airdate LIMIT 20;");

I would suggest something like this:

$result = mysql_query("select * from epdata3 WHERE airdate > '$today' ORDER BY airdate LIMIT 20;");

For a gig listing page I years ago also added a delete algorythm fearing the db could get 'full' - but regretted it later...

Answer 2

Your question is a bit unclear, but I assume you are asking how you can select only episodes from today or future, and how to format the date so that when the episode is airing today, show only the date.

Here's a revised version of your code that can handle both of those:

<?php
//connect to mysql
mysql_connect("localhost","root","PASSWORD");
mysql_select_db("tvguide1"); 

// Select only results for today and future
$result = mysql_query("SELECT * FROM epdata3 WHERE airdate >= CURDATE() ORDER BY airdate ASC LIMIT 20;");

while($r = mysql_fetch_array($result)) { 

    $programme   = $r["programme"];
    $channel     = $r["channel"];
    $airdate     = strtotime($r['airdate']);
    $episode     = $r["episode"];
    $setreminder = $r["setreminder"];
    $now         = time();

    if(date('Y-m-d') == date('Y-m-d', $airdate)) {
        // Same date, show only time
        $dateFormat = 'g:ia';
    } elseif(date('Y') == date('Y', $airdate)) {
        // Same year, show date without year
        $dateFormat = 'F jS - g:ia';
    } else {
        // Other cases, show full date
        $dateFormat = 'F jS, Y - g:ia';
    }

    $airdateFormatted = date($dateFormat, $airdate);

    echo "<tr><td><b>$programme</b></td><td>showing on $channel</td>";
    echo "<td>$airdateFormatted</td><td>$episode</td><td>$setreminder</td></tr>";
}
?>

Answer 3

function reldate ($time) {
    $now = time();
    $cmp_fmt = '%Y%m%d';
    if (strftime($cmp_fmt, $time) == strftime($cmp_fmt, $now)) {
        $out_fmt = '%I:%M %P';
    } else {
        $day = strftime('%e', $time);
        if (preg_match('/([^1]1|^1)$/', $day)) {
            $day_suffix = 'st';
        } elseif (preg_match('/([^1]2|^2)$/', $day)) {
            $day_suffix = 'nd';
        } elseif (preg_match('/([^1]3|^3)$/', $day)) {
            $day_suffix = 'rd';
        } else {
            $day_suffix = 'th';
        }
        $out_fmt = '%B %e' . $day_suffix . ' - %I:%M %P';
    }

    return strftime($out_fmt, $time);
}

Answer 4

http://stackoverflow.com/questions/2031683/pagination-script-for-tv-guide-project is the follow-up to this question.