Reservation system dates

Question

I have problem thinking this trough with PHP & Mysql. I have read How to implement check availability in hotel reservation system and some more but they seem different.

I have to make a reservation system on which a converence room can be booked for 6hours minimal, so when a user make a reservation on a date e.g 24/04/2012 he then selects the start time of the rental (00:00-06:00, 06:00-12:00, 12:00-18:00,18:00-24:00). And the total number of hours(6,12,18 etc..) the user wants to rent the room.

The date is stored as a integer(timestamp).

I have two problems with this; I don´t know how to receive all possible days of a month on which the room is still up for reservation and how to check if a new reservation is possible.

I know how to calculate the start date and end date on the users input but I cant find the correct mysql query or php check for availability.

It's probably really simple but somehow because the end date is always variable I just cant find the answer.

Thanks in advance guys!

EDIT Table structure: reservations

id  int(11)         
user_id int(11)     
reservation_name    varchar(255)    
start_date  int(11)         
end_date    int(11)

I believe reservations is the only one relevant

It would help to include your table structure. Edit your question and paste (between `
` tags) the output of `DESCRIBE table;` for each relevant `table` in your database. — eggyal, Apr 24 '12 at 19:18
No since there is only one, so basically what I need to figure out is if a new reservation is either before the existing ones or after the existing ones but a reservation is never the same ammount of hours — Vincent Cohen, Apr 24 '12 at 19:35
Until now I came up with the following: `SELECT * FROM reservations WHERE start_date BETWEEN '$newStartTimestamp' AND '$newEndTimestamp' OR end_date BETWEEN '$newStartTimestamp' AND '$newEndTimestamp' ` not sure if this is correct. Can anyone confirm? — Vincent Cohen, Apr 24 '12 at 20:31

score 1 · Accepted Answer · edited May 23 '17 at 11:59

You'll find it's pretty difficult to generate a list of available days in MySQL. I recommend instead that you select an ordered list of booked days within your desired month, then loop over all days of that month in PHP skipping the day if it matches the next booked day from your MySQL query. The answer to this question will help you to build the dates over which you want to loop in PHP. In pseudocode:

$booked_days = sql(select all booked days in month order by day);
for each $day in month {
   if $day != current($booked_days) {
      // $day is not booked
   } else advance_next($booked_days);
}

To check if a new reservation is possible, you might want to have a look at my answer to a very similar question earlier today.

I can't test it now because I am not at work but seeing the query you build at the question you linked to is similar to the query I mentioned in a comment above I believe you gave me the correct answer, seems logical. Thanks very much, I have been in on this for about 8 hours now. Logic seems hard sometimes.. — Vincent Cohen, Apr 24 '12 at 20:51

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

This brilliant simple MySQL query must do the trick:

Checking for overlapping car reservations

This solution is only for "dates" but You can simply aggregate it with additional "hours"

edited May 23 '17 at 11:50

Community

1
1

answered Mar 04 '14 at 09:58

Arnis Juraga

1,027
14
31

Reservation system dates

2 Answers2

Linked