For an art project I am trying to set up an order site. The concept allows users to book a max. of two time slots with each artist. There are 12 slots per artist, but each slot with a specific definition (so each is unique). The slots are only available for a very limited time and hopefully booked fast. So there will be a lot of requests in a short period of time. I have to make sure each article/slot is only offered to a single user at a time and cannot be double booked.
My idea was, to check for the next unbooked slot(s) (status="free) and on that request update the status of the corresponding row in the table to status="locked". If the user proceeds to actually book the slot, the status is updated to "booked".
If a user clicks "cancel" I can release the article by updating the row to status="free".
However, it is not unlikely that users just abandon the site and I don't see a way to check for that. The slot would remain "locked". I was thinking, there might be a way to automatically reset the status e.g. 120 seconds after is was "locked" and show a countdown to the users. This could even enhance the excitement factor.
I don't think a cron job would work as I need the anchor to be the last update of the row and not a specific datetime.
I looked into MySQL events but understood that I cannot manipulate the data of the table it is attached to.
I would greatly appreciate any ideas. Thanks, Sam
In your db your status table add a datetime field.
When someone lock a slot you also save the current time using NOW()
When someone consult the slots you perform and update and free the inactive slots
Update slots
SET locked = false
WHERE `datetime`> NOW() - INTERVAL 15 MINUTE;
SELECT *
FROM slots
WHERE locked = false;