How to get rooms availabel between checkin datetime and checkout datetime group by room_types

Need Help
1

Hi, i am new in cakephp, and using CakePHP 4.3 and PHP 7.2.34 i want to calculate number of rooms availabel between checkin datetime and checkout datetime. group by room_type lets say
$checkin = ‘2023-07-18 20:00:00’ and $checkout = ‘2023-07-20 20:00:00’ $room_type_id =1;
checkout time is 24 hr…

I have four tables.

  1. bookings

booking table
booking table1434×161 17.6 KB

  1. booking_details

booking_details
booking_details1209×243 29.2 KB

  1. rooms

rooms
rooms1627×514 51.4 KB

  1. room_types

room_types
room_types958×411 57.2 KB

  1. reservations
    reservations

get available rooms form filed is checkin, checkout and room_type_id

i try a sql query

SELECT rooms.id, rooms.uuid, rooms.name, rooms.dharmshala_id, rooms.room_type.name, rooms.available_room_online - ( SELECT IFNULL(sum(booking_details.quantity), 0)
FROM booking_details WHERE rooms.id = booking_details.room_id
AND booking_details.checkin_date >= ‘.$checkin.’ AND booking_details.checkin_date <= ‘.$checkout.’
) AS remaining FROM rooms

and get result

result

but when i search with datetime, error show
help me
thanks

2

What error, specifically? And how, specifically, does your “error” code differ from your “get a result” code?

3

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘20:00:00 AND booking_details.checkin_date <= 2023-07-22 20:00:00 …’ at line 3

4

my sql query with datetime

SELECT rooms.id, rooms.uuid, rooms.name, rooms.dharmshala_id, rooms.room_type_id, rooms.available_room_online - ( SELECT IFNULL(sum(booking_details.quantity), 0)
FROM booking_details WHERE rooms.id = booking_details.room_id AND rooms.room_type_id = 6
AND booking_details.checkin_date >= 2023-07-20 20:00:00 AND booking_details.checkin_date <= 2023-07-22 20:00:00
) AS remaining FROM rooms;

5

Interesting. You have a syntax error in your SQL near the spot where you have neglected to include quotes around your timestamp values.

6

got it …thanks sir,

final sql query

$result = $conn->execute('SELECT rooms.id, rooms.uuid, rooms.name, rooms.dharmshala_id, rooms.room_type_id, rooms.available_room_online - ( SELECT IFNULL(sum(booking_details.quantity), 0)
FROM booking_details WHERE rooms.id = booking_details.room_id
AND booking_details.checkin_date >= ‘2023-07-20 20:00:00’ AND booking_details.checkin_date <= ‘23023-07-22 20:00:00’
) AS remaining FROM rooms WHERE rooms.room_type_id=1
')->fetchAll(‘assoc’);

Zuluru Sir… request you to help me, How to write above sql query in proper cakephp4 format query

7

You’ll need to use expression functions for this, which is something I’m not very familiar with. Recommend that you post a new question, with the query you’re trying to execute and (important!) your attempt at writing it in Cake.