1) user inputs check-in and check-out dates (to check what rooms are available during the dates input)
2) database is queried for all room categories AVAILABLE within the dates indicated
step 1 and 2 work out well using the query below :
Expand|Select|Wrap|Line Numbers
- (
- SELECT rt.roomtypeID, rt.roomtype, rt.roomprice
- FROM roomtype rt
- INNER JOIN room r ON rt.roomtypeID = r.r_roomtypeID
- WHERE r.room_status = 'available'
- AND r.room_no NOT
- IN (
- SELECT b_room_no
- FROM booking
- WHERE checkin >= '2010-04-04'
- AND checkout <= '2010-04-06'
- )
- GROUP BY rt.roomtypeID
- )
How can I assign ROOM NO for any customer who has just make a reservation.
I have a 'room table' and 'roomtype table'.
right now when user make a reservation, i will assign them a random number based on the roomtype they had choose.
example : roomtype Single=10 rooms, Deluxe=10 rooms, Suite=10 rooms.
right now this is the only things that i can think right now..
Expand|Select|Wrap|Line Numbers
- (SELECT room_no FROM room WHERE r_roomtypeID ='single' AND room_status='available' ORDER BY RAND( ) LIMIT 1");
So, next time if another customer wanted to make a reservation, the random number that will be selected will not involved the room that has status room_status='unavailable'.
I appreciate any ideas, keyword too google for , or any articles that i can refer to in solving this matter:)
here is my database:
Expand|Select|Wrap|Line Numbers
- CREATE TABLE `booking` (
- `bookingID` int(11) NOT NULL auto_increment,
- `b_ic_no` varchar(30) collate latin1_general_ci NOT NULL default '',
- `b_room_no` int(11) NOT NULL default '0',
- `checkin` date default NULL,
- `checkout` date default NULL,
- `nights` int(11) default NULL,
- `totalprice` int(11) default NULL,
- PRIMARY KEY (`bookingID`,`b_ic_no`,`b_room_no`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7 ;
- --
- -- Dumping data for table `booking`
- --
- INSERT INTO `booking` (`bookingID`, `b_ic_no`, `b_room_no`, `checkin`, `checkout`, `nights`, `totalprice`) VALUES
- (1, '1111', 1, '2010-04-04', '2010-04-06', 2, 50),
- (2, '2222', 2, '2010-04-04', '2010-04-06', 2, 50),
- (3, '3333', 3, '2010-04-04', '2010-04-06', 2, 50),
- (4, '4444', 4, '2010-04-04', '2010-04-06', 2, 50),
- (5, '5555', 5, '2010-04-04', '2010-04-06', 2, 50),
- (6, '6666', 11, '2010-04-04', '2010-04-06', 2, 80);
- -- --------------------------------------------------------
- --
- -- Table structure for table `customer`
- --
- CREATE TABLE `customer` (
- `customer_id` int(10) NOT NULL auto_increment,
- `username` varchar(100) collate latin1_general_ci NOT NULL,
- `password` varchar(100) collate latin1_general_ci NOT NULL,
- `Name` varchar(100) collate latin1_general_ci NOT NULL,
- `ICNo` varchar(15) collate latin1_general_ci NOT NULL,
- `DOB` varchar(15) collate latin1_general_ci NOT NULL,
- `Address` varchar(100) collate latin1_general_ci NOT NULL,
- `TelNo` int(15) NOT NULL,
- `CompanyName` varchar(50) collate latin1_general_ci NOT NULL,
- `Occupation` varchar(50) collate latin1_general_ci NOT NULL,
- `Nationality` varchar(30) collate latin1_general_ci NOT NULL,
- `Email` varchar(50) collate latin1_general_ci NOT NULL,
- `level` int(4) NOT NULL default '2',
- PRIMARY KEY (`customer_id`,`ICNo`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=20 ;
- --
- -- Dumping data for table `customer`
- --
- INSERT INTO `customer` (`customer_id`, `username`, `password`, `Name`, `ICNo`, `DOB`, `Address`, `TelNo`, `CompanyName`, `Occupation`, `Nationality`, `Email`, `level`) VALUES
- (18, 'aaa', 'aaa', 'aaa', '1111', '', 'London', 1, '', 'engineer', 'chinese', 'aaa', 2),
- (19, 'sss', 'sss', 'sss', '2222', '', 'London', 222, '', '2222', 'chinese', '2222', 2);
- -- --------------------------------------------------------
- --
- -- Table structure for table `room`
- --
- CREATE TABLE `room` (
- `room_no` int(11) NOT NULL,
- `r_roomtypeID` int(11) default NULL,
- `room_status` varchar(100) collate latin1_general_ci default NULL,
- PRIMARY KEY (`room_no`),
- KEY `r_roomtypeID` (`r_roomtypeID`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
- --
- -- Dumping data for table `room`
- --
- INSERT INTO `room` (`room_no`, `r_roomtypeID`, `room_status`) VALUES
- (1, 1, 'unavailable'),
- (2, 1, 'unavailable'),
- (3, 1, 'unavailable'),
- (4, 1, 'unavailable'),
- (5, 1, 'unavailable'),
- (6, 1, 'available'),
- (7, 1, 'available'),
- (8, 1, 'available'),
- (9, 1, 'available'),
- (10, 1, 'available'),
- (11, 2, 'unavailable'),
- (12, 2, 'available'),
- (13, 2, 'available'),
- (14, 2, 'available'),
- (15, 2, 'available'),
- (16, 2, 'available'),
- (17, 2, 'available'),
- (18, 2, 'available'),
- (19, 2, 'available'),
- (20, 2, 'available'),
- (21, 3, 'available'),
- (22, 3, 'available'),
- (23, 3, 'available'),
- (24, NULL, NULL);
- -- --------------------------------------------------------
- --
- -- Table structure for table `roomtype`
- --
- CREATE TABLE `roomtype` (
- `roomtypeID` int(11) NOT NULL auto_increment,
- `roomtype` varchar(30) collate latin1_general_ci default NULL,
- `roomprice` int(11) default NULL,
- PRIMARY KEY (`roomtypeID`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=6 ;
- --
- -- Dumping data for table `roomtype`
- --
- INSERT INTO `roomtype` (`roomtypeID`, `roomtype`, `roomprice`) VALUES
- (1, 'single', 50),
- (2, 'Twin Sharing', 80),
- (3, 'Deluxe', 100),
- (4, 'Superior', 130),
- (5, 'Suite', 150);