[RESOLVED] Two tables and two queries but I have an issue
Results 1 to 2 of 2

Thread: [RESOLVED] Two tables and two queries but I have an issue

  1. #1
    Designer
    Join Date
    Mar 2009
    Location
    England
    Posts
    18

    resolved [RESOLVED] Two tables and two queries but I have an issue

    Hi All

    It would be great if someone can help.

    I am building a very simple booking system for a group of small hotels. I have three tables one called hotel, one called rooms and one called bookings. Each hotel has an id called hid and each room in every hotel has the individual hotel id and individual room id called rid. When someone makes a booking it goes into the bookings table. When selected the idea is to show how many rooms are available in each hotel by means of filling out a quick form with $d $m $y and the end date of the vacation being $d1 $m1 $y1. i am trying to show each hotel and number of rooms available in the at hotel. I hope that makes sense.

    So the list should look like Devon Hotel 3 rooms available Cary Hotel 2 rooms available.

    When I break the code down each bit works but when I put it together the problem I have is it shows all the hotels but returns the words 0 rows. This is because the second query cant take the id hid from the first query. Here is the code

    PHP Code:
    include 'config.php';
    include 
    'date.class.php';
    $start = new Date(false ,$d$m$y);
    $end = new Date(false ,$d1$m1$y1);
    $query "SELECT * FROM `hotel`ORDER BY RAND()";        
    $result mysql_query ($query); // Run the query.
    while ($row mysql_fetch_array($resultMYSQL_ASSOC))
     {
        echo 
    '
            <h2>' 
    $row['name'] . '</h2>
        '
    ;
    }
    ;
    $result mysql_query ("SELECT * FROM rooms WHERE (hid = '$hid') AND rid NOT IN (SELECT rid FROM bookings WHERE (hid = '$hid') AND ((startdate >= ".$start->getTime()." OR enddate > ".$start->getTime().") AND (startdate < ".$end->getTime().")))");
    $num_rows mysql_num_rows($result);

    echo 
    "$num_rows Rows";

    mysql_free_result ($result); // Free up the resources.    

    mysql_close(); 
    If anyone has any ideas on how to make it work with the hotel name and available rooms showing underneath I would be very pleased.

    Thank you for looking

    Have a great day and an even better one if you can help

  2. #2
    Designer
    Join Date
    Mar 2009
    Location
    England
    Posts
    18

    Resolved The Issue

    Thank you all for looking. I resolved the issue and it was due to the second query not having the id hid.

    Here is the code should anyone need it or want to see how it was resolved

    PHP Code:
    include 'config.php';
    include 
    'date.class.php';
    $start = new Date(false ,$d$m$y);
    $end = new Date(false ,$d1$m1$y1);
    $query "SELECT * FROM `hotel`ORDER BY RAND()";        
    $result mysql_query ($query); 
    while (
    $row mysql_fetch_array($resultMYSQL_ASSOC))
    if (
    mysql_num_rows($result)>0){
    $hid $row['hid'];
     
    $result1 mysql_query ("SELECT * FROM rooms WHERE (hid = '$hid') AND rid NOT IN (SELECT rid FROM bookings WHERE (hid = '$hid') AND ((startdate >= ".$start->getTime()." OR enddate > ".$start->getTime().") AND (startdate < ".$end->getTime().")))");
    $num_rows mysql_num_rows($result1);
    echo 
    $row['name']."<br>";
    echo 
    $row['hid']."<br>";
    echo 
    "$num_rows Rows<br><br>";
    }

    mysql_free_result ($result); // Free up the resources.    

    mysql_close(); 
    Thank you for looking

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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