DO WHILE in a query

[rhecker]

I have a room booking process where the person booking a room may be put in a room that already has other occupants. For instance, some rooms hold five guests. We do not put someone in a room that has occupants of the opposite sex, and when a room reaches it’s maximum capacity it becomes unavailable.

The problem with the query I wrote below is that it works fine if the room in question is consistently empty or consistently occupied for the length of the requested stay, but if, for instance, the guest wants to stay for four days and the first two days a room is empty (and so available) and the next two days the room is occupied by one other person (but still available), the query returns negative. It seems obvious that the solution is to query for each separate day of stay, not the whole length of stay, then analyze each day’s availability. Solving this exceeds my knowledge of SQL.

I appreciate any thoughts.

$sql=$dbh->prepare("SELECT
        room_name,
        room_type_name,
        counted, 
        num_guests, 
        room_id 
FROM     (SELECT * FROM room
WHERE     room.client_id=:client_id AND 
        room.status=1 AND
        room_type=:room_type_id AND
(NOT EXISTS (
SELECT     * FROM invoice
WHERE    client_id=:client_id AND
        product_type = 'LODGING' AND 
        date1<:date2 AND date2 >:date1 AND 
        commit_invoice=1 AND
        room_id = product_id AND
        gender != :gender
        ) OR
(NOT EXISTS (
SELECT     * FROM invoice
WHERE    client_id=:client_id AND
        product_type = 'LODGING' AND 
        date1<:date2 AND date2 >:date1 AND 
        commit_invoice=1 AND
        room_id = product_id
        ))))A 
LEFT JOIN room_type_content ON
        room_type_content.room_type_id=:room_type_id AND
        language_id='en'
LEFT JOIN(SELECT product_id, count(product_id)AS counted FROM invoice
WHERE    gender =:gender AND 
        client_id=:client_id AND
        product_type = 'LODGING' AND 
        date1<:date2 AND date2 >:date1 AND 
        commit_invoice=1 AND
        invoice_set !='SYSTEM'
GROUP BY product_id
        )B
ON        B.product_id=A.room_id 
ORDER BY counted DESC
");