Head ache of a query

[chrispos]

Hi All hope you are all having a good day.

 

I am building a hotel booking site and it shows only rooms that are available. All this works fine and the tables are set up in such a way. The hotel name and description are in one table with hid identifying each hotel. I then have a table for rooms in each hotel with hid used to link the rooms with each hotel. I have another table called bookings and when someone books a room it goes in there. So when someone runs a query only available rooms show up. What I would like to do is show all the hotels, each hotel name and then the available rooms in that hotel.

 

I am using MySQL 5 and PHP 5

 

This is the query that works to show the available rooms

$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().")))";

rid is in the rooms table and is used to identify what room is what. 

[Barand]

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()."))
        )

Seems to be a bit of contradiction between

 

startdate >= X AND startdate < X

 

now we can see the whole query

[chrispos]

Thanks for taking a look OK I don't think I explained this very well but here goes for round two.

 

If you go into say booking.com or laterooms.com you have the option of selecting the dates for your holiday then it brings up hotels with the rooms that they have full or empty. The script above just shows rooms available and works fine.

 

What i am trying to do is get each hotel to show with available rooms. As I have said the script above works fine no problems with showing available rooms. It is the bit of showing the Hotel name and under that the available rooms.

 

This is the script for the hotel search and i have included the PHP as I think it will make more sense

<?php
		include_once 'config.php';
$display = 20;

// Determine how many pages there are. 
if (isset($_GET['np'])) { // Already been determined.

	$num_pages = $_GET['np'];

} else { // Need to determine.

 	// Count the number of records
	$query = "SELECT COUNT(*) FROM `hotels`";
	$result = mysql_query ($query);
	$row = mysql_fetch_array ($result, MYSQL_NUM);
	$num_records = $row[0];

	// Calculate the number of pages.
	if ($num_records > $display) { // More than 1 page.
		$num_pages = ceil ($num_records/$display);
	} else {
		$num_pages = 1;
	}
	
} // End of np IF.

// Determine where in the database to start returning results.
if (isset($_GET['s'])) {
	$start = $_GET['s'];
} else {
	$start = 0;
}
		
// Make the query.
$query = "SELECT * FROM `hotels`ORDER BY RAND() LIMIT $start, $display";		
$result = mysql_query ($query); // Run the query.

// Table header.
echo '<table align="center" cellspacing="0" cellpadding="5">
<tr>
	<td align="left"><p class="text1"><b>Image</b></p></td>
	<td align="left"><p class="text1"><b>Name</b></p></td>
	<td align="left"><p class="text1"><b>Description</b></p></td>
</tr>
';

// Fetch and print all the records.
$bg = '#eeeeee'; // Set the background color.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
	$bg = ($bg=='#CCCCCC' ? '#9B9B9B' : '#CCCCCC'); // Switch the background color.
	echo '<tr bgcolor="' . $bg . '">
		<td align="left"><img src="' . $row['image'] . '"></td>
		<td align="left"><p class="text1"><b>' . $row['name'] . '</b></p></td>
		<td align="left"><p class="text1">' . $row['facilities'] . '</p></td>
	</tr>
	';
}

echo '</table>';

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

mysql_close(); // Close the database connection.

// Make the links to other pages, if necessary.
if ($num_pages > 1) {
	
	echo '<br /><p>';
	// Determine what page the script is on.	
	$current_page = ($start/$display) + 1;
	
	// If it's not the first page, make a Previous button.
	if ($current_page != 1) {
		echo '<a href="index.php?s=' . ($start - $display) . '&np=' . $num_pages . '">Previous</a> ';
	}
	
	// Make all the numbered pages.
	for ($i = 1; $i <= $num_pages; $i++) {
		if ($i != $current_page) {
			echo '<a href="index.php?s=' . (($display * ($i - 1))) . '&np=' . $num_pages . '">' . $i . '</a> ';
		} else {
			echo $i . ' ';
		}
	}
	
	// If it's not the last page, make a Next button.
	if ($current_page != $num_pages) {
		echo '<a href="index.php?s=' . ($start + $display) . '&np=' . $num_pages . '">Next</a>';
	}
	
	echo '</p>';
	
} // End of links section.
?>

This is a pagination script and the hotels come out in a random order. But what I would like to do is as the Hotel name comes out show the available rooms by using the first script. hid is the hotel id and rid is the room id. There is a php script called date class and works by posting arrival day date month departure day date month.

 

As you have asked to see the full script for the room search here it is

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.'<br>';
    $total = $total + ($t * $prices[$d]);

}
$ridr=$row['rid'];
$hid=$row['hid'];
$number=$row['number'];
$description=$row['description'];
$sleeps=$row['sleeps'];

$booking ='<form id="form1" name="form1" method="post" action="/checktobook1b.php">
<input name="stdate" type="hidden" id="stdate" value="' . $stdate . '" />
<input name="dpdate" type="hidden" id="dpdate" value="' . $dpdate . '" />
<input name="ridr" type="hidden" id="id" value="' . $ridr . '" />
<input name="hid" type="hidden" id="hid" value="' . $hid . '" />
<input name="number" type="hidden" id="number" value="' . $number . '" />
<input name="total" type="hidden" id="total" value="' . $total . '" />
<input name="startdate" type="hidden" id="startdate" value="' . $start->getTime() . '" />
<input name="enddate" type="hidden" id="enddate" value="' . $end->getTime() . '" />';
$booking1 = '<label>
<input type="submit" name="Submit" value="Book This Room" />
</label>
</form>';
echo "$booking";
echo'Room Number'."\n\n"."$number<br>";
echo "$description<br>";
echo'The Price Is GBP'."\n\n".'£'."\n"."$total<br>";
echo "$booking1<br><br>";
}
}
} 
?>

d m y are day month year sorry if that sounds silly but you never know. Any help would be goo but if not thanks for looking

Edited April 12, 2013 by chrispos