[SOLVED] Searching a Query...

[slawrence10]

queries have been produced in Dreamweaver for my system as Recordsets

I'm creating a room booking system and once the user has selected Week, Day and Time a query searches the booking table for any bookings which match all three.

 

The next page displays a list of all rooms using the do function

 

do{
//the stuff i want it to do
} while ($row_rooms = mysql_fetch_assoc($rooms));

 

I want it to check the query for each room, to see if there has been a booking...meaning I can allow booking or display "Booked"

 

if($room==$row_timetabled['room_id']){

 

I tried using the above if statement however it only seems to check $room against the first row / record of the query...could anyone tell me how i need to tackle this?

 

Regards and thanks for your time,

Scott

[Jessica]

We can't see the order of the code. Is that if inside your do/while?

Post the code as is.

[slawrence10]

do {
$room=$row_rooms['id'];
if($room==$row_booked['room_id']){
print  "<td width=\"50\">".$row_rooms['name']."</td>";
print "<td width=\"200\" colspan=\"2\"><span class=\"SubtitleRed\">Timetabled Lesson</span></td></tr>";
}else{
print "<td width=\"50\"><form name=\"".$room."\" method=\"post\" action=\"book3.php\">";
print  $row_rooms['name']."</td>";
print "<td width=\"100\"><span class=\"SubtitleGreen\">Available</span></td>";
print "<td width=\"100\"><input name=\"week\" type=\"hidden\" id=\"week\" value=\"".$_POST['week']."\">";
print "<input name=\"day\" type=\"hidden\" id=\"day\" value=\"".$_POST['day']."\">";
print "<input name=\"period\" type=\"hidden\" id=\"period\" value=\"".$_POST['period']."\">";
print "<input name=\"room\" type=\"hidden\" id=\"room\" value=\"".$room."\">";
print "<input type=\"submit\" name=\Book Room\" value=\"Book ".$row_rooms['name']."\"></td></tr></form>";
}
} while ($row_rooms = mysql_fetch_assoc($rooms));

 

Regards for your time,

Scott

[Jessica]

Where is $row_booked['room_id'] coming from?

[slawrence10]

That's the query / Recordset I think...not entirely sure...I dunno if the following will help, the coding for the Recordset created by dreamweaver...

$colname_booked = "-1";
if (isset($_POST['week'])) {
  $colname_booked = (get_magic_quotes_gpc()) ? $_POST['week'] : addslashes($_POST['week']);
}
$colname2_booked = "-1";
if (isset($_POST['period'])) {
  $colname2_booked = (get_magic_quotes_gpc()) ? $_POST['period'] : addslashes($_POST['period']);
}
$colname3_booked = "-1";
if (isset($_POST['day'])) {
  $colname3_booked = (get_magic_quotes_gpc()) ? $_POST['day'] : addslashes($_POST['day']);
}
mysql_select_db($database_connect, $connect);
$query_booked = sprintf("SELECT * FROM booking WHERE week_id = %s AND period = %s AND day = %s ORDER BY id ASC", GetSQLValueString($colname_booked, "int"),GetSQLValueString($colname2_booked, "int"),GetSQLValueString($colname3_booked, "int"));
$booked = mysql_query($query_booked, $connect) or die(mysql_error());
$row_booked = mysql_fetch_assoc($booked);
$totalRows_booked = mysql_num_rows($booked);

[slawrence10]

I've currently created 3 Booking of the 6 Bookings but it is only displaying the first room as booked, i think that the if statement only checks the first result of the query, what I'm really asking I guess is how to check all results of $row_booked['room_id'] for $room.

 

Regards for your time,

Scott

[Jessica]

This is the problem with letting a program like Dreamweaver write your code for you. Now you don't really know what's going on...

 

Put all of the checking inside a

while($row_booked = mysql_fetch_assoc($booked)){

[slawrence10]

The code goes through each room and checks whether booked or not..so won't that just cut off after going through the query results?

 

I tried adding it and I just got an obscure result printing two of Room 1 available for booking

[Jessica]

Why don't you make a field in your rooms and mark it either booked or available? Wouldn't that be easier then looping through the bookings?

[slawrence10]

 

It's a system for to book out my school ICT rooms, so a Room can't just be Booked or Available, it depends on the day, period, week.  It's ICT coursework..I'm just having trouble with this piece of coding  :-\

 

Thanks for your time,

Scott

[Jessica]

Okay, I'm trying to figure this out, sorry if I'm being slow.

 

You have a table of rooms, and another of room bookings.

 

You need to get all the bookings for a certain week, and a list of all the rooms.

Then you need to tell which rooms are booked, so you can mark them as such when you print the records.

 

Do I have this right?

[slawrence10]

Yeh basically the user chooses the Week, Day and Period first through a form...the recordet I set up searches through the Bookings table for any records which match all three...and yes I'm currently looping throught the rooms table to print them showing whether they are available or already booked.

[Jessica]

Here, try something like this:

<?
//Make a big array for all our bookings
$bookings = array();
$query_booked = "SELECT * FROM booking WHERE week_id = '$weekID' AND period = '$period' AND day = '$day' ORDER BY id ASC");
$booked = mysql_query($query_booked, $connect) or die(mysql_error());
while($row_booked = mysql_fetch_assoc($booked)){
//Get the bookings for this room
$roomID	= $row_booked['roomID'];
$roomBookings = $bookings[$roomID];
$roomBookings[]	= //fill in the date info, etc.
$bookings[$roomID] = $roomBookings; //Put it back in the big array.
}

print_r($bookings);
?>

 

Then, you'll have an array for each room based on ID, of all their bookings, so when you loop through the rooms to show them, you can just use that array which is in $bookings. I added a print_r at the end so you could see what it looks like.

 

Edit: Using a mysql JOIN here is probably best, but I don't know how to do them properly, so I can't help you there. This is how I'd do it.

[slawrence10]

I've had a quick play with what you said I'm not that confident with it though...will see if i can work it...

 

 

doesn't the recordset $row_bookings create an array of the bookings already? I just need to work out how to search it for each room.

 

thanks for your time,

Scott.

 

 

[Jessica]

It does, but that array only exists while you're looping through it, then the next row overwrites it. I was just trying to get it all out into a seperate array that you could manage.

[slawrence10]

it's getting there  thanks so much for your help so far much appreciated...

 

if(array_key_exists($room, $bookings))

 

this seems to work HOWEVER

the array seems to be missing the first record of the query....i'm testing it by having records of Room 1 ,2 and 6 being booked,

 

the array only prints 2 and 6

 

thanks for your time,

Scott

[Jessica]

Sorry, can you post the whole code again, what you have now?

[slawrence10]

 <?php
//Make a big array for all our bookings
$bookings = array();
while($row_booked = mysql_fetch_assoc($booked)){
//Get the bookings for this room
$roomID	= $row_booked['room_id'];
$bookings[$roomID] = $roomBookings; //Put it back in the big array.
}
print_r($bookings);
		  
		  print "<table cellpadding=0 cellspacing=0 border=0><tr>";
		  do {
		  $room=$row_rooms['id'];
		  if(array_key_exists($room, $bookings)){
		  print  "<td width=\"50\">".$row_rooms['name']."</td>";
		  print "<td width=\"200\" colspan=\"2\"><span class=\"SubtitleRed\">Booked</span></td></tr>";
		  }else{
		  print "<td width=\"50\"><form name=\"".$room."\" method=\"post\" action=\"book3.php\">";
		  print  $row_rooms['name']."</td>";
		  print "<td width=\"100\"><span class=\"SubtitleGreen\">Available</span></td>";
		  print "<td width=\"100\"><input name=\"week\" type=\"hidden\" id=\"week\" value=\"".$_POST['week']."\">";
		  print "<input name=\"day\" type=\"hidden\" id=\"day\" value=\"".$_POST['day']."\">";
		  print "<input name=\"period\" type=\"hidden\" id=\"period\" value=\"".$_POST['period']."\">";
		  print "<input name=\"room\" type=\"hidden\" id=\"room\" value=\"".$room."\">";
		  print "<input type=\"submit\" name=\Book Room\" value=\"Book ".$row_rooms['name']."\"></td></tr></form>";
		  }
		  } while ($row_rooms = mysql_fetch_assoc($rooms));
		  print "</table>";
		  ?>			  

 

 

here are a couple of screenshots maybe they will help..

http://www.dizine.co.uk/shot.gif

http://www.dizine.co.uk/shot2.gif

 

Thanks for your time,

Scott.

[Jessica]

See how when you do print_r($bookings), it only shows 2 and 6 as keys? Obviously 1 isn't being selected for some reason. Are you sure it should be in the result set?

Edit: Just saw the database shot. One second.

[slawrence10]

yep...if u have a look at shot 2 u can see the record is there: same week id, day and period as the other two...but for some reason not showing hmmm ??? :-\

 

Edit: ha ha Ok

[Jessica]

You don't have mysql_fetch_assoc($booked) anywhere BEFORE

while($row_booked = mysql_fetch_assoc($booked)){

do you? Because that will take the first row, then the while moves it to the second before you can put the first in the array.

[slawrence10]

mysql_select_db($database_connect, $connect);
$query_booked = sprintf("SELECT * FROM booking WHERE week_id = %s AND period = %s AND day = %s ORDER BY id ASC", GetSQLValueString($colname_booked, "int"),GetSQLValueString($colname2_booked, "int"),GetSQLValueString($colname3_booked, "int"));
$booked = mysql_query($query_booked, $connect) or die(mysql_error());
$row_booked = mysql_fetch_assoc($booked);
$totalRows_booked = mysql_num_rows($booked);

 

It's in the coding done by dreamweaver ^second from last line^, would it be safe to remove the statement $row_booked = mysql_fetch_assoc($booked) from there?

 

Regards and thanks for your time,

Scott.

 

[Jessica]

Yep, take out those last two rows. We don't need them as we are using a while loop to get the results.

 

Seriously, don't use dreamweaver - you're making it a lot harder on yourself in the long run.

[slawrence10]

It works, awesome thank you!

 

I've never set up queries without using dreamweaver's recordsets...I guess i should really venture into writing it myself

 

Thanks again for your time, much appreciated!

 

Scott