14 Replies - 1616 Views - Last Post: 11 June 2008 - 07:00 PM Rate Topic: -----

#1 PHP_noob  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 05-June 08

Booking system not working, please help

Post icon  Posted 09 June 2008 - 04:56 AM

Hi people pls refer to the following codes below

<?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


Is This A Good Question/Topic? 0
  • +

Replies To: Booking system not working, please help

#2 PHP_noob  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 05-June 08

Re: Booking system not working, please help

Posted 09 June 2008 - 05:10 AM

In order to reduce confusion, I will attach a few pics to better illustrate everything

Posted Image

grandbr_1.bmp (2.9 MB)

Posted Image
Was This Post Helpful? 0
  • +
  • -

#3 JBrace1990  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 110
  • View blog
  • Posts: 760
  • Joined: 09-March 08

Re: Booking system not working, please help

Posted 09 June 2008 - 06:52 AM

I would suggest getting rid of the while statement, for one row, it's not needed...

of.... on lines 22 and 23, you have this, thourgh $row is not set...
   $rmdesign= $row['room_design'];
    $capacity= $row['capacity'];


and, while your queries don't look completely wrong (they might work correctly, not sure), I would suggest removing the extra (".)'s

  $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'");



let me know how it turns out =)


so, here's the modified code:

<?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'" );

$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";
                               }


?>


Was This Post Helpful? 0
  • +
  • -

#4 PHP_noob  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 05-June 08

Re: Booking system not working, please help

Posted 09 June 2008 - 07:34 AM

Hey JBrace1990, the while loop is absolutely essential

I find it hard to put my explanation into words, so i'll attach a few pics here to illustrate the point of putting the while loop

1. grand_roomdesign table

Posted Image

2. grand_roomtype table

Posted Image

U can see from pic 1, the grand_roomdesign table, that it has a foreign key roomId referencing the primary key of the grand_roomtype table.

In my initial query:

$rs3= mysql_query("select * from grand_roomdesign where roomId='".$roomType."'" );

I want to fetch the rows from the grand_roomdesign table whose roomId matches the primary key of the grand_roomtype table, and this depends on the user input at the booking page. The Id at the grand_roomtype table is the key identifying the room type (e.t.c. Grand Ballroom, Grand Ballroom 1)

So that justifies the the while loop argument doesn't it?

And inside the while loop, I want to retrieve the room_design value that matches that of the user selecion, thus the code:

$rs1= mysql_query("select * from grand_roomdesign where room_design='".$design."'");

Where I have stored the user selection of table design into the $design variable

Hope I make myself clear
Was This Post Helpful? 0
  • +
  • -

#5 joeyadms  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 41
  • View blog
  • Posts: 178
  • Joined: 04-May 08

Re: Booking system not working, please help

Posted 09 June 2008 - 08:01 AM

A couple of quick notes, when using mysql_fetch_assoc and mysql_fetch_array notably on lines 21, and 26, these create an array of results like this

ResultSet(
		  0 => array('col1' => 'val1',
						   'col2' => 'val2');
)

etc...



So you will need to use the while loop again for both of these, to be able to access the associative array in $row1 and $row2, OR you can use mysql_fetch_row see here http://www.php.net/mysql_fetch_row

