lovephp Posted March 8, 2016 Share Posted March 8, 2016 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted March 8, 2016 Share Posted March 8, 2016 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted March 8, 2016 Share Posted March 8, 2016 A couple of notes on your tables you should have a table containing "room_status", each row having and an id and status description the id of the status should be stored in the room table, not the description. 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. Quote Link to comment Share on other sites More sharing options...
lovephp Posted March 10, 2016 Author Share Posted March 10, 2016 A couple of notes on your tables you should have a table containing "room_status", each row having and an id and status description the id of the status should be stored in the room table, not the description. 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. Quote Link to comment Share on other sites More sharing options...
lovephp Posted March 10, 2016 Author Share Posted March 10, 2016 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 Quote Link to comment Share on other sites More sharing options...
lovephp Posted March 10, 2016 Author Share Posted March 10, 2016 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted March 10, 2016 Share Posted March 10, 2016 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 Quote Link to comment Share on other sites More sharing options...
lovephp Posted March 10, 2016 Author Share Posted March 10, 2016 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted March 10, 2016 Share Posted March 10, 2016 you are still making exactly the same mistake Quote Link to comment Share on other sites More sharing options...
lovephp Posted March 10, 2016 Author Share Posted March 10, 2016 Help correct please. Quote Link to comment Share on other sites More sharing options...
Barand Posted March 10, 2016 Share Posted March 10, 2016 OK, once again I had Sn < To AND En > From You have Sn < From AND En > To Can you spot the difference yet? Quote Link to comment Share on other sites More sharing options...
lovephp Posted March 10, 2016 Author Share Posted March 10, 2016 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted March 10, 2016 Share Posted March 10, 2016 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 Quote Link to comment Share on other sites More sharing options...
lovephp Posted March 10, 2016 Author Share Posted March 10, 2016 No - it is occupied from 12th to 15th. It becomes available from the 15th cool so now is my query correct? Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted March 10, 2016 Solution Share Posted March 10, 2016 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 | +---------+-----------+-------------+---------------+-------------+ 1 Quote Link to comment Share on other sites More sharing options...
lovephp Posted March 10, 2016 Author Share Posted March 10, 2016 what is still confusing me is that we are comparing the checkin date with the checkout date from form Quote Link to comment Share on other sites More sharing options...
Barand Posted March 10, 2016 Share Posted March 10, 2016 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. 1 Quote Link to comment Share on other sites More sharing options...
lovephp Posted March 10, 2016 Author Share Posted March 10, 2016 kind of getting it now thanks alot for he help bro really appreciate it. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.