Big problem with booking system PHP + SQL

Question

Hello,

I am creating now a new booking system, the part of the private rooms was done and I have now the part of the Dorm (sharing room).

The system works like that:

1. Insert coming day + leaving day + how many persons in the dorm

2. the system checks in the DB (of MYSQL by the way) if the is free room. now how it checks? in the DB has coulmn of dates - one date is person. for example we have a dorm with those dates: 05/08, 05/08, 05/08, 05/08, 06/08, 06/08, 06/08, 06/08, 07/08, 07/08, 07/08, 07/08 - so there is 4 persons in dorm 05-07/08. for example if this room with 10 persons there is 4 of 10. now if you insert 7 people you dont have place. (it will be 11 of 10).

3. The system returns available rooms.

Now I want to do the next thing: If there is 2 dorms. all of them place to 10 persons. all of them with 8 persons of 10. now coming 4 persons and want to register.. it wont give them because it checks dorm-dorm.. I want to do that the system will calculate the free places (there is 10-8 + 10-8 = 4 in 2 dorms). there is ideas?

My english its not very good I hope you will understand me, THANK YOU VERY MUCH!

EDIT:

When I add dorm in the database:

ID | room_type|            persons                   | dates
------------------------------------------------------------------------
X  |  Dorm    |How many the dorm contain. not change.| of persons coming

if you have 5 dates same = there is 5 persons in the dorm in this date. for example.

Answer 1

OK so i'm typing this out of memory so it may need some fine-tuning but it should serve as a guide. Hope it helps.

1) First, let's create a temporary table that stores all of the ocuppied slots for any given room per date:

CREATE TEMPORARY TABLE room_availability
SELECT ID, DISTINCT dates, COUNT(dates) as full_slots FROM table 
WHERE UNIX_TIMESTAMP(CAST(dates AS DATE)) - UNIX_TIMESTAMP(NOW()) > 0
GROUP BY dates ORDER BY ID;

2) Let's get all the available slots for any room:

SELECT table.*, table.persons - room_availability.full_slots as free_slots
FROM table INNER JOIN room_availability ON table.ID = room_availability.ID
where table.persons - room_availability.full_slots > 0

3) Finally, get rid of the temporary table and you're done

PS: the unix timestamp is there to help you list just the future dates.