Also as JBrace pointed out, change if($row3 = my.. to if($row = my... due to the use on line 22,23.

One last thing is, that this queries are creating a LOT of overhead, it would be best to look into using SQL joins.

This post has been edited by joeyadms: 09 June 2008 - 08:02 AM

Was This Post Helpful? 0
  • +
  • -

#6 PHP_noob  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 05-June 08

Re: Booking system not working, please help

Posted 09 June 2008 - 09:12 AM

joeyadms, u are asking me to do a while loop for the $row2= mysql_fetch_array($rs2); ??

What's that for?
Was This Post Helpful? 0
  • +
  • -

#7 joeyadms  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 41
  • View blog
  • Posts: 178
  • Joined: 04-May 08

Re: Booking system not working, please help

Posted 09 June 2008 - 10:22 AM

I suppose it would be fine, I've just always used fetch_row for using one result.

Your flow is sort of confusing,here is my take on it, At least how I invision it
<?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'];

$query = "SELECT `capacity` FROM grand_roomdesign where `roomId`='$roomType' AND `room_design`='$design'";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
$capacity = $row[0];

$query_appnts = "SELECT * FROM grand_reservation where `time`='$time' AND `apptDay`='$day' AND `apptMonth`='$month' AND `apptYear`='$year' AND `room_design`='$design' AND `roomType`='$roomType'";
$result_appnts = mysql_query($query_appnts);
$count = mysql_num_rows($result_appnts);

if($count < $capacity){
	//Insert
} else {
	echo "no room";
}
?>



Also once you get it working, you should consider using a DateTime column instead of the time,day,year etc columns
Was This Post Helpful? 0
  • +
  • -

#8 JBrace1990  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 110
  • View blog
  • Posts: 760
  • Joined: 09-March 08

Re: Booking system not working, please help

Posted 09 June 2008 - 01:44 PM

the While loop would be if you need something done multiple times. However, with the way you're using your query, you'll only get 1 result. that makes the while loop run once, which is the same as just placing everything.

Using a while loop just adds some server stress (not much, but more then is needed).

Also, as joeyadms said, a join statement would help. Try looking at it here, and let us know if you have problems understanding it.
Was This Post Helpful? 0
  • +
  • -

#9 PHP_noob  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 05-June 08

Re: Booking system not working, please help

Posted 09 June 2008 - 07:57 PM

Hey joeyadms, I have tried running your codes but to no avail. It keeps returning 'no room' instead
Was This Post Helpful? 0
  • +
  • -

#10 joeyadms  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 41
  • View blog
  • Posts: 178
  • Joined: 04-May 08

Re: Booking system not working, please help

Posted 09 June 2008 - 08:47 PM

I have a feeling you are trying to use this
`roomId`='$roomType' 


And looking at your tables now, these values are drastically different. You need a $roomId in your script and pass that into the query instead.


Edit2:

Ahh ok, I see, hmmm I must be missing something.

This post has been edited by joeyadms: 09 June 2008 - 09:12 PM

Was This Post Helpful? 0
  • +
  • -

#11 PHP_noob  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 05-June 08

Re: Booking system not working, please help

Posted 09 June 2008 - 08:59 PM

Ok this is the php code I included in my booking page codes, under the dropdown list to allow user to select their type of rooms

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




So why can't I use `roomId`='$roomType' ? Since $roomType itself is an integer as seen in the option value=""
Was This Post Helpful? 0
  • +
  • -

#12 joeyadms  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 41
  • View blog
  • Posts: 178
  • Joined: 04-May 08

Re: Booking system not working, please help

Posted 09 June 2008 - 09:11 PM

try changing this

$roomType = $_POST['type'];

to

$roomType = $_POST['SP'];


Was This Post Helpful? 0
  • +
  • -

#13 PHP_noob  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 05-June 08

Re: Booking system not working, please help

Posted 09 June 2008 - 09:35 PM

Ya joeyadms I have noticed that error after I posted this thread, and have corrected that

Btw when running the codes you have shown to me at post #7, it works for a while. But the $count is not working, cos I set one of the table design capacity in Grand Ballroom 2 to 2, and after 2 bookings, it still returns 'Registration successful'

Then during subsequent tries, weird things start to happen. The end result is 'no room' continually no matter what choice I selected

Funny
Was This Post Helpful? 0
  • +
  • -

#14 PHP_noob  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 05-June 08

Re: Booking system not working, please help

Posted 10 June 2008 - 08:18 PM

Hi folks here is my revised code:

<?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 * from grand_roomdesign where room_design='".$design."' AND roomId='".$roomType."'" );
   while($row = mysql_fetch_array($rs1)){ 
   $capacity= $row['capacity'];
   }
   echo $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'='.$design.' and 'roomType'='.$roomType.' ");
 while($row1= mysql_fetch_array($rs2))
{
  $count= $row1['count'];
}

 echo $count;
 
 
   if ($count <$capacity)
 			{
				
			mysql_query("Insert into grand_reservation (name, email, contact, memberId, apptDay, apptMonth, apptYear, timeslot, table_design, roomType) 
			 						 values ('$name', '$email',  '$telephone', '$memberId', '$day', '$month', '$year', '$time', '$design', '$roomType')");
			echo "Registration successful.";
										  
										  

 			}

						  else
							   {
							 echo "no room";
							   }


?>





Ok now the problem is partially solved, it returns registration successful but even when the booking is already full, it doesn't return 'no room' but continue to insert the entry into the database

e.g.
I set the capacity for 'cocktail' able in Grand Ballroom 2 to '2', so that means that a maximum of 2 bookings booking can be made for that, for the SAME DATE AND TIME. Yet I couldn't achieve the desired result with that code

After the second while loop, I do an echo $count to print out the value. But it return 0 even though there is >= 1 entry of that in the database. So something is wrong with the code

Anyone knows how to recode that to achieve the desired results?
Was This Post Helpful? 0
  • +
  • -

#15 JBrace1990  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 110
  • View blog
  • Posts: 760
  • Joined: 09-March 08

Re: Booking system not working, please help

Posted 11 June 2008 - 07:00 PM

  $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'='.$design.' and 'roomType'='.$roomType.' ");


You don't group the data at all >_>

basically, you're getting 0 back from the query because it's an invalid query.... you can NOT use COUNT(*) without a GROUP BY statement...

just make the code this and then lemme know if it works:
  $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'='.$design.' and 'roomType'='.$roomType.' GROUP BY time,apptday,apptmonth,apptyear,room_design,roomType")or die(mysql_error());


if the above doesn't work (which it very well might not, because it's late for me, and I think that would return 1 o_0), set the end to "GROUP BY id"...

also, A quick suggestion.... right now, someone could easily delete the table, empty it, Etc. because you don't sanitize your code.... just add this to each $_POST value and you should be fine....
mysql_real_escape_string(htmlspecialchars($_POST['data_values']))

This post has been edited by JBrace1990: 11 June 2008 - 07:02 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1