SQL query in PHP: got nothing with printing the array

Question

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.

Codewerks · Accepted Answer · 2008-11-25 02:51:56Z

5

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...

answered Nov 25, 2008 at 2:51

Codewerks

5,9825 gold badges32 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andy Lester · Accepted Answer · 2008-11-25 03:58:02Z

1

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.

answered Nov 25, 2008 at 3:58

Andy Lester

94.2k16 gold badges106 silver badges162 bronze badges

1 Comment

Kris C Over a year ago

This link appears to be out of date - is now at en.wikibooks.org/wiki/PHP_Programming/… by the look of it

staticsan · Accepted Answer · 2008-11-25 05:06:24Z

1

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.

edited Nov 25, 2008 at 5:06

answered Nov 25, 2008 at 3:21

staticsan

30.6k5 gold badges64 silver badges72 bronze badges

2 Comments

Codewerks Over a year ago

Don't you need parentheses around the second section of your where clause? Otherwise, the precedence of those predicates is not clear...?

staticsan Over a year ago

Dang. Yes, you're right. I meant to add those, but got distracted. It's fixed.

Collectives™ on Stack Overflow

SQL query in PHP: got nothing with printing the array

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related