<?php include "dbFunction.php"; $con = connect(); $roomType=$_POST['type']; $time=$_POST['time']; $day=$_POST['day']; $month=$_POST['month']; $year=$_POST['year']; $memberId=$_POST['member_id']; $name=$_POST['name']; $email=$_POST['email']; $telephone=$_POST['handphone']; $design=$_POST['design']; $rs3= mysql_query("select * from grand_roomdesign where roomId='".$roomType."'" ); while ($row3 = mysql_fetch_array($rs3)) { $rs1= mysql_query("select * from grand_roomdesign where room_design='".$design."'"); $row1= mysql_fetch_assoc($rs1); $rmdesign= $row['room_design']; $capacity= $row['capacity']; $rs2= mysql_query("Select count(*) as 'count' from grand_reservation where time = '". $time ."' and apptDay = '".$day."' and apptMonth = '".$month."' and apptYear = '".$year."' and room_design='".$rmdesign."' and roomType='".$roomType."'"); $row2= mysql_fetch_array($rs2); } if ($row2['count'] < $capacity) { mysql_query("Insert into grand_reservation (name, contact, apptMonth, apptYear, apptDay, memberId, email, time, timeslot, roomType) values ('$name', '$telephone', '$month', '$year', '$day', '$memberId', '$email', '$time', '$roomType')"); echo "Registration successful."; } else { echo "no room"; } ?>
That is the coding I have done for my sch project hotel website grand ballroom booking system. In my grandroom, there are 3 room types- Grand Ballroom, Grand Ballroom 1 and Grand Ballroom2. And when user select a room type, they must select a table design as well, in which there are 6 types- Theatre, Classroom, Hollow Square, U-Shape, Chinese/Western, and Cocktail
The thing is, there are >1 tables of each type(e.g. Theatre) for each room type('Grand Ballroom'). To give an example, let's say there are 20 table type of 'Theatre' in Grand Ballroom. Therefore the same booking CAN be made for Grand Ballroom 'Theatre' table design for the same date and time as long as the total number of bookings for this doesn't exceed the number of theatre tables, which in this example is 20. When the number of bookings exceeded 20, there'll be no more vacancies and the message 'no room' (inside the else loop) will appear instead
In order to code this, I created a table called grand_roomtype to store the 3 rooms- Grand Ballroom, Grand Ballroom 1 and Grand Ballroom 2 and assign an Id (which is the pri key) to each.
Another table I created called grand_roomdesign, to store all the 6 table types. There are 4 fields, the Id, which is the unique pri key, the table_name, the capacity(which stores the total number of tables for each type of design) and lastly the roomId which is the foreign key referencing the pri key of the grand_roomtype table.
In this query: $rs3= mysql_query("select * from grand_roomdesign where roomId='".$roomType."'" );
I match the roomId of the grand_roomdesign table to the $roomType variable, which holds the primary key of the grand_roomtype table according to the room the user choose, so that the end result of the query will only be the table types and their capacity of the room the user chooses (either Grand Ballroom, Grand ballroom 1 or Grand ballroom2).
Then in the while loop:
while ($row3 = mysql_fetch_array($rs3))
{
$rs1= mysql_query("select * from grand_roomdesign where room_design='".$design."'");
$row1= mysql_fetch_assoc($rs1);
$rmdesign= $row['room_design'];
$capacity= $row['capacity'];
$rs2= mysql_query("Select count(*) as 'count' from grand_reservation where time = '". $time ."' and apptDay = '".$day."' and apptMonth = '".$month."' and apptYear = '".$year."' and room_design='".$rmdesign."' and roomType='".$roomType."'");
$row2= mysql_fetch_array($rs2);
}
I do a query to retrieve the row that has the table type that the user chooses, as well as the capacity. Then compare it to the reservation table (grand_reservation), which holds the user information, to check whether a similar booking exists in the table. The do a count to check whether the total number exceeds the capacity
If it doesn't exceed the capacity, which means there are still available slots, the user data will be inserted into the database table. else it will return 'no room'
The thing now is, the above code is wrong cos it keeps returning 'no room' even though there aren't any entry in the database
Anyone knows what's wrong with the code and how to recode it to achieve the desired result?
This post has been edited by PHP_noob: 09 June 2008 - 04:58 AM