I have a script that allows me to automatically generate slots on a calendar according to the different opening hours of my users.
Today, I would like to improve the execution of my script to make it much faster than today (it takes a few seconds to make the calendar appear).
You have to know that I have a table OPENING_TIMES which contains 7 rows (1 per day) for each user.
I tried some improvements but without success (with joins for example).
Do you have any ideas?
Thanks in advance !
$Sch_Link_User = $_GET['psy'];
$Data_query = mysqli_query($_Db_Con, 'SELECT ID_UNIQUE, CONSULTATION_TIME, CONSULTATION_TIME_BEFORE, TIMEZONE FROM USERS WHERE PROFILE_URL LIKE "' . $Sch_Link_User . '"');
$Data = mysqli_fetch_assoc($Data_query);
$Sch_Id_User = $Data['ID_UNIQUE'];
$Difference = '0';
switch ($Data['CONSULTATION_TIME']) {
case 1:
$Sch_Time_Consultation = 30;
break;
case 2:
$Sch_Time_Consultation = 45;
break;
case 3:
$Sch_Time_Consultation = 60;
break;
default:
$Sch_Time_Consultation = 60;
}
$Calendar = array();
$weekslots = array_map(function($dow) {return array('dow' => $dow, 'hour' => 00);}, range(0, 6));
$date = time();
$end_date = $date + (6 * 7 * 24 * 60 * 60);
$open_hours_cache = array();
while($date <= $end_date) {
$date_dow = date('w', $date);
foreach($weekslots as $timeslot) {
if($date_dow == $timeslot['dow']) {
$timeofday = $date + (3600 * $timeslot['hour']);
for($nd = 1; $nd <= 7; $nd++) {
if(date('N', $timeofday) == $nd) {
if (!isset($open_hours_cache[$nd])) {
$Data_Openhours_query = mysqli_query($_Db_Con, 'SELECT START_HOUR_M, END_HOUR_M, START_HOUR_A, END_HOUR_A FROM OPENING_TIMES WHERE ID_USER = "' . $Sch_Id_User . '" AND WEEKDAY = "' . $nd . '" AND CLOSED = 0');
$open_hours_cache[$nd] = mysqli_fetch_assoc($Data_Openhours_query);
}
$Data_Openhours = $open_hours_cache[$nd];
if(isset($Data_Openhours['START_HOUR_M']) && $Data_Openhours['END_HOUR_M'] == NULL && $Data_Openhours['START_HOUR_A'] == NULL && isset($Data_Openhours['END_HOUR_A'])) {
$shm = strtotime($Data_Openhours['START_HOUR_M'] . $Difference . ' hour');
$eha = strtotime($Data_Openhours['END_HOUR_A'] . $Difference . ' hour');
$timeslots = array();
$interval = new DateInterval("PT{$Sch_Time_Consultation}M");
$timeRange = new DatePeriod(new DateTime(date('Y-m-d H:i', $shm)), $interval, new DateTime(date('Y-m-d H:i', $eha)));
foreach ($timeRange as $time) {
$timeslots[] = $time->format("H:i");
}
foreach ($timeslots as $slot) {
$datefinal = date('Y-m-d', $timeofday) . ' ' . $slot . ':00';
$timestamp = strtotime($datefinal . $Difference . ' hour');
$Check_Slot = mysqli_query($_Db_Con, 'SELECT ID, ID_USER, STATUS FROM APPOINTMENTS WHERE ID_USER = "' . $Sch_Id_User . '" AND DATE_START = "' . $timestamp . '" AND (STATUS = 1 OR STATUS = 2 OR STATUS = 9 OR STATUS = 10) AND DATE_START > "' . $Time . '"');
$Data_Slot = mysqli_fetch_assoc($Check_Slot);
$status = isset($Data_Slot['STATUS']) && $Data_Slot['STATUS'] == 9 && $Data_Slot['ID_USER'] == $_SESSION['uid'];
$check_slot = mysqli_num_rows($Check_Slot) == 0;
$time_before = ($Time + (60 * 60 * $Data['CONSULTATION_TIME_BEFORE'])) < $timestamp;
if ($status) {
$class = 'blocked';
} elseif ($check_slot && $time_before) {
$class = 'available';
} else {
$class = 'unavailable';
}
$Calendar[] = array(
'id' => date('Y-m-d', $timeofday) . '/' . $slot,
'title' => $slot,
'class' => $class,
'psy' => $Sch_Id_User,
'start' => $timestamp . '000',
);
}
} else if(isset($Data_Openhours['START_HOUR_M']) && isset($Data_Openhours['END_HOUR_M']) && isset($Data_Openhours['START_HOUR_A']) && isset($Data_Openhours['END_HOUR_A'])) {
$shm = strtotime($Data_Openhours['START_HOUR_M'] . $Difference . ' hour');
$ehm = strtotime($Data_Openhours['END_HOUR_M'] . $Difference . ' hour');
$sha = strtotime($Data_Openhours['START_HOUR_A'] . $Difference . ' hour');
$eha = strtotime($Data_Openhours['END_HOUR_A'] . $Difference . ' hour');
$timeslots = array();
$interval = new DateInterval("PT{$Sch_Time_Consultation}M");
$timeRange = new DatePeriod(new DateTime(date('Y-m-d H:i', $shm)), $interval, new DateTime(date('Y-m-d H:i', $ehm)));
foreach ($timeRange as $time) {
$timeslots[] = $time->format("H:i");
}
$timeRange = new DatePeriod(new DateTime(date('Y-m-d H:i', $sha)), $interval, new DateTime(date('Y-m-d H:i', $eha)));
foreach ($timeRange as $time) {
$timeslots[] = $time->format("H:i");
}
foreach ($timeslots as $slot) {
$datefinal = date('Y-m-d', $timeofday) . ' ' . $slot . ':00';
$Check_Slot = mysqli_query($_Db_Con, 'SELECT ID, ID_USER, STATUS FROM APPOINTMENTS WHERE ID_USER = "' . $Sch_Id_User . '" AND DATE_START = "' . strtotime($datefinal) . '" AND (STATUS = 1 OR STATUS = 2 OR STATUS = 9 OR STATUS = 10) AND DATE_START > "' . $Time . '"');
$Data_Slot = mysqli_fetch_assoc($Check_Slot);
// * 24 le temps avant possibilité de prende rendez-vous
$status = isset($Data_Slot['STATUS']) && $Data_Slot['STATUS'] == 9 && $Data_Slot['ID_USER'] == $_SESSION['uid'];
$check_slot = mysqli_num_rows($Check_Slot) == 0;
$time_before = ($Time + (60 * 60 * $Data['CONSULTATION_TIME_BEFORE'])) < strtotime($datefinal . $Difference . ' hour');
if ($status) {
$class = 'blocked';
} elseif ($check_slot && $time_before) {
$class = 'available';
} else {
$class = 'unavailable';
}
$Calendar[] = array(
'id' => date('Y-m-d', $timeofday) . '/' . $slot,
'title' => $slot,
'class' => $class,
'psy' => $Sch_Id_User,
'start' => strtotime($datefinal . $Difference . ' hour') . '000',
);
}
}
}
}
}
}
$date += 86400;
}
$Calendar_Data = array(
'success' => 1,
'result' => $Calendar);
echo json_encode($Calendar_Data);
So I try to have a much faster loading with joins or a cache system to limit the number of requests.
Moreover, wouldn't it be more efficient if I also put everything in one column of my user table, the schedules?
