SQL query in PHP: nothing happened with printing array
I have a query that selects all the matching records in the 'hotels' table and then for each hotel looks for a certain type of booked room in the 'booked_rooms' table and all for a specific period. So, first I retrieve all the hotel_ids from the 'hotel_table' based on the location given in the search form and for each hotel_id I iterate over the 'booked_rooms' table.
Here is the code:
if(isset($_GET['book'])){
$sql=mysql_query("SELECT hotel_id FROM 'hotels' WHERE city='$city") or die(mysql_error());
while($row=mysql_fetch_array($sql)){
$sql_2=mysql_query("SELECT * FROM 'booked_rooms' WHERE hotel_id='$hotel_id'
AND arrival_date BETWEEN '$arrival_date' AND '$departure_date'
OR departure_date BETWEEN '$arrival_date' AND '$departure_date'")
or die(mysql_error());
}
while($row_2=mysql_fetch_array($sql_2)){
print_r($row_2);
}
}
// $city, $arrival_date and $departure date are values retrieved from the search form
The problem is I get a loop through the 'hotel' table and get all the hotel_ids corresponding to the location, but I get nothing when I print the $ row_2 array. I tried to use JOINS in SQL, 'foreach' loop but no luck either.
source to share
Without knowing PHP, can you do this in one request?
SELECT booked_rooms.*, hotels.* FROM 'hotels'
JOIN 'booked_rooms' ON hotels.hotel_id = booked_rooms.hotel_id
WHERE
hotels.city='$city" AND
(
booked_rooms.arrival_date BETWEEN '$arrival_date' AND '$departure_date' OR
booked_rooms.departure_date BETWEEN '$arrival_date' AND '$departure_date')
Check "quotes around your tables" as needed for PHP strings, etc.
source to share
First of all, you have an error in your first SQL because you did not specify your city name properly. Then you won't get hotel_id
from the result set. And then you have a second loop in the wrong place.
Try the following:
if( isset($_GET['book']) ) {
$sql = mysql_query("SELECT hotel_id FROM 'hotels' WHERE city='".mysql_real_escape_string($city)."'") or die(mysql_error());
$arrival_date = mysql_real_escape_string($arrival_date);
$departure_date = mysql_real_escape_string($departure_date);
while( $row = mysql_fetch_assoc($sql) ) {
$hotel_id = $row['hotel_id'];
$sql_2 = mysql_query("SELECT *
FROM `booked_rooms`
WHERE hotel_id = ".$hotel_id."
AND (
arrival_date BETWEEN '".$arrival_date."' AND '".$departure_date."'
OR departure_date BETWEEN '".$arrival_date."' AND '".$departure_date."'
)")
or die(mysql_error());
while( $row_2 = mysql_fetch_assoc($sql_2) ) {
print_r($row_2);
}
}
}
// $city, $arrival_date and $departure date are values retrieved from the search form
I also recommend being more generous with your whitespace. This makes PHP easier to read.
source to share