How to change the field status automatically?

[polly89]

Hi everybody. right now i'm doing a hotel reservation system using php and phpmyadmin. The process involved

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 :

(
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
)

Then a problem arise..
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..

(SELECT room_no FROM room WHERE r_roomtypeID ='single' AND room_status='available' ORDER BY RAND( ) LIMIT 1");

And it did work out. But then, i was thinking, how can i automatically assign the status of"unavailable" for the room no that was just assign to the customer who had just make reservation.
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:

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);

[pritaeas]

It is possible to use a trigger. When you insert a new reservation with a room no, a function is triggered, in which you can set the room to unavailable. Mysql documentation has a lot of info about it.