ROW COUNT where a field has the SAME value (not speciefied)

denislav

Hi guys,

I really need to count the number of rows where a field has the same value as others. I'm making a hotel booking system and I have to check if from all rooms there is at least one available at a given time. I can check a room and if already booked, check the next but it's not a very efficient way.
So...I've made a SELECT query that gives me the free rooms with X beds on the right dates. If I count that I know that there are free rooms for that period but I don't know if there is ONE room free on all dates. So is there a way to do something like

count($query) WHERE room number is the same

If possible I'd like a row count (number free days) for every room returned by the $query or a true or false answer if there is at least 1 room where the count is = number days requested. I don't know if this is possible with simple code but any suggestions will be appreciated.
Thanks!

kmunk

I don't get it, do you want to check how many rooms are available in a given timeslot (periode)?

how about booking the rooms from startdate till enddate.

then you can select following:

count(room) where not ($startdate < enddate AND $startdate > startdate) AND not ($enddate > startdate AND $enddate < enddate) AND not ($startdate > startdate AND $enddate < enddate)

$startdate < enddate AND $startdate > startdate

gives you all rooms where $startdate lies between its booking-dates
$enddate > startdate AND $enddate < enddate
gives you all rooms where $enddate lies between its booking-dates

$startdate > startdate AND $enddate < enddate
gives you all rooms where $startdate and $enddate lies between its booking-dates

So if you don't select those you will get any room that is free in the given periode.

This gives you the opportunity to book a room the same day the previous booking ends - so remember to tell your guests that they shouldn't check-in until 3pm 😉

kmunk

If your system is build up on dates and not rooms you can make following statement in stead:

SELECT room, count(*) as days FROM daytable WHERE day > $startdate AND day < $enddate AND occupied = null GROUP BY room;

This counts any day between startdate and enddate and gives you the total number of days it is free - though it wont give you it in a row. So you can encounter problems if the room is booked for one or two nights within the period.

eg.

room 5 is booked 5. and 6. of july

you make a query to see if there is a room with 6 days in the period from 2. of july till the 10. of july.

room 5 has 6 days free namely: 2,3,4,7,8,9,10 but not in a row.

hope it helps