Please i want to create a php code to check if time has expired or still active based on the start date and end time that users specified. Below is the database structure and sample php code which i tried.
game_config
game_id | start_date | start_t | start_h | end_time | end_h | status
--------|------------|---------|---------|----------|-------|---------
100 | 03/26/2018 | 10:45 | PM | 12:30 | AM | 0
101 | 03/27/2018 | 09:23 | AM | 11:10 | AM | 0
Php code sample
<?php
$conf_execution_date = date('m/d/Y'); /*Current date*/
$conf_execution_meridiem = date('A'); /*Current meridiem*/
$conf_execution_timer = date('h:m'); /*Current time*/
$conf_execution_proccess = false; /*Proceed or not*/
$conf_handler = $appconn->prepare("
SELECT * FROM game_config WHERE game_id = :game_id AND start_date = :start_dateAND start_t = :start_t AND status = 0 LIMIT 1
");
$conf_handler->bindParam(":game_id", 100);
$conf_handler->bindParam(":start_date", $conf_execution_date);
$conf_handler->bindParam(":start_t", $conf_execution_meridiem);
$conf_handler->execute();
$appconf = $conf_handler->fetch(PDO::FETCH_OBJ);
if($appconf){
$config_start_time = strtotime($appconf->start_t);
$config_end_time = strtotime($appconf->end_time);
$current_time = strtotime($conf_execution_timer);
if($appconf->start_h == $conf_execution_meridiem){ /*AM-PM*/
if($config_start_time >= $current_time){ /*Check if the start time is now o still active*/
$conf_execution_proccess = true;
}
}
if($appconf->end_h == $conf_execution_meridiem){ /*AM-PM*/
if($config_end_time >= $current_time){ /*Check if the time has ended*/
$conf_execution_proccess = false;
}
}
if($conf_execution_proccess == true){
echo 'YES THE GAME IS STILL ACTIVE';
}else{
echo "NO GAME HAS ENDED";
}
}
?>
Assuming your start_date column is type VARCHAR():
Assuming your end_time is always later in the day than your start_time:
You can use this sort of query to get a starting and ending DATETIME value for each row of your table. It glues together the date, the time, and the AM/PM fields and converts them to DATETIME. (http://sqlfiddle.com/#!9/2d84ea/1/0)
SELECT game_id,
STR_TO_DATE(CONCAT(start_date,'-',start_t,start_h), '%m/%d/%Y-%h:%i%p') start_ts,
STR_TO_DATE(CONCAT(start_date,'-',end_time,end_h), '%m/%d/%Y-%h:%i%p') end_ts,
status
FROM game_config
Then you can compare those start_ts and end_ts values to NOW() by appending this line to your query
Then you can fill out your query with the criteria you need, giving this query.
SELECT game_id,
STR_TO_DATE(CONCAT(start_date,'-',start_t,start_h), '%m/%d/%Y-%h:%i%p') start_ts,
STR_TO_DATE(CONCAT(start_date,'-',end_time,end_h), '%m/%d/%Y-%h:%i%p') end_ts,
status
FROM game_config
WHERE game_id = : gameid AND status = 0
HAVING start_ts <= NOW() and NOW() < end_ts
LIMIT 1
But, your start and end times sometimes span midnight, which means your end_ts needs to be on the next day. That makes your date-checking logic quite a bit trickier. You'll have to do some sort of conditional expression in which you do + INTERVAL 1 DAY to your end_ts if it is before your start_ts.
You will be much better off (@eggyal pointed this out in his comment) if you put the following columns in your table.
game_id int
start_ts timestamp of start
end_ts timestamp of end
status int
Then you don't have to worry about all the stuff about making a proper timestamp from three different columns, and you don't have to do anything special when the start and end times span midnight.
And, this will work intuitively no matter what timezone your users are in.