RE: [PHP] Reservation technique
How will a PHP script perform such checking to prevent that sort of overlapping in reservation ? Or could it be that my database design is bad that's blocking ideas into my head ? Hi Roger, I think you need to check out the BETWEEN operator in MySQL (assuming you're using MySQL? Look for the equivalent operator in whatever db platform you're using, if otherwise). Essentially what you're looking to do is find out if either the start time or the end time of the new booking falls BETWEEN the start time or the end time of an existing booking. If either of them does, it's safe to assume there's a booking conflict. Much warmth, Murray http://www.planetthoughtful.org Building a thoughtful planet, One quirky comment at a time. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Reservation technique
Great! Thanks Murray. -- roger Quoting Murray @ PlanetThoughtful [EMAIL PROTECTED]: How will a PHP script perform such checking to prevent that sort of overlapping in reservation ? Or could it be that my database design is bad that's blocking ideas into my head ? Hi Roger, I think you need to check out the BETWEEN operator in MySQL (assuming you're using MySQL? Look for the equivalent operator in whatever db platform you're using, if otherwise). Essentially what you're looking to do is find out if either the start time or the end time of the new booking falls BETWEEN the start time or the end time of an existing booking. If either of them does, it's safe to assume there's a booking conflict. Much warmth, Murray http://www.planetthoughtful.org Building a thoughtful planet, One quirky comment at a time. --- Sign Up for free Email at http://ureg.home.net.my/ --- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Reservation technique
Great! Thanks Murray. Hi Roger, On second thoughts, perhaps the BETWEEN operator isn't what you're looking for, since it will probably return false matches on bordering reservations. In other words, someone attempting to make a reservation between 10am and 12pm might be told there's a conflicting reservation if there is already a reservation for between, for example, 12pm and 2pm on the same day. The end time of 12pm on the new reservation will return a match for the start time of 12pm on the existing reservation using the BETWEEN operator. As an alternative, you might try something like: SELECT * FROM reservation_table WHERE (new_start_datetime reservation_start_datetime AND new_start_datetime reservation_end_datetime) OR (new_end_datetime reservation_start_datetime AND new_end_datetime reservation_end_datetime) This should exclude false matches on bordering reservations. Much warmth, Murray http://www.planetthoughtful.org Building a thoughtful planet, One quirky comment at a time. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Reservation technique
Yes. I flipped thru the manpages and found: expr BETWEEN min AND max If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min = expr AND expr = max) if all the arguments are of the same type. Thanks again. -- roger Quoting Murray @ PlanetThoughtful [EMAIL PROTECTED]: Great! Thanks Murray. Hi Roger, On second thoughts, perhaps the BETWEEN operator isn't what you're looking for, since it will probably return false matches on bordering reservations. In other words, someone attempting to make a reservation between 10am and 12pm might be told there's a conflicting reservation if there is already a reservation for between, for example, 12pm and 2pm on the same day. The end time of 12pm on the new reservation will return a match for the start time of 12pm on the existing reservation using the BETWEEN operator. As an alternative, you might try something like: SELECT * FROM reservation_table WHERE (new_start_datetime reservation_start_datetime AND new_start_datetime reservation_end_datetime) OR (new_end_datetime reservation_start_datetime AND new_end_datetime reservation_end_datetime) This should exclude false matches on bordering reservations. Much warmth, Murray http://www.planetthoughtful.org Building a thoughtful planet, One quirky comment at a time. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php --- Sign Up for free Email at http://ureg.home.net.my/ --- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php