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
$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($result, MYSQL_ASSOC))
<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.
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