PHP code for moveToNextRecord with MySQL

Hi all,

I'm trying to make a PHP, MySQL booking system. I asked about this in the MySQL forum because I thought I was struggling with the SQL, but I've realised it's the php I'm struggling with.

Basically, I've got several rooms (the number and names of the rooms is changeable), which can be booked for a day, or part of a day. What I want is a table with one cell for each room and each day with all the bookings for that day in it: eg:

|-|A|B|C|
|1|-|-|-|
|2|-|-|-|
|3|-|-|-|

Where A, B, C are the room names and 1,2,3 are the dates.

I have two tables, one of current rooms (called 'rooms'), and another of the bookings (called 'bookings'), with the date and room in it. I use this SQL query to pull out the data

SELECT * FROM bookings RIGHT JOIN rooms ON bookings.Name=rooms.Name ORDER BY bookings.Name, bookings.Date

OldPedant gave me bit help over here (http://codingforums.com/showthread.php?t=179555) when he pointed out the secret is to order by Name and then by Date - he even wrote me out some psuedo code. However, basically, I'm not good enough at php to translate it into proper code.

My query puts it in a list sorted by room and then date, and with the join I've added in, it lists rooms even if there aren't any bookings in them, which is what I want. The problem is, there are gaps in the data - not every room will be booked every day, and I want it to just skip over those values.

This is what's holding me back. I can't work out how to get it so that it if the date in the date column matches the date pulled from the databse it prints it and moves onto the next match, and if it doesn't match, it prints a blank cell and goes onto the next line and sees if those dates match.

I don't suppose anyone could give me a firm shove in the right direction - I'm really not asking anyone to write it for me, but if you could very simply help me with what I'm trying to do, then I'd be able to take it from there.

Thanks everyone for any help you can offer.

Maybe something like this...


$sql = "SELECT * FROM bookings RIGHT JOIN rooms ON bookings.Name=rooms.Name ORDER BY bookings.Name, bookings.Date";
$result = mysql_query($sql);
if(mysql_num_rows($result)>0) {
while($row = mysql_fetch_assoc($result)) {
print_r($row); //this will show an array of the data by row
//do what you want here
}
}


That will iterate over your result set. Is that what you're looking for?

Thanks for that bcarl314, but I'm not sure that's quite what I want.

At the moment I'm printed my results like this:


$result = mysql_query("SELECT * FROM bookings RIGHT JOIN rooms ON bookings.Name=room.Name ORDER BY room.Name, bookings.Date");
while($row = MySQL_fetch_array($result))
{ echo "<tr><td>" . $row['Date'] . "</td><td>" . $row['Name'] . "</td><td>" . $row['Event'] . "</td></tr>

It's a bit difficult to demonstrate what I've got and what I'm trying to do in this page, so I've made a dummy page here which shows it more clearly:

Dummy Example Page (http://imagedissectors.com/help/demo.html)

On here, I've listed currently what I'm getting from my query and how it's displayed, and then how I want the data to be displayed.

Basically, I don't want the data just in a list.

There are two things that are confusing me:

1) How to build the table so that it puts the data in date and room columns and rows, leaving the gaps when there aren't values. If every room was booked every day, I think it would be okay, but that's not the case.

2) When there are two bookings on the same day in the same room, I want them to be in the same cell with a line break between them, but I can't work out how to do that either.

I'm sure this isn't too hard, it's just that my php knowledge isn't good enough. I'm happy to reverse engineer and develop it, if someone could just help me start it off.

Thanks very much.