Hi there pls refer to the following codes --------------------------------------- <?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?
1, you need to add SQL injection protection (look up mysql_real_escape_string() 2, you dont need the design loopup inside the while loop, as it looks up the same thing each time. 3, have your tried echoing out different stages so you can see if its producing your desired results from the queries ? 4, what happens if your initial query $rs3= mysql_query("select * from grand_roomdesign where roomId='".$roomType."'" ); Code (markup): has no results (use mysql_num_rows($rs3) > 0) to check if we have the room in our DB.
Bro with regards to points 2 and 4 2- U are referring to this code? $rs1= mysql_query("select * from grand_roomdesign where room_design='".$design."'"); This is to retrieve the table type from the grand_roomdesign table based on the user selection (Theatre, Hollow Square e.t.c.), while the initial query ($rs3= mysql_query("select * from grand_roomdesign where roomId='".$roomType."'" ) is true ----------------------------------------------- 4- The initial query will definitely return results To clarify my point, I will attach a pic of my table grand_roomdesign here U can see from the pic, the table has a foreign key - roomId referencing the grand_roomtype table. Here is the php code in my booking page which I use to retrieve the Room type from the database to display in the dropdown list --------------------------------------------- <select name="SP" id="type"> <?php $con = connect(); $result = mysql_query("Select * from grand_roomtype"); while($row = mysql_fetch_array($result)) { echo "<option value = '".$row['Id']."'>".$row['roomType']."</option>"; } ?> </select></td>
2, Your var $design is coming from the _POST and not the Query (where it is inside the while() statement) and being run each time for every record restrieved. As it will not change, you can put that outside of the while loop. So it would become $rs1= mysql_query("select * from grand_roomdesign where room_design='".$design."'"); $row1= mysql_fetch_assoc($rs1); $rmdesign= $row['room_design']; $capacity= $row['capacity']; $rs3= mysql_query("select * from grand_roomdesign where roomId='".$roomType."'" ); while ($row3 = mysql_fetch_array($rs3)) { $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); } Code (markup): I would also use mysql_num_rows() and not count(*) so $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); Code (markup): would become $rs2= mysql_query("Select * from grand_reservation where time = '". $time ."' and apptDay = '".$day."' and apptMonth = '".$month."' and apptYear = '".$year."' and room_design='".$rmdesign."' and roomType='".$roomType."'"); $count= mysql_num_rows($rs2); Code (markup): but againt, if you have multiple rows, this value will change for every record in the loop before the outside capacity check is done. If there is only 1 record in grand_roomdesign for each roomType, then you dont need to do a while loop.
Ok I have modified my codes to: ------------------------------- <?php include "dbFunction.php"; $con = connect(); $roomType=$_POST['SP']; $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']; $rs1= mysql_query("select capacity from grand_roomdesign where room_design='".$design."' AND roomId='".$roomType."'" ); $row1= mysql_fetch_row($rs1); $capacity= $row1[0]; $rs2= mysql_query("Select * from grand_reservation where 'time' = '. $time .' and 'apptDay' = '.$day.' and 'apptMonth' = '.$month.' and 'apptYear' = '.$year.' and 'room_design'='.$design.' and 'roomType'='.$roomType.' "); $count= mysql_num_rows($rs2); if ($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"; } ?> ----------------------- I find the while loop unnecessary, so I removed it. However it is still not working, keep returning the message 'no room' Anyone knows wad's wrong?