Jump to content

Avoid appointment conflict


phppup
 Share

Go to solution Solved by mac_gyver,

Recommended Posts

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)?

 

Link to comment
Share on other sites

  • Solution

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.

  • Like 1
Link to comment
Share on other sites

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 |
+---------+---------------+       +------------+--------------+---------+--------+

image.png.239a9554d8b4e083cb54294c8213b7c8.png

 

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.

Link to comment
Share on other sites

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 by phppup
Forgot item
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.