Styjun

Trying to make a basic booking system where by a user can say they need a 30 minute appointment and the code can output what time slots are free to book for 30 minutes.

I first tried to ignore the "time" factor and code this by "slots" to take all the date time complexity out of it, and started with just making it work for 1 day.

I divided the day up into 15 minute slots and then created a $slotstatus of 1 for busy and 0 for free.

Then was a simple matter of looking through the table rows to find free slots and echo them.

The problem I have is when the time required takes more than 1 slot. SO I then need to count the amount of "Slotstatus = 0" and ensure there is enough available time for the requirement.

I tried using a count ++ when I see a 0 but then had problems on the areas where I needed to output two slots in sequence.

Im very amateur at code and im doing this as a bit of a hobby to boost my coding logic brain which is poor.

Please can someone point me in the right direction

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 $count = 0;
 $req = 3;
 echo $req . " is the SlotReq<br>";
 echo $count . " is the starting count<br><br>";




$sql = "SELECT slotnumber, slottime, slotstatus FROM timeslots";
$result = $conn->query($sql);


if ($result->num_rows > 0) 
 
 // output data of each row
 while($row = $result->fetch_assoc() ) 
 
 if ($row["slotstatus"] == 0)
 
 $count ++;

 if ( $count == $req)
 


 echo "<br>Pass for " . $row["slottime"] . " 
 Status: ". $row["slotstatus"] . " Count " . $count;


 $count = 0; 
 
 else 
 
 echo "<br> False for " . $row["slottime"] . " 
 Status: ". $row["slotstatus"] . " Count " . $count; 



 
 
 else
 
 echo "<br> False for " . $row["slottime"] . " 
 Status: ". $row["slotstatus"] . " Count " . $count;
 $count = 0;

 
 

 
 else 
 
 echo "Error: No results Found";
 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

should look like this on the page

Table
table as image
ID slottime slotstatus
1 10:15 0
2 10:30 1
3 10:45 0
4 11:00 0
5 11:15 0
6 11:30 0
7 11:45 1

So if the user wants a 30 minute slot they should be presented with the options based on the table above as

10:45
11:00
11:15

You can use a query like this. It count first the free slots and the you
can query the requested slots in the outer SELECT:

SELECT * from (
SELECT ts.*
 , @sclotcount := IF( ts.`slotstate` = 1, 0, @sclotcount +1 ) as freeslots
 FROM timeslots ts
 CROSS JOIN ( SELECT @sclotcount := 0) as init
 ORDER by id DESC
) as result
WHERE freeslots >= 3 -- request slots
ORDER by id;

SAMPLE

MariaDB [test]> SELECT * from ( SELECT ts.*, @sclotcount := IF( ts.`slotstate` = 1, 0, @sclotcount +1 ) as freeslots FROM timeslots ts CROSS JOIN ( SELECT @sclotcount := 0) as init ORDER by id DESC ) as result WHERE freeslots >= 3 ORDER by id;
+----+----------+-----------+-----------+
| id | slottime | slotstate | freeslots |
+----+----------+-----------+-----------+
| 1 | 00:00:00 | 0 | 3 |
| 10 | 02:15:00 | 0 | 3 |
| 14 | 03:15:00 | 0 | 7 |
| 15 | 03:30:00 | 0 | 6 |
| 16 | 03:45:00 | 0 | 5 |
| 17 | 04:00:00 | 0 | 4 |
| 18 | 04:15:00 | 0 | 3 |
| 22 | 05:15:00 | 0 | 75 |
| 23 | 05:30:00 | 0 | 74 |
| 24 | 05:45:00 | 0 | 73 |
...
| 83 | 20:30:00 | 0 | 14 |
| 84 | 20:45:00 | 0 | 13 |
| 85 | 21:00:00 | 0 | 12 |
| 86 | 21:15:00 | 0 | 11 |
| 87 | 21:30:00 | 0 | 10 |
| 88 | 21:45:00 | 0 | 9 |
| 89 | 22:00:00 | 0 | 8 |
| 90 | 22:15:00 | 0 | 7 |
| 91 | 22:30:00 | 0 | 6 |
| 92 | 22:45:00 | 0 | 5 |
| 93 | 23:00:00 | 0 | 4 |
| 94 | 23:15:00 | 0 | 3 |
+----+----------+-----------+-----------+
80 rows in set (0.001 sec)

MariaDB [test]>