0

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

berlinaise
  • 1
  • 1

1 Answers1

0

  1. In your db your status table add a datetime field.

  2. When someone lock a slot you also save the current time using NOW()

  3. 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;
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • Wow. That was fast. Do I understand correctly, that "INTERVAL 15 MINUTE" is a setting I can change according to my needs? And can you explain, what you mean by "When someone consults the slots"? Thanks! – berlinaise Apr 05 '17 at 18:21
  • probably also need to filter on a specific id or something too. – Jonathan Apr 05 '17 at 18:24
  • Yes, `15 minutes` can be `1 hour` Check [**HERE**](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html) for all the options. – Juan Carlos Oropeza Apr 05 '17 at 18:34
  • My guess is you go to db and check for available slots to show the user? So before check what slots are free, you update the table to unlock the inactive ones. – Juan Carlos Oropeza Apr 05 '17 at 18:35
  • Basically, what you are saying is to update the status of all rows as soon as any user sends a new request. That makes sense. I hadn't thought of that as a "clean-up" trigger. Very cool. I will try this! Thanks so much :) – berlinaise Apr 05 '17 at 18:35
  • Yes. Of course you also need a data binding between all users page and the db. So if someone lock an slot also get locked for other users. But that is a different issue. – Juan Carlos Oropeza Apr 05 '17 at 18:38
  • Well, that happens on the same first request. The user first selects the artist and number of slots to book. When he sends the request, I would now first run the above update and then select the next free slots. Those would also be updated in the same step to status="locked" so they won't be available for other users until released again (if not booked). That's the idea... – berlinaise Apr 05 '17 at 18:46
  • I mean if an userA have a list of `{1,2,3}` and userB free slot 4, you need a way so userA get updated to `{1,2,3,4}` I would `AngularJs` to have a data component. – Juan Carlos Oropeza Apr 05 '17 at 18:49
  • I'll look into it. Though I think, in this specific scenario with a max of 2 slots available anyway, I might be able to get away with it. – berlinaise Apr 05 '17 at 18:57