Jump to content

how would i query these tables for availables for room reservation


lovephp
Go to solution Solved by Barand,

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

:)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by lovephp
Link to comment
Share on other sites

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 by lovephp
Link to comment
Share on other sites

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 :tease-03:  sorry for eating up ur head too

Link to comment
Share on other sites

  • Solution

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 |
+---------+-----------+-------------+---------------+-------------+
  • Like 1
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.