+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Check Available Rooms For Selected Dates (/showthread.php?tid=73742)
Check Available Rooms For Selected Dates - demyr - 05-29-2019
Hello there,
Working on a small hotel reservation system and got stucked on "check availability" part and need your help.
My DB Structure :
1- ROOMS table :
room_id - room_no - room_capacity
2- RESERVATION table :
reservation_id - days - daily_price - room_no - customer_id
room_no is the FK.
I can get the result of the rooms which are booked on selected dates but I cannot get the ones which are available (which are not booked for those days).
I have tried lots of things upon my searchs but let me write here some example :
My Modal :
PHP Code:
public function check_room_availability($date_in, $date_out) {
$query = $this->db->select('*')->from('rooms')
->join('reservation', 'rooms.room_no = reservation.room_no', 'LEFT')
->where('reservation.days >=', $date_in)
->where('reservation.days <=', $date_out)
->order_by('reservation.room_no', 'ASC')
->get()->result();
return $query;
Thanks in advance.
RE: Check Available Rooms For Selected Dates - neuron - 05-29-2019
I won't understand how did you booked rooms.
There are should be reservation start date and end date data to do such queries.
RE: Check Available Rooms For Selected Dates - demyr - 05-29-2019
(05-29-2019, 05:04 AM)neuron Wrote: I won't understand how did you booked rooms.
There are should be reservation start date and end date data to do such queries.
Hi neuron.
Do you mean the structure of reservation table ?
Are not "days column" enough for that operation which keeps records after having received selected days via a for loop upon a date selection both for date_in and date_out?
RE: Check Available Rooms For Selected Dates - InsiteFX - 05-29-2019
Add a flag to your rooms table is_reserved
is_reserved = 0 ( room not reserved ).
is_reserved = 1 ( room is reserved ).
Then just do a query on rooms is_reserved flag and you can get a list of
rooms that are reserved and not reserved.
But as @ neuron mentioned you should also have a start and ending date fields in your reservation table.
RE: Check Available Rooms For Selected Dates - Wouter60 - 05-29-2019
You can check the available rooms with a subquery.
PHP Code:
$this->db
->select('room_no')
->from('reservation')
->where('days >=', $date_in)
->where('days <=', $date_out);
$where_clause = $this->db->get_compiled_select();
$query = $this->db->where("room_id NOT IN ($where_clause)", NULL, FALSE)->get('rooms');
if ($query->num_rows() == 0) {
return FALSE;
}
else {
return $query->result();
}
RE: Check Available Rooms For Selected Dates - demyr - 05-29-2019
(05-29-2019, 10:32 AM)Wouter60 Wrote: You can check the available rooms with a subquery.
PHP Code:$this->db
->select('room_no')
->from('reservation')
->where('days >=', $date_in)
->where('days <=', $date_out);
$where_clause = $this->db->get_compiled_select();
$query = $this->db->where("room_id NOT IN ($where_clause)", NULL, FALSE)->get('rooms');
if ($query->num_rows() == 0) {
return FALSE;
}
else {
return $query->result();
}
Thank you so much. Your code was like a tutorial for me. It worked like a charm. I was thinking of an array but missed get_compiled_select() part and how to use it properly.
RE: Check Available Rooms For Selected Dates - neuron - 05-30-2019
(05-29-2019, 10:32 AM)Wouter60 Wrote: You can check the available rooms with a subquery.
PHP Code:$this->db
->select('room_no')
->from('reservation')
->where('days >=', $date_in)
->where('days <=', $date_out);
$where_clause = $this->db->get_compiled_select();
$query = $this->db->where("room_id NOT IN ($where_clause)", NULL, FALSE)->get('rooms');
if ($query->num_rows() == 0) {
return FALSE;
}
else {
return $query->result();
}
the complexity of WHERE IN/NOT IN is O(n^2) which is why we should avoid this type of query. JOIN complexity is O(N+M) which should be preferred over WHERE IN/NOT IN clause.
My solution would be:
add to reservation table date_in and date_out columns.
then to get free rooms in specified dates use.
Code:
$this->db
->select('*')
->from('room r')
//join with ones that are not available in the given dates
->join('reservation rs', "r.room_no = rs.room_no and (rs.date_in <= '$date_in' and rs.date_out > '$date_in') and (rs.date_in < '$date_out' and rs.date_out > '$date_out' ", 'LEFT')
//then filter those unavailable rooms
->where('rs.id', NULL);
RE: Check Available Rooms For Selected Dates - demyr - 06-01-2019
Thank you for the suggestion Neuron.
If I tried your solution now, then I think, I would have to change the Controller part of input->post for the dates.. But, I do not have time for now as it is a simple project and must be finished as soon as possible. However, as I, in one example, had seen this column type: using only one column for days, and inserting each date (date in - out) via a for loop.. So far, I've had no problem with that.