• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Problem with database query

#1
I have 2 tables in database. 
I need to get from database all rooms in which there is no reservation in the hours and date given by me.

rooms
Quote:id int(11)
name varchar(15)

reservations
Quote:id int(11)
room_id int(11)
date date
time_start time
time_finish time

My function: 

PHP Code:
public function get_free_rooms() {
 
   $date $this->input->post('date'true);
 
   $time_start $this->input->post('time_start'true);
 
   $time_finish $this->input->post('time_finish'true);

 
   $query $this->db->join('reservations','rooms.id = reservations.room_id','left')
 
       ->where('reservations.id IS NULL'nullfalse)
 
       ->where('reservations.date'$date)
 
       ->where('reservations.time_start<'$time_start)
 
       ->where('reservations.time_finish>'$time_finish)
 
       ->get('rooms');
 
   $query $query->result();

 
   echo json_encode($query);



My reservations table is empty now. When I give data:

Quote:$date = 2019-05-29, $time_start = 10:00:00, $time_finish = 13:00:00

result of query is an empty array and should return all rooms because there is no booking.
Reply

#2
After you run your query did you use

PHP Code:
$temp $this->db->last_query();

echo 
$temp

To see what your query looks like?
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply

#3
@mizkuskuz,

What does your query look like when it is executed? Also, why don't you have a from and to date? Is the user only allowed to have the room for 1 day?
Reply

#4
@InsiteFX, $temp return this:

Quote:SELECT * FROM `rooms` LEFT JOIN `reservations` ON `rooms`.`id` = `reservations`.`room_id` and `reservations`.`date` = '2019-05-29' and `reservations`.`time_start`<='10:00' and `reservations`.`time_finish`>='13:00' WHERE reservations.id IS NULL


@php_rocs, yes. Reservations are only for one day.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.