Avoid appointment conflict

[phppup]

As a rudimentary example, I want to allow users to set an appointment.

I'm considering a drop-down that will display 1,2,3, and 4 o'clock.

There are 2 appointment slots available each hour.

After the 2nd appointment is filled for a specific time, the option will be removed from the drop-down selection.

I've essentially gotten the necessary mechanics figured out and plan to eliminate a timeslot when a SELECT returns 2 as the number of current appointments for a specified time.

My question is: How can I ensure that additional appointments are not set from the time that the first appointment refreshes the drop-down to the time the option is removed.

I realize this is a thin possibility, but theoretically, 10 users could have the opportunity to grab the second slot. If they all hit enter simultaneously, how can I prevent an overloaded schedule (since the time itself is not unique)?

 

[Barand]

Set a UNIQUE constraint on the appointment datetime in your database table. Validate that all the time are on the hour and half-hour.

[mac_gyver]

the following is a 'tricky' example of INSERTing data that satisfies a maximum count of rows -

	$query = "INSERT INTO team_members (team_id, staff_id, stafftype)
	SELECT -- the following values being SELECTed are the actual data values to insert
	?,?,? FROM DUAL -- dual is an allowed dummy table name to satisfy the FROM ... WHERE syntax
	WHERE (SELECT COUNT(*) FROM team_members WHERE team_id = ? AND stafftype='leader') < 1 -- insert the data if the WHERE (subquery count) < 1 is TRUE";
	$stmt = $pdo->prepare($query);
	$stmt->execute([$team_id, $staff_id, $stafftype, $team_id]);
	
	if($stmt->rowcount())
	{
		echo "A leader row was inserted for team_id: $team_id, staff_id: $staff_id<br>";
		} else {
		echo "A leader already exists for team_id: $team_id<br>";
	}

this example was to insert a maximum of one 'leader' row per team id. you would change it to insert a maximum of two rows per datetime appointment slot.

because this uses a single query to both get a count of the number of existing rows and insert a new row, it will work correctly for multiple concurrent instances of your script.

[Barand]

I tried out @mac_gyver's excellent idea using these tables...

TABLE: booking_slot;              TABLE: booking;
+---------+---------------+       +------------+--------------+---------+--------+      CREATE TABLE `booking` (
| slot_no | slot_times    |       | booking_id | booking_date | slot_no | userid |        `booking_id` int(11) NOT NULL AUTO_INCREMENT,
+---------+---------------+       +------------+--------------+---------+--------+        `booking_date` date DEFAULT NULL,
|       9 | 09:00 - 10:00 |       |          1 | 2022-11-14   |      10 |    101 |        `slot_no` int(11) DEFAULT NULL,
|      10 | 10:00 - 11:00 |       |          2 | 2022-11-14   |      10 |    102 |        `userid` int(11) DEFAULT NULL,
|      11 | 11:00 - 12:00 |       |          3 | 2022-11-14   |      11 |    103 |        PRIMARY KEY (`booking_id`),
|      12 | 12:00 - 13:00 |       |          7 | 2022-11-14   |      11 |    104 |        UNIQUE KEY `unq_booking_1` (`booking_date`,`slot_no`,`userid`),
|      13 | 13:00 - 14:00 |       |          8 | 2022-11-14   |      12 |    105 |        KEY `idx_booking_slot_no` (`slot_no`),
|      14 | 14:00 - 15:00 |       |          5 | 2022-11-15   |      11 |    103 |        KEY `idx_booking_userid` (`userid`)
|      15 | 15:00 - 16:00 |       |         10 | 2022-11-15   |      11 |    107 |      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|      16 | 16:00 - 17:00 |       |          9 | 2022-11-15   |      12 |    106 |
|      17 | 17:00 - 18:00 |       |         11 | 2022-11-16   |       9 |    101 |
+---------+---------------+       +------------+--------------+---------+--------+

 

On experimenting, it appears that "FROM DUAL" is optional as this worked fine

        $stmt = $pdo->prepare("INSERT IGNORE INTO booking (booking_date, slot_no, userid)
                                   SELECT ?, ?, ? 
                                   WHERE (SELECT COUNT(*) FROM booking WHERE booking_date = ? AND slot_no = ?) < 2
        ");
        $stmt->execute([ $date, $slot, $user, $date, $slot ]);
        if ($stmt->rowCount()==0) {
            $error = "Booking was unsuccessful";
        }

The unique key in the booking table prevents a single user booking both places in a timeslot.

[phppup]

Thanks @Barand. I hadn't seen the update until now and had been thinking about how to make the appointment time unique (since all must begin on the hour).

I was considering querying the table in a manner such as

//for a rough example in a given week
SELECT timeslot from APPT where day=Thursday

//get the data into a useable variable 

$a = "9AM";
if (exists ($a)){
$a = $a."_2";
if (exists ($a)){
 //set appointment as a unique string 9AM_2

}else{
echo "pick a different time";

Not sure if this approach would be reasonably effective.

Opinion??

It about the need for an additional table, but I will try to understand @mac_gyver example and see if I can get it to work for me too.

 

Is a TRY/CATCH necessary either way?

Edited November 17, 2022 by phppup
Forgot item

[Barand]

I don't see your problem. I have given you a solution using @mac_gyver's example.