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:
- Connect to database
- Initialise
- Prepare header
- Get data from database
- Prepare content, format data
- Read template and replace variables
- Send generated output
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 . ' </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">
</div>
</div>
</div>
</body>
</html>
When run…
From this input
…this output is generated
