Hi,
I have a simple “bookings/reservation” MySQL database table (tblbookings). There are four fields in the table:
bookId (auto increment unique ID)
bookdate (Date - YYYY-MM-DD)
bookrm (INT) (this is the room number booked on that date)
bookorderId (this is a relational field that lists the full booking/order details in another table).
Typically someone may book a particular room for 3 nights and another user may book a different room for some other nights, so the table data would look like this:
bookId - bookdate - bookrm - bookorderId
1000 - 2015-08-21 - 1 - 564
1001 - 2015-08-22 - 1 - 564
1002 - 2015-08-23 - 1 - 564
1003 - 2015-08-22 - 2 - 565
1004 - 2015-08-23 - 2 - 565
1005 - 2015-08-24 - 2 - 565
This all works fine and I can use it to check whether a room is available on a certain day (i.e. if it’s not in the table).
However due to there being another (not mine) way of adding records occasionally a double booking may occur - where the same room (bookrm) is listed for more than one order (bookorderId) on a particular date (bookdate).
Therefore I’m trying to create an alert for the administrator that will query the table to check whether any rooms are listed more than once on the same day - i.e.
bookId - bookdate - bookrm - bookorderId
1000 - 2015-08-21 - 1 - 564
1001 - 2015-08-22 - 1 - 564
1002 - 2015-08-23 - 1 - 564
1003 - 2015-08-23 - 1 - 565
1004 - 2015-08-24 - 1 - 565
1005 - 2015-08-25 - 1 - 565
The result would be a recorsset that gave me the bookorderId, bookdate and bookrm of the clashed bookings (only one side of the clash would need to be outputted if that’s easier). To be honest I haven’t a clue how to construct a SELECT statement that can do this - can anyone help?
Many thanks
Scim