pollysal

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

i have two table in my database which is the room table and roomtype table.i'm using phpmyadmin. room table room_no r_roomtypeID roomtype table roomtypeID roomtype how can i create a query that can select A random room_no from "room table " based on the roomtypeID in the "table roomtype" p/s - each roomtypeID got it's own quantity room no. roomtypeID room_no example : Single room : 1-10 : Deluxe room : 11-20 : Suite room : 21-30 right now i'm only having this kind of idea. the random number from room. I don't know how can i generate A random room_no from table room based on the roomtypeID. (SELECT room_no FROM room ORDER BY RAND( )LIMIT 1) any ideas is really appreciated:)

thanks to those who reply. ym_chaitu, i did use the code you give as reference, but it still not work out. Plus, it seems that it doesn't even insert any record into the database. do actually a button can only make a function. I mean that, one button for submit data to the database, and one button to go to the next page? this question might sound stupid, but i'm relatively just learn. so, if anyone please straighten me out if i'm wrong..

i have a form named:"RegistrationForm.php". when i click the "submit button", there's a data inserted in the database (the prove is, there a new row in the database), but there's no value from user textfield(ic_no,name,email...) that have been inserted. It means like there's a row of data inserted but without value.. p/s- however, when i click the submit button, it successfully directed me to the "BookingForm.php" with all the session value...it's just that there's no data inserted into the database. can someone straighten this up for me? am i doing wrong with the coding with the submit button? here's the code <?php session_start(); ?> <html> <body> <form action="BookingForm.php" method="post"> <p><strong>REGISTRATION FORM</strong></p> <table width="285" border="1"> <tr> <th width="120" scope="row">Ic No :</th> <td width="149"><label> <input type="text" name="ic_no" id="ic_no" value="<?php $ic_no; ?>"> </label></td> </tr> <tr> <th scope="row">Name :</th> <td><label> <input type="text" name="name" id="name" value="<?php $name; ?>"> </label></td> </tr> <tr> <th scope="row">Address :</th> <td><label> <input type="text" name="address" id="address" value="<?php $address; ?>" > </label></td> </tr> <tr> <th scope="row">Contact No :</th> <td><label> <input type="text" name="tel_no" id="tel_no" value="<?php $tel_no; ?>"> </label></td> </tr> <tr> <th scope="row">Email :</th> <td><label> <input type="text" name="email" id="email" value="<?php $email; ?>"> </label></td> </tr> </table> <input type="submit" name="submit" id="submit" value="Submit"> <?php $con = mysql_connect("localhost","root","password"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("ambos", $con); mysql_query("INSERT INTO customer (ic_no, name, address, tel_no, email) VALUES ('$_POST[ic_no]', '$_POST[name]', '$_POST[address]','$_POST[tel_no]','$_POST[email]')"); mysql_close($con); $_SESSION['ic_no']=$ic_no; $_SESSION['name']=$name; $_SESSION['address']=$address; $_SESSION['tel_no']=$tel_no; $_SESSION['email']=$email; ?> </form> </body> </html>

i'm sorry the the super moderater for my inappropriate post....please don't be mad...i'm seriously sorry sir.. i'm using the mysql phpmyadmin. here's the error that appear : #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REFERENCES customer( ic_no ) , PRIMARY KEY ( b_room_no ) REFERENCES room( room_' at line 10 however, here the relationship table. or, am i actually wrong in definening the syntax to create a composite key relationship? [attachment deleted by admin]

i wanted to create a table name booking that have 3 composite key where 2 of the composite key is referring to each another table named customer and room. however when i wanted to create the table, it give me error. can someone tell me how can i fix this. Here's the command : CREATE TABLE booking( bookingID INT NOT NULL AUTO_INCREMENT , checkin DATETIME, checkout DATETIME, nights INT, totalprice INT, b_ic_no VARCHAR(30), b_room_no INT, PRIMARY KEY ( bookingID) , PRIMARY KEY ( b_ic_no ) REFERENCES customer( ic_no ) , PRIMARY KEY ( b_room_no ) REFERENCES room( room_no ), ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = INNODB;

i have created a database for a room reservation. for this database i'm using mysql phpmyadmin. Right now it's just have the simple basic data that needed in the database. I hope someone can commeneted wether this database relationship look good or not...(eventhough i'm using mysql, i just make the ERD in access so that i can see relationship between the tables) especially part where the data relationship between the booking table and customer table. [attachment deleted by admin]

thanks to andrew and ignace.. i finally manage to get the result that i wanted based on my query. i fix back my table especially at the part where the roomtypeID that have the conflict with the varchar. This is the final result of my query that gave me the result that i wanted. SELECT rt.roomtype, rt.roomprice FROM roomtype rt INNER JOIN rooms r ON rt.roomtypeID = r.roomtypeID WHERE r.roomID NOT IN ( SELECT roomID FROM booking WHERE checkin >= '2010-04-01' AND checkout <= '2010-04-06' ) GROUP BY rt.roomtypeID thanks again to anyone who reply. cheers!!

i would want the query to first, find the available room the checkin and checkout, then it will display the room available in the based on the table rooms that contains roomID and roomtypeID. Then, from the roomtypeID(frm table room) as a foreign key to the roomtypeID in the table roomtype, it will display the roomprice and roomtype from roomtype. The display of the roomprice and roomtype is really important as i want to sent the value of the roomtypeID to the next page of the php form by using id

//then, at this part- the end part, it will only show the availability room based on the roomtype and roomprice only.. select distinct roomtype, roomprice from roomtype where romtypeID IN //then, it wil show the available room based on the roomtypeID and roomID. (select roomtypeID, roomID from rooms where roomID NOT IN //at this part, i wanted to show the room availability, (select roomID froom booking where checkin>="2010-04-01" AND checkout<="2010-04-06"))

i have just create 4 tables like below : CREATE TABLE customer( customerID INT NOT NULL AUTO_INCREMENT , name VARCHAR( 30 ) , address VARCHAR( 30 ) , tel_no INT( 15 ) , email VARCHAR( 30 ) , PRIMARY KEY (customerID) ) ENGINE=INNODB; CREATE TABLE roomtype( roomtypeID INT NOT NULL AUTO_INCREMENT , roomtype VARCHAR( 30 ) , roomprice INT( 30 ) , roombed INT( 15 ) , PRIMARY KEY ( roomtypeID ) ) ENGINE=INNODB; CREATE TABLE rooms( roomID INT NOT NULL AUTO_INCREMENT , roomtypeID varchar( 30 ) , room_no INT( 15 ) , PRIMARY KEY ( roomID ) , FOREIGN KEY ( roomtypeID ) REFERENCES roomtype( roomtypeID ) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = INNODB CREATE TABLE booking( bookingID INT NOT NULL AUTO_INCREMENT , checkin DATETIME, checkout DATETIME, nights INT( 10 ) , totalprice INT( 100 ) , customerID INT, roomID INT, PRIMARY KEY ( bookingID ) , FOREIGN KEY ( customerID ) REFERENCES customer( customerID ) , FOREIGN KEY ( roomID ) REFERENCES rooms( roomID ) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = INNODB i really got no idea how to only display the roomtype and roomprice from the table roomtype. I do really hope someone can help me as i have spent 5 hours only for this one query (yes..i admit i'm not talented in this stuff..), so please,if there's anyone can give any ideas for me to solve this... i do appreciate it so much... below is the query that i'm working on that never success : select distinct roomtype, roomprice from roomtype where romtypeID IN ( select roomtypeID, roomID from rooms where roomID NOT IN ( select roomID froom booking where checkin>="2010-04-01" AND checkout<="2010-04-06")) when i test it at phpmyadmin, the problem comes from the outter select which is the part "select distinct...". when i tested it, the subselect works fine..the problems comes from the select distinct part

i have just create 4 tables like below : CREATE TABLE customer( customerID INT NOT NULL AUTO_INCREMENT , name VARCHAR( 30 ) , address VARCHAR( 30 ) , tel_no INT( 15 ) , email VARCHAR( 30 ) , PRIMARY KEY (customerID) ) ENGINE=INNODB; CREATE TABLE roomtype( roomtypeID INT NOT NULL AUTO_INCREMENT , roomtype VARCHAR( 30 ) , roomprice INT( 30 ) , roombed INT( 15 ) , PRIMARY KEY ( roomtypeID ) ) ENGINE=INNODB; CREATE TABLE rooms( roomID INT NOT NULL AUTO_INCREMENT , roomtypeID varchar( 30 ) , room_no INT( 15 ) , PRIMARY KEY ( roomID ) , FOREIGN KEY ( roomtypeID ) REFERENCES roomtype( roomtypeID ) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = INNODB CREATE TABLE booking( bookingID INT NOT NULL AUTO_INCREMENT , checkin DATETIME, checkout DATETIME, nights INT( 10 ) , totalprice INT( 100 ) , customerID INT, roomID INT, PRIMARY KEY ( bookingID ) , FOREIGN KEY ( customerID ) REFERENCES customer( customerID ) , FOREIGN KEY ( roomID ) REFERENCES rooms( roomID ) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = INNODB i really got no idea how to only display the roomtype and roomprice from the table roomtype. I do really hope someone can help me as i have spent 5 hours only for this one query (yes..i admit i'm not talented in this stuff..), so please,if there's anyone can give any ideas for me to solve this... i do appreciate it so much... below is the query that i'm working on that never success : select distinct roomtype, roomprice from roomtype where romtypeID IN ( select roomtypeID, roomID from rooms where roomID NOT IN ( select roomID froom booking where checkin>="2010-04-01" AND checkout<="2010-04-06")) when i test it at phpmyadmin, the problem comes from the outter select which is the part "select distinct...". when i tested it, the subselect works fine..the problems comes from the select distinct part

i have 2 forms here. 1) DisplayDetails.php 2) RegistrationForm.php when user click to the link 'Next' at the DisplayDetails.php page it will bring all the session value to the RegistrationForm.php page. But, there's also value which is not session which is i) $room_type so, at the RegistrationForm, i wanted to passed all the values from the DisplayDetails.php into mysql query to insert into database.After i test it, all the data work fine including the session values from DisplayDetails.php. The only problem is, value which is i) $room_type that i got from the query in the DisplayDetails.php as the only that unsuccessfully inserted into the database. So, the question is, is it possible for me to get value from the DisplayDetails.php page into RegistrationForm.php page query so that i can insert the value into database. below is the code for DisplayDetails.php <?php session_start(); //echo "<pre>"; //var_dump($_SESSION); //echo "</pre>"; $id_no=$_GET['id_no']; $query="SELECT * from room1 WHERE id_no=$id_no"; $result=mysql_query($query); //Get the number of rows in array for loop $num=mysql_numrows($result); mysql_close(); $i=0; while ($i < $num) { $id_no=mysql_result($result,$i,"id_no"); $room_no=mysql_result($result,$i,"room_no"); $room_type=mysql_result($result,$i,"room_type"); $qty=mysql_result($result,$i,"qty"); $room_price=mysql_result($result,$i,"room_price"); //echo "$id_no - $room_no - $room_type - $qty - $rom_price"; $i++; } ?> <body> <h3><center> Room's Reservation </center></h3> <form action="DisplayDetails.php" method="post"> <table width="373" border="1"> <tr> <td colspan="2"><strong>Reservation Summary</strong></td> </tr> <tr> <td>Check In :</td> <td><label> <?php echo $_SESSION['checkin']; ?> </label></td> </tr> <tr> <td>Check Out :</td> <td><label><?php echo $_SESSION['checkout']; ?></label></td> </tr> <tr> <td>Rooms :</td> <td><label><?php echo $_SESSION['rooms']; ?></label></td> </tr> <tr> <td>Adults Per Room :</td> <td><label><?php echo $_SESSION['adults']; ?></label></td> </tr> <tr> <td>Children Per Room :</td> <td><label><?php echo $_SESSION['children']; ?></label></td> </tr> <tr> <td>Days :</td> <td><?php echo $_SESSION['days']; ?></td> </tr> <tr> <td>Room Type</td> <td><?php echo $room_type; ?></td> </tr> <tr> <td>Room Price</td> <td><?php echo $room_price; ?></td> </tr> <tr> <td>TOTAL PRICE :</td> <td><?php $total =$_SESSION['days'] * $room_price; echo $total;?> </td> </tr> </table> <label> <input type="submit" name="submit" id="submit" value="submit" /> </label> <a href="RegistrationForm.php"><strong> Next >> </strong></a> </form> </body> </html> below is the code for RegistrationForm.php <?php session_start(); ?> <html> <body> <form action="RegistrationForm.php" method="post"> <p><strong>REGISTRATION FORM</strong></p> <table width="285" border="1"> <tr> <th width="120" scope="row">Name :</th> <td width="149"><label> <input type="text" name="name" id="name" value="<?php $name; ?>"/> </label></td> </tr> <tr> <th scope="row">Ic No :</th> <td><label> <input type="text" name="ic_no" id="ic_no" value="<?php $ic_no; ?>"> </label></td> </tr> <tr> <th scope="row">Contact No :</th> <td><label> <input type="text" name="contact_no" id="contact_no" value="<?php $contact_no; ?>"> </label></td> </tr> <tr> <th scope="row">Email :</th> <td><label> <input type="text" name="email" id="email" value="<?php $email; ?>"> </label></td> </tr> <tr> <th scope="row">Gender :</th> <td><label> <select name="gender" id="gender" value="<?php $gender; ?>"> <option>female</option> <option>male</option> </select> </label></td> </tr> <tr> <th scope="row">Username :</th> <td><label> <input type="text" name="username" id="username" value="<?php $username; ?>"> </label></td> </tr> <tr> <th scope="row">Password :</th> <td><label> <input type="text" name="password" id="password" value="<?php $password; ?>"> </label></td> </tr> </table> <p> </p> <p> <label> <input type="submit" name="submit" id="submit" value="Submit"> </label> <?php $room_type=$_POST['room_type']; $sql="INSERT INTO reservation1 (name,ic_no, gender,checkin,checkout, room_type) VALUES ('$_POST[name]','$_POST[ic_no]','$_POST[gender]','$_SESSION[checkin]','$_SESSION[checkout]', '$_POST[room_type]')"; ?> </p> </form> </body> </html>

thanks Ruzzas for all the efforts to help me. yeah, the probs come from the wrongly flow of the code. I finaly realize that... you did really help knocked me some sense about the variables...i'm the one who doesn't make sense here with the code. thanks again!!

thanks wildteen88 for the reply... however, finally i managed to get the output that i wanted after configure it myself..