CODEWITHSUNDEEP
mysql
Terry Carter
Terry Carter

Reputation: 300

Item exclusions by date in MySQL

I have a very simple reservations system I have built for an RV Park. It only has 8 RV parking spaces in it. The issue I am having is it seems the available RV spots for reservation is spotty at best. Each spot has a check out time of 11am and a check in time of 2pm similar to a hotel.

Effectively a user hits the site, choose an arrival and departure date and clicks search. A list then shows up from an ajax query that shows all 8 RV spots and whether or not they are available for reservation. Apparently I have something effed up in my query that is not taking into consideration whether the arrival date falls into a range of dates for another reservation.

I have read a couple of other similar but not the same questions on SE and none of the proposed solutions fixed what I needed since I am not combining any other tables or keys. This is a simple 1 table system that is basically just a record of the registrations.

$sql = "SELECT id AS rid, sid, paid, arriveDate, departDate FROM saved_reservations
    WHERE 
        arriveDate BETWEEN ".$db->quote($formattedArrival.' 14:00:00')." AND ".$db->quote($formattedDeparture.' 11:00:00')."
    OR 
        departDate BETWEEN ".$db->quote($formattedArrival.' 14:00:00')." AND ".$db->quote($formattedDeparture.' 11:00:00')."
";
$sql .= " AND paid = 1 ORDER BY arriveDate ASC";

sid is the ID of the specific RV site. Just a numeric value 1-8.

I think where I am going wrong is my system is not picking up reservations from before the submitted arrival date but I am not 100% sure. Any ideas or suggestions for this query?

Here is my table structure:

CREATE TABLE `saved_reservations` (
  `id` int(4) NOT NULL,
  `sid` int(2) NOT NULL,
  `arriveDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `departDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `paid` tinyint(1) NOT NULL DEFAULT '0',
  `amount` int(4) NOT NULL,
  `payment_date` int(15) NOT NULL,
  `txn_id` varchar(100) NOT NULL,
  `first_name` varchar(65) NOT NULL,
  `last_name` varchar(65) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Saved Reservations';

Upvotes: 1

Views: 65

Answers (1)

Yevgen
Yevgen

Reputation: 1667

You query does not take into consideration a case when existing reservation is within desired reservation interval.

Anyway, here is my take on your problem, given user inputs are arriveDate and departDate the logic should be as follows.

# find unavailable sports for provided period    
select sid from reservations R where arriveDate < R.departDate and departDate > R.arriveDate
    
# find available spots for provided period
select sid from reservations R where not (arriveDate < R.departDate and departDate > R.arriveDate)

SQL Fiddle

Upvotes: 1

Related Questions