I have developed a PHP system where my friends can bet on who will become the European football champions. The system allows users to set their scores for every game up to one hour before a match starts, after which the scores are “locked” and no further changes can be made. Additionally, users must predict the gold and silver medalists before the tournament’s first match begins.
I have a cron job (cron.php) that runs every minute to check if a user has forgotten to set a score or predict medalists. If a match is locked and no score is set, or if the first match starts and no medalists are predicted, the cron job iterates through all players and assigns random values to all locked games with NULL scores or no scores at all.
The system works almost as intended, except for an issue with the audit system that logs the actions of the cron job in human-readable format. The timestamps in these logs are saved in UTC, which is the non-changeable server time of my hosting service. This affects all audit entries related to:
- Setting scores that do not exist.
- Setting scores that exist but are NULL.
- Setting medalist records that exist but are NULL.
- Setting medalist records that do not exist in the database.
After spending many hours troubleshooting, I am unable to resolve this issue. I have isolated the problem to the following line:
// Set MySQL timezone to Helsinki time
$C->query("SET time_zone = '+03:00'");
Removing or altering this line causes the code to fail, iterating only the first match ID and then stopping. Additionally, changing or removing:
date_default_timezone_set('Europe/Helsinki');
has no effect.
I am including the code for cron.php and utils.php, which handles database interactions, for reference. How can I fix this timezone issue while ensuring the cron job works correctly so that the timestamp is set correctly to UTC+3 (Europe/Helsinki)?
Here is the cron.php
<?php
require_once __DIR__ . '/utils.php'; // Ensure the path is relative to the current directory
$C = connect();
if (!$C) {
die("Database connection failed: " . mysqli_connect_error());
}
// Set MySQL timezone to Helsinki time
$C->query("SET time_zone = '+03:00'");
// Fetch all matches that are locked (i.e., within one hour of the start time) or already started
$matchesQuery = "
SELECT m.Match_ID, m.Home_Team, m.Guest_Team
FROM matches m
WHERE m.Match_Time <= (NOW() + INTERVAL 1 HOUR)
";
$matchesResult = sqlSelect($C, $matchesQuery);
date_default_timezone_set('Europe/Helsinki'); // Set the default timezone to Helsinki
if ($matchesResult && $matchesResult->num_rows > 0) {
while ($match = $matchesResult->fetch_assoc()) {
$matchId = $match['Match_ID'];
$homeTeam = $match['Home_Team'];
$guestTeam = $match['Guest_Team'];
// Fetch all players
$allPlayersQuery = "SELECT id, name FROM users";
$allPlayersResult = sqlSelect($C, $allPlayersQuery);
if ($allPlayersResult && $allPlayersResult->num_rows > 0) {
while ($player = $allPlayersResult->fetch_assoc()) {
$userId = $player['id'];
$username = $player['name'];
// Check if there is an existing prediction
$predictionQuery = "
SELECT Predicted_Home_Score, Predicted_Guest_Score
FROM predictions
WHERE Match_ID = ? AND User_ID = ?";
$predictionResult = sqlSelect($C, $predictionQuery, 'ii', $matchId, $userId);
if ($predictionResult && $predictionResult->num_rows > 0) {
$prediction = $predictionResult->fetch_assoc();
$homeScore = $prediction['Predicted_Home_Score'];
$guestScore = $prediction['Predicted_Guest_Score'];
if (is_null($homeScore) || is_null($guestScore)) {
$oldHomeScore = is_null($homeScore) ? 'NULL' : $homeScore;
$oldGuestScore = is_null($guestScore) ? 'NULL' : $guestScore;
$homeScore = is_null($homeScore) ? rand(0, 5) : $homeScore;
$guestScore = is_null($guestScore) ? rand(0, 5) : $guestScore;
// Update prediction to set NULL values to random values between 0 and 5
$updateScoresQuery = "
UPDATE predictions
SET Predicted_Home_Score = ?, Predicted_Guest_Score = ?
WHERE Match_ID = ? AND User_ID = ?";
sqlInsert($C, $updateScoresQuery, 'iiii', $homeScore, $guestScore, $matchId, $userId);
// Log the audit for updated prediction
$newValue = "$homeScore - $guestScore";
$notes = "Järjestelmä arpoi tuloksen $newValue pelaajalle $username otteluun $homeTeam — $guestTeam sillä pelaaja ei itse asettanut tulosta otteluun.";
$auditQuery = "INSERT INTO audits (user_id, match_id, old_value, new_value, notes) VALUES (?, ?, ?, ?, ?)";
$auditStmt = $C->prepare($auditQuery);
$oldValue = "$oldHomeScore - $oldGuestScore";
$auditStmt->bind_param('iisss', $userId, $matchId, $oldValue, $newValue, $notes);
$auditStmt->execute();
$auditStmt->close();
}
} else {
// Insert a random prediction if no prediction exists
$homeScore = rand(0, 5);
$guestScore = rand(0, 5);
$insertPredictionQuery = "
INSERT INTO predictions (Match_ID, User_ID, Predicted_Home_Score, Predicted_Guest_Score, Prediction_Time)
VALUES (?, ?, ?, ?, NOW())";
sqlInsert($C, $insertPredictionQuery, 'iiii', $matchId, $userId, $homeScore, $guestScore);
// Log the audit for new prediction
$newValue = "$homeScore - $guestScore";
$notes = "Järjestelmä arpoi tuloksen $newValue pelaajalle $username otteluun $homeTeam — $guestTeam (pelaaja ei asettanut tulosta)";
$auditQuery = "INSERT INTO audits (user_id, match_id, old_value, new_value, notes) VALUES (?, ?, NULL, ?, ?)";
$auditStmt = $C->prepare($auditQuery);
$auditStmt->bind_param('iiss', $userId, $matchId, $newValue, $notes);
$auditStmt->execute();
$auditStmt->close();
}
}
}
}
}
// Fetch the first match time
$firstMatchQuery = "SELECT MIN(Match_Time) as FirstMatchTime FROM matches";
$firstMatchResult = sqlSelect($C, $firstMatchQuery);
$firstMatchTime = $firstMatchResult->fetch_assoc()['FirstMatchTime'];
// 2. Randomize gold and silver medalists if not set one hour before the first match
if ($firstMatchTime && (strtotime($firstMatchTime) - time()) <= 3600) {
// List of teams
$teams = ['Saksa', 'Skotlanti', 'Unkari', 'Sveitsi', 'Espanja', 'Kroatia', 'Italia', 'Albania', 'Puola', 'Hollanti', 'Slovenia', 'Tanska', 'Serbia', 'Englanti', 'Romania', 'Ukraina', 'Belgia', 'Slovakia', 'Itävalta', 'Ranska', 'Turkki', 'Georgia', 'Portugali', 'Tšekki'];
// Fetch all users
$allUsersQuery = "SELECT id, name FROM users";
$allUsersResult = sqlSelect($C, $allUsersQuery);
if ($allUsersResult && $allUsersResult->num_rows > 0) {
while ($player = $allUsersResult->fetch_assoc()) {
$userId = $player['id'];
$username = $player['name'];
// Check if there is an existing medalist prediction
$medalistQuery = "
SELECT Gold_Medalist, Silver_Medalist
FROM medalists
WHERE User_ID = ?";
$medalistResult = sqlSelect($C, $medalistQuery, 'i', $userId);
$medalist = $medalistResult ? $medalistResult->fetch_assoc() : null;
$currentGold = $medalist['Gold_Medalist'] ?? null;
$currentSilver = $medalist['Silver_Medalist'] ?? null;
$randomGold = null;
$randomSilver = null;
// If either medalist is missing, assign random teams
if (is_null($currentGold) || is_null($currentSilver)) {
if (is_null($currentGold)) {
$randomGold = $teams[array_rand($teams)];
}
if (is_null($currentSilver)) {
do {
$randomSilver = $teams[array_rand($teams)];
} while ($randomSilver == $randomGold);
}
// Insert or update the medalist record
$insertMedalistQuery = "
INSERT INTO medalists (User_ID, Gold_Medalist, Silver_Medalist)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
Gold_Medalist = IF(VALUES(Gold_Medalist) IS NOT NULL, VALUES(Gold_Medalist), Gold_Medalist),
Silver_Medalist = IF(VALUES(Silver_Medalist) IS NOT NULL, VALUES(Silver_Medalist), Silver_Medalist)";
sqlInsert($C, $insertMedalistQuery, 'iss', $userId, $randomGold ?? $currentGold, $randomSilver ?? $currentSilver);
// Log the audit for medalist predictions
if (!is_null($randomGold)) {
$notes = "Järjestelmä arpoi pelaajan $username kultamitalistimaaksi $randomGold";
$auditQuery = "INSERT INTO audits (user_id, match_id, old_value, new_value, notes) VALUES (?, NULL, NULL, ?, ?)";
$auditStmt = $C->prepare($auditQuery);
$auditStmt->bind_param('iss', $userId, $randomGold, $notes);
$auditStmt->execute();
$auditStmt->close();
}
if (!is_null($randomSilver)) {
$notes = "Järjestelmä arpoi pelaajan $username hopeamitalistimaaksi $randomSilver";
$auditQuery = "INSERT INTO audits (user_id, match_id, old_value, new_value, notes) VALUES (?, NULL, NULL, ?, ?)";
$auditStmt = $C->prepare($auditQuery);
$auditStmt->bind_param('iss', $userId, $randomSilver, $notes);
$auditStmt->execute();
$auditStmt->close();
}
}
}
}
}
mysqli_close($C);
?>
and here is the utils.php
<?php
require_once 'config.php';
use PHPMailerPHPMailerPHPMailer;
use PHPMailerPHPMailerException;
require 'PHPMailer-master/src/Exception.php';
require 'PHPMailer-master/src/PHPMailer.php';
require 'PHPMailer-master/src/SMTP.php';
function debugLog($message) {
if (defined('DEBUG_MODE') && DEBUG_MODE) {
echo $message . "<br>";
}
}
function connect() {
$C = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
if ($C->connect_error) {
debugLog("Connection failed: " . $C->connect_error);
return false;
}
$C->set_charset("utf8mb4");
debugLog("Database connected successfully.");
return $C;
}
function sqlSelect($C, $query, $format = false, ...$vars) {
debugLog("Preparing query: $query");
$stmt = $C->prepare($query);
if (!$stmt) {
debugLog("Prepare failed: (" . $C->errno . ") " . $C->error);
return false;
}
if ($format) {
debugLog("Binding parameters.");
if (!$stmt->bind_param($format, ...$vars)) {
debugLog("Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error);
return false;
}
}
debugLog("Executing query.");
if (!$stmt->execute()) {
debugLog("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
$stmt->close();
return false;
}
debugLog("Getting result.");
$res = $stmt->get_result();
if (!$res) {
debugLog("Getting result set failed: (" . $stmt->errno . ") " . $stmt->error);
}
$stmt->close();
return $res;
}
function sqlInsert($C, $query, $format = false, ...$vars) {
$stmt = $C->prepare($query);
if($format) {
$stmt->bind_param($format, ...$vars);
}
if($stmt->execute()) {
$id = $stmt->insert_id;
$stmt->close();
return $id;
}
$stmt->close();
return -1;
}
function sqlUpdate($C, $query, $format = false, ...$vars) {
$stmt = $C->prepare($query);
if($format) {
$stmt->bind_param($format, ...$vars);
}
if($stmt->execute()) {
$stmt->close();
return true;
}
$stmt->close();
return false;
}
Here I finally provide the essential DB schematics:
describe audits;
describe predictions;
describe medalists;
id int(11) NO PRI NULL auto_increment
user_id bigint(20) NO MUL NULL
match_id int(11) YES MUL NULL
old_value varchar(255) YES NULL
new_value varchar(255) NO NULL
change_time timestamp NO current_timestamp() on update current_timestamp()
notes text YES NULL
Prediction_ID int(11) NO PRI NULL auto_increment
User_ID bigint(20) NO MUL NULL
Match_ID int(11) NO MUL NULL
Predicted_Home_Score int(11) YES NULL
Predicted_Guest_Score int(11) YES NULL
Prediction_Time timestamp NO current_timestamp() on update current_timestamp()
User_ID bigint(20) NO PRI NULL
Gold_Medalist varchar(255) YES NULL
Silver_Medalist varchar(255) YES NULL