How create a simple php mysql timetable? -
i want show on simple way tv guide timeline, i'm new this, hope can me on't want nothing complicated, , search on web , find complex timelines lot of functions don't need, want display current , upcoming tv shows, don't know how it, this:
i don't need php mysql connection, know how that, need how display table this. how sql table looks:
---------------------------------------------------------------- | start | end | channel | title | info | ---------------------------------------------------------------- | datetime | datetime | int | text | text | ---------------------------------------------------------------- no matter if table it's static, don't need interactive timetable, need display current show , maybe next 4 hour upcoming shows, hope can me or give me link of open source or commercial script
so, here solution problem, guess.
suggest study code, learn it, , enhance ;-). and, i'd glad feedback you!
the database uses same item names , item types describe in question.
the script index.php has configuration section @ top should modify parameters necessary.
these blocks of script:
- connect database
- initialise
- prepare header
- get data database
- prepare content, format data
- read template , replace variables
- send generated output
the output laid out divs of variable widths corresponding different durations of tv shows. widths calculated percents, makes layout somehow responsive different screen sizes.
an overlay div shows marker current time.
as can see, use template tvprogramme.tpl contains variable references: variable names embraced in curly brackets {{varname}}. script replaces these @ end runtime values.
embedded in template find style definitions (css), plus script refreshes page @ configurable intervals. use ajax call refresh parts of page if required. and, of course, both style , javascript definitions alternatively held in separate files.
i suggest store both files (index.php, tvprogramme.tpl) utf-8 formatted, without bom. living in 7-bit world solution might work when files stored in ansii format.
index.php (script)
<?php // รค // configuration begin --------------------------------------------------------- define('displayhours',4); // hours 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 database $mysqli = new mysqli(dbhost, dbuserid, dbpasswd, dbname, dbport, dbsocket); if ($mysqli->connect_errno) { echo "failed connect 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; } // data 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 p_id, c.id c_id, channel, channel_name, start, end, title, info' . ' ' . dbtableprefix . dbtable . ' p, ' . dbtableprefix . dbtable1 . ' c'. ' ( ( (not start < \'' . $starttime . '\') , ' . '(not start > \'' . $endtime . '\') ' . ') or ' . '( (not end < \'' . $starttime1 . '\') , ' . '(not end > \'' . $endtime . '\') ' . ') ) , ( p.channel = c.id )' . ' order channel, start;'; $result = $mysqli->query($query); if ($result!==false) { // query successful, prepare output $entries = array(); $prevend = false; while($row = $result->fetch_assoc()) { // every tuple database ... $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 $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'] $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 , replace variables $template = file_get_contents(dirname(__file__).'/tvprogramme.tpl'); $output = preg_replace_callback('/(\{\{(.*?)\}\})/si', 'compute_replacement', $template); // done, send out echo $output; } else { // query failed echo "failed read 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 input
…this output generated
Comments
Post a Comment