PHP Query within a while fetch loop

Question

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 !

you should show us both table data

krishn Patel
– krishn Patel

2017-01-17 08:40:07 +00:00
Commented Jan 17, 2017 at 8:40 — krishn Patel
– krishn Patel, Commented Jan 17, 2017 at 8:40

user2447161 · Accepted Answer · 2017-01-17 08:44:59Z

2

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
}

answered Jan 17, 2017 at 8:44

user2447161

2774 silver badges13 bronze badges

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

2 Comments

Gawet Over a year ago

It works ! I'm not used to JOIN request, but I guess I have to give a try sometimes, thank you !

user2447161 Over a year ago

You welcome. Yes, you have to. It'll save you time and performance

Patrick Vogt · Accepted Answer · 2017-01-17 08:50:13Z

0

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;

answered Jan 17, 2017 at 8:50

Patrick Vogt

9263 gold badges16 silver badges35 bronze badges

Collectives™ on Stack Overflow

PHP Query within a while fetch loop

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related