This is a continuation of my radio station fansite project question from before. Our small team (3 people) have decided that JSON is not useful for now, but we are still aiming for a table with the rollover effect as seen at http://www.vikingfm.co.uk/on-air/station-schedule/
We've got the essential things done; content researched, pictures, information etc. and our localhost (MAMP) with PHP 5.53 and PDO, so that's one half of the work done; now the second half is finishing the site, which we've mostly done, apart from one page, as I will mention below.
This is my current code, which works well enough but isn't very interactive, it just displays the table in plain format as a list of shows which is OK but lacks any interactivity (apart from hyperlinks):
<html>
<head>
<link rel="stylesheet" type="text/css" href="style.css">
<style type="text/css">
table {
border-collapse: collapse;
}
tr.heading {
font-weight: bolder;
}
td {
border: 1px solid black;
padding: 0 0.5em;
}
</style>
</head>
<body>
<h2>Schedule</h2>
<table>
<tr class="heading">
</tr>
{% for d in data %}
<tr>
<td>{{ d.airtime||date("H:i")|escape }}</td>
<td><img src="{{ d.image|escape }}"></td>
<td>{{ d.presenter|escape }}</td>
<td>{{ d.description|escape }}</td>
</tr>
{% endfor %}
</table>
</body>
</html>
and the PHP code behind it:
<?php
// include and register Twig auto-loader
include 'Twig/Autoloader.php';
Twig_Autoloader::register();
// attempt a connection
try {
$dbh = new PDO('mysql:dbname=radio;host=localhost', 'test', 'PASSWORD');
} catch (PDOException $e) {
echo "Error: Could not connect. " . $e->getMessage();
}
// set error mode
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// attempt some queries
try {
// execute SELECT query
// store each row as an object
$sql = "SELECT * FROM stationschedule ORDER BY airdate AND expiration LIMIT 0,20";
$sth = $dbh->query($sql);
while ($row = $sth->fetchObject()) {
$data[] = $row;
}
// close connection, clean up
unset($dbh);
// define template directory location
$loader = new Twig_Loader_Filesystem('templates');
// initialize Twig environment
$twig = new Twig_Environment($loader);
// load template
$template = $twig->loadTemplate('radio.htm');
// set template variables
// render template
echo $template->render(array (
'data' => $data
));
} catch (Exception $e) {
die ('ERROR: ' . $e->getMessage());
}
?>
Now, however, we're looking at re-designing this part of our site so it looks better (it is offline for now, and only on a localhost MAMP install).
What we are trying to do is use a Javascript like that seen in http://www.vikingfm.co.uk/on-air/station-schedule/ but without copying their design or using JSON.
The first thing we have done is tried to copy the basic HTML for the table to use with our own CSS, and this is stored as template1.html:
<div class="content-inner">
<div class="row">
<div class="column size-12">
<div class="page-header-widget page-header-info widgetbox" id="header-generic">
<h2>Station Schedule</h2>
</div>
</div>
</div>
<div class="row">
<div class="column size-10">
<div class="large-schedule-widget main-col main-col-large widgetbox">
<table class="schedule-table">
<!-- column headers -->
<thead>
<tr>
<th class="hide">Units</th>
<th>Hours</th>
<th>Monday</th>
<th>Tuesday</th>
<th>Wednesday</th>
<th>Thursday</th>
<th>Friday</th>
<th>Saturday</th>
<th>Sunday</th>
</tr>
</thead>
<tbody>
<tr id="units-0">
<td class="hide">0</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">00:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-1">
<td class="hide">1</td>
<!-- Every other row echo an hour block -->
</tr>
<!-- echo a row for each of the 48 units -->
<tr id="units-2">
<td class="hide">2</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">01:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-3">
<td class="hide">3</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-4">
<td class="hide">4</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">02:00</td>
</tr>
<tr id="units-5">
<td class="hide">5</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-6">
<td class="hide">6</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">03:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-7">
<td class="hide">7</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-8">
<td class="hide">8</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">04:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-9">
<td class="hide">9</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-10">
<td class="hide">10</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">05:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-11">
<td class="hide">11</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-12">
<td class="hide">12</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">06:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-13">
<td class="hide">13</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-14">
<td class="hide">14</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">07:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-15">
<td class="hide">15</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-16">
<td class="hide">16</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">08:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-17">
<td class="hide">17</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-18">
<td class="hide">18</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">09:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-19">
<td class="hide">19</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-20">
<td class="hide">20</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">10:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-21">
<td class="hide">21</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-22">
<td class="hide">22</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">11:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-23">
<td class="hide">23</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-24">
<td class="hide">24</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">12:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-25">
<td class="hide">25</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-26">
<td class="hide">26</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">13:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-27">
<td class="hide">27</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-28">
<td class="hide">28</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">14:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-29">
<td class="hide">29</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-30">
<td class="hide">30</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">15:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-31">
<td class="hide">31</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-32">
<td class="hide">32</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">16:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-33">
<td class="hide">33</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-34">
<td class="hide">34</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">17:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-35">
<td class="hide">35</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-36">
<td class="hide">36</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">18:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-37">
<td class="hide">37</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-38">
<td class="hide">38</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">19:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-39">
<td class="hide">39</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-40">
<td class="hide">40</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">20:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-41">
<td class="hide">41</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-42">
<td class="hide">42</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">21:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-43">
<td class="hide">43</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-44">
<td class="hide">44</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">22:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-45">
<td class="hide">45</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-46">
<td class="hide">46</td>
<!-- Every other row echo an hour block -->
<td class="time" rowspan="2">23:00</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-47">
<td class="hide">47</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
<tr id="units-48">
<td class="hide">48</td>
<!-- start polling days -->
<!-- finish echo a row for each of the 48 units -->
</tr>
</tbody>
</table>
</div> <!-- end .main-col -->
The first problem is, how can I get the PHP/PDO/MySQL to order it like on the table, all the shows at midnight go across, 1am going across, 6am going across etc. for the 7 days of the week using just PHP, PDO, MySQL and a bit of Javascript for a rollover?
Although the design of the site is obviously copyrighted, the idea of a station schedule with a rollover can't be; as obviously, ideas can't be copyrighted, so what would be the best way for me and my small team to accomplish this?
We've done friends' websites in Twig before, but that was simple PHP/MySQL/PDO and/or PHP variables for very small sites; for a slightly bigger one (a radio station fansite) how would we get this done, and working properly?