3

I'm trying to create a computer reservation system, where user chooses a computer and select the time how long he will be using this PC. In that time other persons can't reserve this pc, I need to find a solution, how to automaticaly delete all rows containing reserved pc's after their time expires. Thank you for the advice.

Jonathan
  • 585
  • 7
  • 27
Osvalda Kazlaučiūnaitė
  • 165
  • 1
  • 3
  • 12

5 Answers5

5

The common way to handle this is to store an expires_at timestamp on the reservation row. Then your query to find any "open" reservations would have WHERE 'expires_at' < NOW() or something similar.

MDrollette
  • 6,887
  • 1
  • 36
  • 49
4

If your server is linux, you can use cron jobs to check once a day every reservation dates. If these dates have expired .. modified field reserves to be available.

csotelo
  • 1,453
  • 2
  • 28
  • 43
4

This is an untested answer, that may only be a suggestion, but I just started looking at these, so am interested in feedback as well. i'm still working through possibilities and drawbacks, but it might well suit your need.

Take a look at MySQL Events, an article about it is here, and official syntax at Mysql Docs.
Per the article:

An event is similar to a trigger. However, rather than running in response to a data change, events can be scheduled to run any number of times during a specific period. In effect, it’s a database-only cron job.

Pondering this, I'd envision a procedure that deleted anything >1hr (if that's the expiration). This procedure would be TRIGGERED on new inserts to get rid of anything expired at that moment, but also in an event to run every 15 minutes or so so that automatic deletes by the trigger aren't dependant on somebody else adding a reservation to trigger that procedure.

GDP
  • 8,109
  • 6
  • 45
  • 82
  • If you want to actually delete the rows, using an event that calls a procedure containing your `delete from ... where ...` should work really well. Just schedule the event to go off often enough. On the other hand, if you use a start and end time like some of the other answers, you keep records of all the reservations. Depends on what you need. – Windle Jun 14 '12 at 18:45
3

Normally I would do it this way:

  • when storing a reservation, store date_from and date_to both of datatype DATETIME
  • when checking if there is a computer free check for all computers and filter with WHERE '{$my_date}' >= date_to AND '{$my_date}' <= date_from - by this You should be able to get all the PCs that are not reserved within a certain time...
shadyyx
  • 15,825
  • 6
  • 60
  • 95
  • 2
    I wrote the reservation system for our hotel, and this is how I do it too, only using dates instead of `DATETIME` since we don't sell rooms by the hour. – Mike Jun 14 '12 at 18:12
1

To be complete in the solution, you need to run a CRON job which calls a query to remove all reservations that have a reservation_time + (15 * 60) < unix_timestamp().

I am assuming you have a time that the reservation was placed or started and are using UNIX/Epoch Timestamps.

Instead of doing a expires_now, if you know it will always be a fixed interval ie 15 minutes, you can do:

DELETE FROM reservations WHERE  reservation_time + (15 * 60) < unix_timestamp()

Something you could look into is managing cron job's from PHP, http://www.highonphp.com/cron-job-manager.

The above script will, when a reservation is created, insert an entry into /etc/cron.d/ and you could configure it to run at the expected reservation endtime. Then inside the php file which would be executed, you could do:

DELETE FROM reservations WHERE id = :id
Mike Mackintosh
  • 13,917
  • 6
  • 60
  • 87