phpmysqltelevisiontimetable

How create a simple php mysql timetable?

I want to show on a very simple way a TV guide timeline, but i'm really new into this, so I hope somebody can help me I on't want nothing too complicated, and I already search on the web and I find very complex timelines with a lot of functions that i really don't need, I just want to display the current and upcoming tv shows, but I don't know how to do it, something like this:

A really simple timetable

I don't need help to the php mysql connection, I already know how to do that, I just really need help with how display a table like this. This is how my sql table looks:

----------------------------------------------------------------
|  start   |   end    |  channel  |      title      |   info   |
----------------------------------------------------------------
| DATETIME | DATETIME |    INT    |      TEXT       |   TEXT   |
----------------------------------------------------------------

no matter if the table it's static, I really don't need an interactive timetable, just I need to display the current show and maybe the next 4 hour upcoming shows, I hope somebody can help me or give me the link of a open source or commercial script

Solution

  • So, here is a solution for your problem, I guess.
    I suggest that you study the code, learn from it, and enhance it ;-). And, I'd be glad to get some feedback from you!

    The database uses the same item names and item types as you describe in your question.

    The script index.php has a configuration section at the top where you should modify the parameters as necessary.

    These are the blocks of the script:

    The output is laid out as DIVs of variable widths corresponding to the different durations of the TV shows. The widths are calculated as percents, which makes the layout somehow responsive to different screen sizes.
    An overlay DIV shows a marker for the current time.

    As you can see, I use a template tvprogramme.tpl which contains variable references: variable names embraced in curly brackets like this {{varname}}. The script replaces these at the end by runtime values.

    Embedded in the template you find the style definitions (CSS), plus a script which refreshes the page at configurable intervals. You could use an AJAX call to refresh parts of the page if required. And, of course, both the style and javascript definitions could alternatively be held in separate files.

    I suggest to store both files (index.php, tvprogramme.tpl) UTF-8 formatted, without BOM. As you are probably living in the 7-bit world the solution might also work properly when the files are stored in ANSII format.

    index.php (script)

    <?php // ä

// Configuration BEGIN ---------------------------------------------------------

define('displayhours',4); // Hours to display
define('chnlwidth',12);   // Width of channel column, in percents
define('bodymargin',8);   // Margin of BODY, in pixels
define('refreshpage',0.5);  // Refresh page interval, in minutes
define('dbname', 'tv');
define('dbtableprefix', 'tv_');
define('dbtable', 'programmes');
define('dbtable1', 'channels');
define('dbuserid', 'tv_user');
define('dbpasswd', 'tv_password');
define('dbhost', 'localhost');
define('dbport', '3306');
define('dbsocket', '');
date_default_timezone_set('America/New_York');

// Configuration END -----------------------------------------------------------

define('LBRK','<br />');

// Connect to database
$mysqli = new mysqli(dbhost, dbuserid, dbpasswd, dbname, dbport, dbsocket);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error.LBRK;
    exit(1);
}

// Initialise
$time = (isset($_GET['time'])) ? $_GET['time'] : time();
define('fldwidth', (97.4-chnlwidth) / displayhours);
define('secsperhour',3600);
$starthour = $time - ($time % secsperhour);
$endhour = $starthour + (displayhours * secsperhour);
$marker = ($time % secsperhour) / secsperhour;


// $outvars holds variables referred in template
$outvars = array(
    'header'  => '',
    'content' => '',
    'channelwidth' => chnlwidth . '%',
    'markerwidth' => (chnlwidth + fldwidth * $marker) . '%',
    'markerheight' => (1.2) . 'em',
    'bodymargin'  => bodymargin,
    'fieldwidth' => fldwidth . '%',
    'panelminwidth' => 150 * displayhours,
    'refreshpage' => refreshpage * 60000,
);

// Prepare header
$outvars['header'] .= PHP_EOL . '            <div class="hd_channel" style="min-width: '.chnlwidth.'%;">' . date('D, M j',$time) . '</div>' . PHP_EOL;
for($xi=0; $xi<displayhours; $xi++) {
    $outvars['header'] .= '            <div class="hd_field" style="min-width: '.fldwidth.'%;">' . date('g A',$time + ($xi * secsperhour)) . '</div>' . PHP_EOL;
}

// Get data from database
$starttime = date('Y-m-d H:i:s',$starthour);
$starttime1= date('Y-m-d H:i:s',floor(($starthour+60) / 60) * 60);
$endtime   = date('Y-m-d H:i:s',$endhour);
$query = 
    'SELECT p.id as p_id, c.id as c_id, channel, channel_name, start, end, title, info' .
    ' FROM ' .     dbtableprefix . dbtable . ' p, ' . dbtableprefix . dbtable1 . ' c'.
    ' WHERE ( (  (NOT start < \'' . $starttime . '\') AND ' .
              '(NOT start > \'' . $endtime . '\') ' .
           ') OR ' .
           '(  (NOT end < \'' .   $starttime1 . '\') AND ' . 
              '(NOT end > \'' .   $endtime . '\') ' .
           ') ) AND ( p.channel = c.id  )' .
    ' ORDER BY channel, start;';
