Help with SQL query

Question

Hi to all

I'm stuck in trying to solve this problem for a days. I even give up for a while, and since i'm a PHP newbie, i need help.

This is my problem: I have a query that selects all appropriate record in a table 'hotels' and then for each hotel looks for booked room of certain type in table 'booked_rooms' and all of that for certain period. So first i'm taking out all hotel_ids from 'hotel_table', based on the location provided from the search form, and for each hotel_id i loop through the 'booked_rooms' table.

Here's 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 that i get a loop through 'hotel' table and get all the hotel_ids appropriate to the location, but got nothing with printing the $row_2 array. I tried using JOINS in the SQL, 'foreach' loop, but no luck as well. I know it's a trivial questions, but i'm still learning, so any tip is highly welcomed. Thanks in advance.

Answer 1

Not knowing PHP, can you do it in one query?

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 the '' quotes around your tables as necessary for the PHP strings etc...

Answer 2

Thanks for the tip AugustLights.

As i said on the previous post i tried to use JOINS but without any result, and for the query you posted i still have problems with printing fetched data. I guess the problem is in the way i print that data

Answer 3

First of all, you have an error in your first SQL in that you haven't quoted your cityname properly. Then you don't fetch the hotel_id out of the resultset. And then you have the 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'd also recommend being more generous in your whitespace. It makes the PHP easier to read.

Answer 4

Please don't put build SQL queries from outside, untrusted data. This is the Bobby Tables problem.

Please see a page like this one for details on using parameterized statements.