how would i query these tables for availables for room reservation

[lovephp]

ok so i got this two tables

 

 

CREATE TABLE IF NOT EXISTS `rooms` (
  `room_id` int(11) NOT NULL AUTO_INCREMENT,
  `room_name` text,
  `room_number` int(11) NOT NULL,
  `room_capacity` int(11) DEFAULT NULL,
  `room_status` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`room_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;



CREATE TABLE IF NOT EXISTS `reservations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start` datetime DEFAULT NULL,
  `end` datetime DEFAULT NULL,
  `room_id` int(11) DEFAULT NULL,
  `status` varchar(30) DEFAULT NULL,
  `ip` varchar(32) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

 

now how do i do a search for available rooms for reservation user would select checkin date and checkout and room name like delux, standard or suite etc or all available rooms.rooms in html form like

 

 

<form action="" method="post">
 <h5>Checkin</h5>
                             <div class="book_date">
                                 <input class="date" id="from" name="form" type="text" value="" >                            
                             </div>                    
                         </li>
                         <li  class="span1_of_1 left">
                             <h5>Checkout</h5>
                             <div class="book_date">
                            
                                <input class="date" id="to" name="to" type="text" value="" >
                            
                             </div>        
                            <li class="span1_of_1">
                             <h5>Room</h5>
                            
                             <div class="rooms">
                                  <select name="rooms">
                                   <option value="" selected="selected" disabled="disabled">Select</option>
                                        <option value="All">All Rooms</option>
                                        <option value="Deluxe Room">Deluxe Rooms</option>
                                        <option value="Super Deluxe Room">Super Deluxe Rooms</option>         
                                        <option value="Executive Room">Executive Rooms</option>
                                        <option value="Suite">Suites</option>
                                  </select>
                             </div>    
                         </li>
                         <li class="span1_of_3">
                                 <div class="date_btn">                        
                                        <input type="submit" name="submit" value="Check Availability"/>
                                        </div>
                                    </form>

 

i need help to query out available room type which start and end date is not available in reservation table and give out result how many rooms available in rooms table.

 

thanks appreciate your time and help

[Barand]

If you look at the attached diagram, it shows that room 2, 3, 4 and 5 are not available between dates From and To.

 

The condition that these rooms have in common is

Sn < To AND En > From

Edited March 8, 2016 by Barand

[Barand]

A couple of notes on your tables

 

1. you should have a table containing "room_status", each row having and an id and status description
2. the id of the status should be stored in the room table, not the description.
3. the status should not be in the reservation table (it's a property of a room)

 

You would use the status table to construct your room status menu.

[lovephp]

A couple of notes on your tables

 

1. you should have a table containing "room_status", each row having and an id and status description
2. the id of the status should be stored in the room table, not the description.
3. the status should not be in the reservation table (it's a property of a room)

 

You would use the status table to construct your room status menu.

the status in reservations table is like new, confirmed etc type of status and the status in rooms is for whether its clean drit etc.

 

[lovephp]

ok so what is wrong in this query of mine that no results are showing?

 

 

$from = $_REQUEST['from'];
                    $to = $_REQUEST['to'];
                    $room_type = $_REQUEST['room_type'];
                    $sql = mysql_query("SELECT * FROM rooms WHERE name LIKE '%".$room_type."%' id NOT IN (SELECT room_id FROM reservations WHERE start < '".$from."' AND end > '".$to."'") or die(mysql_error());

 

m getting syntax error but am not understanding whats going wrong

[lovephp]

sorry here is the code something is definitely wrong because im not getting desired results

 

 

$sql = mysql_query("SELECT * FROM rooms WHERE name LIKE '%".$room_type."%' AND id NOT IN (SELECT room_id FROM reservations WHERE start < '".$from."' AND end > '".$to."')") or die(mysql_error());

 

my start and end date structure in db is like

 

`start` datetime DEFAULT NULL,

`end` datetime DEFAULT NULL,

Edited March 10, 2016 by lovephp

[Barand]

Look more carefully at the condition dates in my post

 

The condition that these rooms have in common is

 

Sn < To AND En > From

 

 

You have

Sn < From AND En > To

 

Use DATE types and not DATETIME as unwanted time values can distort comparisons

[lovephp]

Look more carefully at the condition dates in my post

 

 

You have

Sn < From AND En > To

 

Use DATE types and not DATETIME as unwanted time values can distort comparisons

ok so i created two fields as

 

`checkin` date NOT NULL,

`checkout` date NOT NULL,

 

checkin 2016-03-15 checkout  2016-03-20

 

and the query im using is

$sql = mysql_query("SELECT * FROM rooms WHERE name = '".$room_type."' AND id NOT IN (SELECT room_id FROM reservations WHERE checkin  < '".$from."' AND checkout > '".$to."')") or die(mysql_error());

and in my rooms table there are 8 rooms by the name Deluxe, 8 Executive Rooms and when i do a search on the dates above and room name Deluxe and echo mysql_num_rows($sql); it returns 8 whereas it should show 7

 

and if i do it as <= '".$from."' AND checkout >= '".$to."' then it returns 7

 

now what wrong am i doing?

Edited March 10, 2016 by lovephp

[Barand]

you are still making exactly the same mistake

[lovephp]

Help correct please.

[Barand]

OK, once again

 

I had

Sn < To AND En > From

 

You have

Sn < From AND En > To

 

Can you spot the difference yet?

[lovephp]

OK, once again

 

I had

Sn < To AND En > From

 

You have

Sn < From AND En > To

 

Can you spot the difference yet?

is this what you mean?

 

 

 WHERE checkin  < '".$to."' AND checkout > '".$from."'

 

but say i got a booking on room id 1 from 2016-03-12 to 2016-03-15 and in form i select checkin from date 12 and checkout date on 16 shouldn't that room be available then? this is really confusing me like hell   sorry for eating up ur head too

[Barand]

 

but say i got a booking on room id 1 from 2016-03-12 to 2016-03-15 and in form i select checkin from date 12 and checkout date on 16 shouldn't that room be available then?

 

No - it is occupied from 12th to 15th. It becomes available from the 15th

[lovephp]

No - it is occupied from 12th to 15th. It becomes available from the 15th

cool so now is my query correct?

[Barand]

Should be. These are my reservations

+----+---------+------------+------------+------+---------------------+
| id | room_id | checkin    | checkout   | ip   | date                |
+----+---------+------------+------------+------+---------------------+
|  1 |       6 | 2016-03-01 | 2016-03-05 | NULL | 2016-03-10 18:08:16 |
|  2 |       2 | 2016-03-02 | 2016-03-06 | NULL | 2016-03-08 15:12:34 |
|  3 |       4 | 2016-03-03 | 2016-03-07 | NULL | 2016-03-10 18:08:16 | <-booked
|  4 |       3 | 2016-03-06 | 2016-03-07 | NULL | 2016-03-10 18:08:16 | <-booked
|  5 |       5 | 2016-03-07 | 2016-03-08 | NULL | 2016-03-08 16:07:57 | <-booked
|  6 |       1 | 2016-03-08 | 2016-03-10 | NULL | 2016-03-10 18:08:16 |
|  7 |       7 | 2016-03-08 | 2016-03-09 | NULL | 2016-03-08 15:12:34 |
|  8 |       8 | 2016-03-09 | 2019-03-10 | NULL | 2016-03-08 15:12:34 |
+----+---------+------------+------------+------+---------------------+

If I want to check in on the 6th and out on the 8th then the rooms indicated are already booked (rooms 3, 4 and 5)

SELECT * 
FROM rooms  
WHERE room_id NOT IN  
        ( 
        SELECT room_id  
        FROM reservations 
        WHERE checkin  < '2016-03-08' AND checkout > '2016-03-06' 
        );


+---------+-----------+-------------+---------------+-------------+
| room_id | room_name | room_number | room_capacity | room_status |
+---------+-----------+-------------+---------------+-------------+
|       1 | Deluxe    |         101 |             2 |           1 |
|       2 | Standard  |         102 |             2 |           2 | rooms 3,4,5
|       6 | Executive |         203 |             1 |           1 | not available
|       7 | Executive |         301 |             2 |           2 |
|       8 | Standard  |         302 |             2 |           3 |
|       9 | Executive |         303 |             1 |           3 |
|      10 | Suite     |         401 |             6 |           5 |
|      11 | Suite     |         501 |             4 |           2 |
+---------+-----------+-------------+---------------+-------------+

[lovephp]

what is still confusing me is that we are comparing the checkin date with the checkout date from form

[Barand]

That's right. I suggest you read my first reply (#2) again and study the attached diagram.

 

You have a search period From - To.

 

A room is already booked if it was checked in

• before the end of the search period (To)
• AND it is checked out after the start (From) of the search period

In other words, if the booking period of the room overlaps the search period.

[lovephp]

kind of getting it now thanks alot for he help bro really appreciate it.