$result = $mysqli->query($query);
if ($result!==false) {
    // Query was successful, so prepare output
    $entries = array();
    $prevend = false;
    while($row = $result->fetch_assoc()) {
        // For every tuple from the database do ...
        $row['start'] = strtotime($row['start']);
        $row['end'] = strtotime($row['end']);
        $sleft  = ($row['start'] - $starthour) / secsperhour;
        $start = ($row['start']<$starthour) ? $starthour : $row['start'];
        $end   = ($row['end']>$endhour) ? $endhour : $row['end'];
        $sdelta = floor($end - 30 - $start) / secsperhour;
        if (!isset($entries[$row['channel']])) {
            $entries[$row['channel']] = array('name' => $row['channel_name']);
        }
        if (($sdelta+$sleft) > displayhours) $sdelta = displayhours - $sleft;
        if (isset($entries[$row['channel']]['values'])) {
            $ix = count($entries[$row['channel']]['values'])-1;
            $delta = $row['start'] - $entries[$row['channel']]['values'][$ix]['end'];
        } else {
            $delta = 0;
        }
        if ($delta>0) $delta /= secsperhour;
        $pad = ($delta>0) ? fldwidth * floor($delta / secsperhour) : 0;
        $dopad = ( ($prevend===false) || ($row['start'] != $prevend) );
        $entries[$row['channel']]['values'][] = array(
            'start'  => $row['start'],
            'end'    => $row['end'],
            'pad'    => $dopad ? (($delta>0) ? fldwidth * $delta : fldwidth * $sleft) : 0,
            'pleft'  => fldwidth * $sleft,
            'pdeltah' => fldwidth * $sdelta,
            'title' => $row['title'],
            'info' => $row['info'],
        );
        $prevend = $row['end'];
    }
    $outvars['markerheight'] = (1.2 + count($entries) * 2.7) . 'em';

    foreach($entries as $id => $entry) {
        $outvars['content'] .= PHP_EOL;
        $outvars['content'] .= '            <div class="ct_channel">' . $entry['name'] . LBRK . '&nbsp;</div>' . PHP_EOL;
        $pleft = 0;
        $psum = 0;
        foreach($entry['values'] as $k => $xe) {
            if ($xe['pad']>0) {
                $outvars['content'] .= '            <div class="ct_pad" style="width:' . $xe['pad'] . '%; min-width:' . $xe['pad'] . '%;"></div>' . PHP_EOL;
                $pleft = $xe['pad'];
                $psum += $xe['pad'];
            }
            $outvars['content'] .= '            <div class="ct_field" title="' . date('D d, g:i a',$xe['start']) . ' - ' . date('D d, g:i a',$xe['end']) . PHP_EOL . $xe['info'] . '" style="width:' . $xe['pdeltah'] . '%; min-width:' . $xe['pdeltah'] . '%;">' . 
            $xe['title'] . LBRK .$xe['info'] . '</div>' . PHP_EOL;
            $psum += $xe['pdeltah'];
        }
        $pad = (99-chnlwidth) - $psum;
    }
    $result->free();

    // Read template and replace variables
    $template = file_get_contents(dirname(__FILE__).'/tvprogramme.tpl');
    $output = preg_replace_callback('/(\{\{(.*?)\}\})/si', 'compute_replacement', $template);

    // Done, send it out
    echo $output;

} else {

    // Query failed
    echo "Failed to read the database table '" . dbtableprefix . dbtable . "'" .LBRK;
    exit(1);
}

// Return template variable
function compute_replacement($groups) {
    global $outvars;
    return isset($outvars[$groups[2]]) ? $outvars[$groups[2]] : '['.$groups[2].']';
}

?>

    tvprogramme.tpl (template)

    <!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
<head>

<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0"> 

<title>TV Programmes</title>

<style type="text/css">
    BODY { font-family: tahoma, arial, helvetica, sans-serif; font-size: 76%; margin:{{bodymargin}}px; }
    P { margin:0; padding:0; }
    .hd_channel, .ct_channel { width:{{channelwidth}}; background-color:#006699; color:white; clear:left; float:left; vertical-align:top; font-size:1.0em; }
    .hd_field, .ct_field, .ct_pad {  vertical-align:top; font-size:1.0em; float:left; }
    .hd_channel, .hd_field, .ct_channel { border-style:solid; border-color:white; border-width:0 0 1px 1px; padding: 0 0 0 2px; }
    .hd_field { width:{{fieldwidth}}; background-color:#006699; color:white; }
    .ct_field { background-color:#009966; color:white;height:2.6em; padding: 0 2px 0 2px; }  
    .ct_channel, .ct_field { text-overflow: ellipsis; overflow:hidden; }
    .ct_channel { height:2.6em; }
    .ct_channel, .ct_pad, .ct_field { }
    .ct_pad { background-color:transparent; color:black; height:2.6em; }
    .hd_field, .ct_field, .ct_pad { border-style:solid; border-color:white; border-width:0 0 1px 1px; }
    .header, .content { width:100%; min-width:{{panelminwidth}}px; }
    .marker { left:{{bodymargin}}px; top:{{bodymargin}}px; position:absolute; min-width:{{markerwidth}}; border-color:red; border-style:solid; border-width:0 1px 0 0; height:{{markerheight}}; background-color:transparent; color: transparent; z-index:900; }
</style>

<script type="text/javascript">
    function refreshPage() {
        window.location.replace(window.location.pathname);
    }
</script>

</head>

<body onload="setTimeout('refreshPage();',{{refreshpage}});">

<div class="page">
    <div class="panel">
        <div class="header">
{{header}}
        </div>
        <div class="content">
{{content}}
        </div>
        <div class="marker">&nbsp;
        </div>
    </div>
</div>

</body>
</html>

    When run…

    From this input

    enter image description here

    …this output is generated

    enter image description here