Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Oct 2009
    Thanked 0 Times in 0 Posts

    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:


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

  • #2
    Mega-ultimate member
    Join Date
    Jun 2002
    Winona, MN - The land of 10,000 lakes
    Thanked 45 Times in 42 Posts
    Maybe something like this...

    PHP Code:
    $sql "SELECT * FROM bookings RIGHT JOIN rooms ON bookings.Name=rooms.Name ORDER BY bookings.Name, bookings.Date";
    $result mysql_query($sql);
    mysql_num_rows($result)>0) {
    $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?

  • #3
    New to the CF scene
    Join Date
    Oct 2009
    Thanked 0 Times in 0 Posts
    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:

    PHP Code:
    $result mysql_query("SELECT * FROM bookings RIGHT JOIN rooms ON bookings.Name=room.Name ORDER BY room.Name, bookings.Date");   
    $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

    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.


    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts