Joining two tables to produce multiple end result not standard

Hi All

first thanks for taking a look i am using PHP and MySQL 5.

I am building an online booking system for a group of hotels and what I am trying to achieve is select all from a table called hotels so it shows each individual hotel but under where each hotel has its name to count from a table called bookings and then return say X hotel has 4 rooms click here to book online and if they do not have any available rooms then say something like no rooms available for these dates.

The first code works fine and selects the hotels.


// Make the query.
$query = “SELECT * FROM hotels ORDER BY RAND()”;
$result = mysql_query ($query); // Run the query.

// Fetch and print all the records.
$bg = ‘#eeeeee’; // Set the background color.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$bg = ($bg==’#0AA2A7’ ? ‘#F4F7F8’ : ‘#0AA2A7’); // Switch the background color.
print ’

’ . $row[‘name’] . ’

'. $row[‘short’]. ’



mysql_free_result ($result); // Free up the resources.
mysql_close(); // Close the database connection.

?> [/php]

What this does is show each hotel a short description and a link to a page with more information.

The next stage is for who ever is looking to select dates that they would like to come and then show each hotel as above but with the number of rooms available or not as the case may be. Here is the code and this works for an individual hotel.

$d = $_POST[‘d’];
$m = $_POST[‘m’];
$y = $_POST[‘y’];
$d1 = $_POST[‘d1’];
$m1 = $_POST[‘m1’];
$y1 = $_POST[‘y1’];
include ‘config.php’;
include ‘date.class.php’;
$start = new Date(false ,$d, $m, $y);
$end = new Date(false ,$d1, $m1, $y1);
$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().”)))”;
$result = mysql_query($query) or die (mysql_error());
if (mysql_num_rows($result)>0){
while ($row = mysql_fetch_assoc($result)) {
$prices = array();
$q = “SELECT * FROM price WHERE (hid = ‘$hid’) AND rid = “.$row[‘rid’].” AND ((year = $y) OR (year = $y1))”;

$r1 = mysql_query($q) or die (mysql_error());
while ($rowa = mysql_fetch_assoc($r1)) {
$prices[$rowa[‘year’].$rowa[‘month’]] = $rowa[‘price’];


$days = $start->daysToPay($end);
$total = 0;
foreach($days as $d=>$t){
//$w = str_split($d, 4);
//echo $w[0].’ ‘.$w[1].’ ‘.$t.’
$total = $total + ($t * $prices[$d]);

//$pid = $row[5];
//$hid1 = $row[6];
//$rid1 = $row[7];
//$number1 = $row[8];
//$rmonth = $row[9];
//$ryear = $row[10];
//$price1 = $row[11] * $montha;
//$pid1 = $row[12];
//$hid12 = $row[13];
//$rid12 = $row[14];
//$number2 = $row[15];
//$rmonth1 = $row[16];
//$ryear1 = $row[17];
//$firstprice = $row[18];
//$price2 = $row[18] * $month2;
//$total = $price1 + $price2;
$booking =’

$booking1 = ’

'; echo "$booking"; echo'Room Number'."\n\n"."$number
"; echo "$description
"; echo'The Price Is GBP'."\n\n".'£'."\n"."$total
"; echo "$booking1

"; } } } [/php]

What this bit does is check for rooms in a table called bookings and in a table called rooms. Each room has an id called rid and that is in the room table and the bookings table. Each hotel has an id called hid and that is in the tables hotels and rooms. There is a query for prices but this is not needed now. As I say I need to run the query show it shows each hotel and just the number of rooms available. d m y and d1 m1 y1 are the arrival and departure dates. If by chance anyone has any ideas or can help it would be great. I can build it for individual hotels just not for multi hotels.

Thank you for looking.

You should post your structures for all 3 tables

What does this do:

SELECT a.*, COUNT(b.`rid`) AS `num_available` FROM `hotels` AS a, `rooms` AS b WHERE a.`hid` = b.`hid` AND b.`rid` NOT IN (SELECT `rid` FROM `bookings` WHERE `hid` = a.`hid`)

Hi thanks for your help very kind. I will give this a go and mark it as resolved.

Well you shouldn’t mark it resolved until you test?

I was just guessing. Without seeing your tables it’s hard to say if that’s a valid query or not

Sponsor our Newsletter | Privacy Policy | Terms of Service