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:
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:
- 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
- How/when mysql compiles stored procedures?
- Search a 2d array by one column value and return another value from the qualifying row
- Search a multidimensional array by one column value and return another value from the qualifying row
- Get element value from a 2d array row which qualifies because of another column value
- Can I use array_search() to find a whole-row match in a 2d array?
- How to secure my login page
- Symfony 2: How to get route defaults by route name?
- php - find if an array contains an element
- Doctrine entity object to array
- Get URL to a Sulu page by content id
- Determine if an associative array of strings or arrays is equal to another array with the same structure
- PHP How to determine the first and last iteration in a foreach loop?
- Disable Undefined Property error in PHP Devsense extension on VSCode
- Fill the ACF field who modified the user profile
- How to Integrate 3rd party API in Wordpress
- Coverting a .BAK file into a .SQL for use in XAMPP phpmyadmin
- Add product thumbnails to Woocommerce admin orders list, only if product still exists
- Merge two 2d arrays, group by one column and sum another column within each group
- PHP json_decode multiple arrays or array in array to database
- laravel try/catch doesn't work for a failed query
- Return the first level key when a column value is found in a 2d array
- Find key of parent in array / PHP
- Find parent key of match in a multidimensional array
- Recursively search a multidimensional array for the parent key of a matched key or value
- Find a parent key in a 2d array
- Get first level key in a 2d array of the first row with a specified value in any element
- Removing currency symbol, except on Woocommerce cart and checkout pages
- How to display course list in moodle form?
- Multiple Image Upload PHP form with one input
- How to remove "Shipping" label from Woocommerce Email notifications?