How to query MySQL database for each day in a date range?

Question

I have these three tables:

hotel
hotel_id
hotel_name

room
room_id
hotel_id

room_price
room_id
date
price

I know how to query the database to get the price for a given room_id and given date for the hotel stay. So I can do it, however, when it comes to doing this for a long date range, this becomes impractical.

Answer 1

You could either create your date series dynamically, as shown in the answer suggested by Bagus Tesa, or maintain a calendar table. Given that dates play a big part in booking/reservations apps, I would suggest creating a simple calendar table.

For this example I am using a calendar table with just a single dt (DATE) column, which you can easily populate with a stored proc (or any other method you choose):

CREATE TABLE calendar (dt DATE NOT NULL PRIMARY KEY);

DELIMITER $$
CREATE PROCEDURE `filldates`(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
    INSERT INTO calendar (dt) VALUES (dateStart);
    SET dateStart = dateStart + INTERVAL 1 DAY;
  END WHILE;
END$$
DELIMITER ;

CALL filldates('2023-01-01', '2039-12-31');

Then it is a simple case of a CROSS JOIN between the dates for the booking and rooms, and a correlated subquery to retrieve the price per room per day:

SET @checkin = '2023-11-26', @checkout = '2023-12-15';

SELECT r.room_id, COUNT(*) AS nights, (
    SELECT price
    FROM room_price
    WHERE room_id = r.room_id
    AND start_date <= c.dt
    ORDER BY start_date DESC
    LIMIT 1
) AS price
FROM calendar c
CROSS JOIN room r
WHERE c.dt >= @checkin AND c.dt < @checkout
GROUP BY room_id, price;

Note: although the keyword CROSS has no significance in MySQL (it's just an INNER join with no ON clause), I think it makes the intent clear.

Here's a db<>fiddle.