CODEWITHSUNDEEP
phpmysqlsqlpdowhile-loop
Gawet
Gawet

Reputation: 315

PHP Query within a while fetch loop

I was wondering if it was possible to run a query inside a while loop which is used to display the content of a SQL table.

Here is the code if I'm not clear enough :

$sql="SELECT * FROM hotels WHERE rooms>0";
                        $req=$db->query($sql);

                        while($row=$req->fetch()){
                           //The second query to check how many place is left
                            $req2=$db->query('SELECT COUNT(*) FROM people WHERE idhotels='.$row["idhotels"].';');
                            echo "hey".$req2;
                            $left_rooms= $row["rooms"] -$req2;
                            echo '<option value="'.$row["idhotels"].'">'.$row["name_hotel"].' ('.$left_rooms.' rooms left)</option>';

                        }

What I'm trying to do here, is to display a list of hotels with the number of rooms left. The problem is I have to count how many rooms are taken before displaying the number of rooms left, hence the second request.

My code obviously doesn't work, but I can't figure out why. Can someone help me ?

Many thanks !

Upvotes: 0

Views: 4235

Answers (2)

Patrick Vogt
Patrick Vogt

Reputation: 916

Have you tried to calculate your left rooms in the database with a joined query like:

SELECT rooms - COUNT(*) AS left_rooms FROM hotels h WHERE rooms > 0 JOIN people p ON (p.idhotels = h.idhotels) GROUP BY h.idhotels, h.name ORDER BY left_rooms ASC;

Upvotes: 0

user2447161
user2447161

Reputation: 277

Why not using a join and a group by so you only have one query ?

$sql="SELECT h.idhotels,h.name_hotel,count(*) FROM hotels h inner join people p on h.idhotels = p.idhotels WHERE h.rooms>0 group by h.idhotels,h.name";
while($row=$req->fetch()){
    // Here do whatever you want with each row
}

Upvotes: 2

Related Questions