Hello everyone. I have been trying for a while now to deal with timezones on my site. My site contains sport schedules. I output match details into a table.
I am trying to work out a way to allow the user to select (from a select box populated with timezone values such as GMT +1:00, GMT 2:00 and so on) the timezone they are in. When the user selects the timezone then the date and time value from the match details (a timestamp field) should be updated to reflect what timezone the user select.
I have used the following code below to set the timezone and it changes the timezone of the date and time field to the offset I have used in the SET statement correctly.
$DB->exec("SET time_zone='+0:00';");`
What is confusing me is how can I have the +0:00 change to whatever the user has selected from a select box, instead of always being set to “+0:00”.
I tried an if statement such as
if (offset = "+1:00") {
$DB->exec("SET time_zone='+1:00';");
}
But when I select +1:00 from the select box and click the submit button, the URL shows offset=+1:00, but the value of the date and time field doesn’t change to reflect the timezone change in the if statement and just stays at the value from the SET statement below
$DB->exec("SET time_zone='+0:00';");
Am I going about this in the right way? Or is there another method I could use? Also I was thinking that the fact that when the submit button is pressed that the output is being put into the URL string instead of the SET time_zone statement. Is there a way I can have the button when clicked change the value of the SET statement to the offset selected from the dropdown box?
Many Thanks for any help or suggestions anyone provides or to anyone who reads this post.
HTML and PHP code as requested 
config.php
<?php
error_reporting( E_ALL & ~E_DEPRECATED & ~E_NOTICE );
define('DB_DRIVER', 'mysql');
define('DB_SERVER', '*******');
define('DB_SERVER_USERNAME', '*******');
define('DB_SERVER_PASSWORD', '*******');
define('DB_DATABASE', '*******');
define('PROJECT_NAME', 'SportSchedule Easy Sports Schedules');
$dboptions = array(
PDO::ATTR_PERSISTENT => FALSE,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);
try {
$DB = new PDO(DB_DRIVER.':host='.DB_SERVER.';dbname='.DB_DATABASE, DB_SERVER_USERNAME, DB_SERVER_PASSWORD , $dboptions);
$DB->exec("SET time_zone='+0:00';");
} catch (Exception $ex) {
echo $ex->getMessage();
die;
}
//get error/success messages
?>
Index.php (main page, user can search by team via search box on this page, as default it will show all matches from database)
<?php
date_default_timezone_set('Europe/London');
echo date('Y-m-d H:i:s');
include './config.php';
include './header.php';
if (offset = "+1:00") {
$DB->exec("SET time_zone='+1:00';");
}
try{
$keyword = trim($_GET["keyword"]);
if ($keyword <> "" ) {
$sql = "SELECT f.hometeam, f.versus, f.awayteam, f.sport, f.competition, f.dateandtime,
Group_concat(s.name SEPARATOR ',') name,
Group_concat(x.channelid_fc SEPARATOR ',') channelid_fc
FROM footballfixtures f
LEFT JOIN fixturechannels x
ON x.matchid_fc=f.matchid
LEFT JOIN satellite s
ON x.channelid_fc=s.channelid
WHERE " . " (hometeam LIKE :keyword)
OR awayteam LIKE :keyword
OR competition LIKE :keyword
GROUP BY f.hometeam, f.versus, f.awayteam, f.sport, f.competition, f.dateandtime,
ORDER BY f.dateandtime ";
$stmt = $DB->prepare($sql);
$stmt->bindValue(":keyword", $keyword."%");
} else {
$sql = "SELECT f.hometeam, f.versus, f.awayteam, f.sport, f.competition, f.dateandtime,
Group_concat(s.name SEPARATOR ',') name,
Group_concat(x.channelid_fc SEPARATOR ',') channelid_fc
FROM footballfixtures f
LEFT JOIN fixturechannels x
ON x.matchid_fc=f.matchid
LEFT JOIN satellite s
ON x.channelid_fc=s.channelid
GROUP BY f.hometeam, f.versus, f.awayteam, f.sport, f.competition, f.dateandtime
ORDER BY f.dateandtime";
$stmt = $DB->prepare($sql);
}
$stmt->execute();
} catch (Exception $ex) {
echo 'Error loading match data';
}
?>
<html>
<head>
<script src="css/footable.filter.js"></script>
</head>
<body>
<div class="container mainbody">
<div class="mainpagetitle">
<h11>Sports Schedule</h11> <br> <br>
<p>We aim to provide you with sports schedule in an easy to view format</p> <br> <br> <br>
<form class="form-inline">
</div>
<div class="clearfix"></div>
<div class="col-xs-12">
<img src="css/tv3.png" class="img-responsive" />
<div id=class="container-fluid">
<div class="row">
<h2>Whats On Today</h2> <br>
<p> All todays matches/events are displayed below, you can search by team/player or competition/event using the search box. If you want to filter by sport, please select the sport name from the select box and filtered results will be displayed. </p>
<br>
<br>
<br>
<br>
</div>
</div>
</div>
<div class="searchform">
<form action="index.php" method="get" >
<label class="col-xs-12" for="offset";>
<select name="offset" id="offset">
<option value="+1:00">+1:00</option>
<option value="+2:00">+2:00</option>
<option value="+3:00">+3:00</option>
<option value="+4:00">+4:00</option>
</select>
</label>
<button class="btn btn-info">search</button>
</form>
</div>
<div class="searchform">
<h2> Search </h2>
<form action="index.php" method="POST" >
<label class="col-xs-12" for="keyword";>
<input type="text" value="<?php echo htmlspecialchars($_POST["keyword"]); ?>" placeholder="Enter Team or Competition" id="" class="form-control" name="keyword">
</label>
<button class="btn btn-info">search</button>
</form>
</div>
<div class="clearfix"></div>
<div class="searchform2">
<p> Select Sport Below to Display Events</p>
<div class="dropbox1">
<input type="hidden" id="filter" type="text"></input>
<select class="filter-status">
<option> </option>
<option value="aussierules">Aussie Rules</option>
<option value="basketball">Basketball</option>
<option value="boxing">Boxing</option>
<option value="football">Football</option>
<option value="gaelic">Gaelic Sports</option>
<option value="handball">Handball</option>
<option value="icehockey">Ice Hockey</option>
<option value="mma">MMA</option>
<option value="motorsports">Motorsports</option>
<option value="rugbyleague">Rugby League</option>
<option value="rugbyunion">Rugby Union</option>
<option value="tennis">Tennis</option>
<option value="volleyball">Volleyball</option>
</select>
</div>
<div class="clearlinks">
<a class="clear-filter" href="#clear" title="clear filter">[clear]</a>
</div>
</div>
<div class="container">
<div class="row">
<div class="tables">
<div class="col-xs-12">
<table class="table table-hover footable" data-filter="#filter" data-filter-text-only="true">
<thead>
<tr>
<th>Home Team</th>
<th> vs </th>
<th>Away Team</th>
<th data-hide="phone, tablet">Sport</th>
<th data-hide="phone, tablet">Competition</th>
<th data-hide="phone, tablet"> Date and Time</th>
<th data-hide="phone, tablet">Channels</th>
</tr>
</thead>
<?php
if($stmt->rowCount() >0) {
while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
$hometeam = $row['hometeam'];
$versus= $row['versus'];
$awayteam= $row['awayteam'];
$sport= $row['sport'];
$competition = $row['competition'];
$dateandtime=$row['dateandtime'];
$name=explode(',', $row['name']);
$channelid=explode(',', $row['channelid_fc']);
?>
<tbody>
<td> <?php echo $row[hometeam] ; ?> </td>
<td> <?php echo $row[versus] ; ?> </td>
<td> <?php echo $row[awayteam] ; ?> </td>
<td> <?php echo $row[sport] ; ?> </td>
<td> <?php echo $row[competition] ; ?> </td>
<td> <?php echo $row[dateandtime] ; ?> </td>
<td>
<?php for ($i = 0; $i < count($channelid) && $i < count($name); ++$i) {
$achannelid = $channelid[$i];
$aname = $name[$i];
//here the code you wanted
echo "<a href='http://sportschedule.xyz/view_channels.php?channelid=" .$achannelid."'> ".$aname." </br> </a> ";
}
?>
</tbody>
</td>
</tr>
<?php } ?>
<?php } else { ?>
<p>No matches found matching your search criteria. Please try again</p>
<?php } ?>
</table>
</div>
</div>
</div>
</div>
<script>
$('.footable').footable({ addRowToggle: false });
$('.footable').footable({
calculateWidthOverride: function() {
return { width: $(window).width() };
}
});
$(function () {
$('table').footable().bind('footable_filtering', function (e) {
var selected = $('.filter-status').find(':selected').text();
if (selected && selected.length > 0) {
e.filter += (e.filter && e.filter.length > 0) ? ' ' + selected : selected;
e.clear = !e.filter;
}
});
$('.filter-status').change(function (e) {
e.preventDefault();
$('table').trigger('footable_filter', {filter: $('#filter').val()});
});
$('.clear-filter').click(function (e) {
e.preventDefault();
$('.filter-status').val('');
$('table').trigger('footable_clear_filter');
});
});
$(document).ready(function(){ if ($.trim($(".footable tbody").text()).length == 0) { $('.footable').hide(); } });
</script>
</body>
</html>