The plot thickens: tallying records

[Daot Lagorille]

Hello again from your friendly neighbourhod dumbass.

I am working on a php/mysql application that schedules in appointments. One of the functions allows the user to view a calendar of the next 4 weeks, and the date displays red if there are no spots available, and green if there is at least one available.

This calendar is dynamically generated from a calendar table I created, which has a date field and a tally field. The tally field starts at 0, which means there are no spots available.

As I assign staff to a particluar day, the tally goes up +1 for every timeslot that is manned by a staff member.

As people take appointments (they are only allowed to on a day and time when a staff member is available) the tally goes down -1.

I do this using an SQL update statement.

For some reason, the tally get screwed up, even though I have thoroughly tested all functions to make sure that it doesn't happen. I think it does happen when more than 1 person are using the system, but I am not sure.

Is there an easier way to do this? I would like to have a query for each day in the dynamic calendar that queries both the staff availability table and the appointment table, but how do I do that if I do not know beforehand the date?

Actually, as I have been writing this, I think I have figured it out. I will post anyway just in case someone has a better idea, or my solution helps them:

Here is my tentative solution:

Because the dynamic calendar displays 20 days at a time, only mondays through fridays, and starting with today's date, I will create 20 queries.

Each one will have in the where statement something like this:

WHERE available_dte = '$varCurrentDte+1'

then

WHERE available_dte = '$varCurrentDte+2'

And so forth.

Or something.

Alternatively, I will pawn my computer for beer money.

[AdamBrill]

Here is what I would do... ;)

1. Create a table in MySQL called days(or something like that) with the following columns:

id
date
members
customers

You can generate the the rows automatically with the date going in the 'date' column. Next, when a member says they will be there on a certain day, you can increment the 'members' column. Then you can figure out if there are any more slots open by subtracting customers from members. And if a customer says they will be there, you can increment the customers cell...

Anyway, that is how I would do it. If you want to use it, fine. If not, that's fine, too. ;)

[Daot Lagorille]

Aha...

Great minds think alike. That was my original solution, but for some reason the tally for each day keeps going off by a few numbers...

Still working on it...