Problem with an agenda/availability query

Question

I have a mysql table with users and their weekly calendar.

Every user can set his own availability for the week (morning, afternoon, night / MON thru SAT), and that is not going to change often, almost never.

Imagine those users are personal trainers in a gym, or tennis courts you can book...

My problem here is to find the right query (or maybe even rethinking the way i'm storing that data in mysql) in order for an external web user to check availability of them based on 3 check buttons [o]morning, [o]afternoon, and [o]night

So I want my web user to go to my website and check/uncheck those buttons in order to see which one (personal trainer, or whatever) is available

So if I check Morning i can see only the people available, also (but not only), in the morning,(because a personal trainer can be available during the morning but also in the afternoon etc..)..

it may sounds an easy problem but i'm having hard time...

any help is appreciated

Thanks!

Answer 1

This isn't really an algorithm question, this is more of a DBA question. You'd most likely have a user table and an availability table.

user:
    userid
    ...

availability:
    userid
    day
    timeofday

When given a query such as Monday Wednesday Morning Afternoon (assuming the relationship is (Monday OR Wednesday) AND (Morning OR Afternoon)) you can do a query such as.

SELECT userid FROM availability WHERE day='wednesday' OR day is 'monday' AND timeofday='morning' OR timeofday='afternoon'

Answer 2

The answer to this question will be dependant on your DB structure. If your are storing the availabile times as 1, 2, 3 or any combo of such 12, 13, 123, 23 then you can simply use a MYSQL Regular expression to limit your results based on the input checkbox criteria.

I would suggest somthing like:

SELECT trainer FROM trainer_table WHERE availaility regexp '[Limiting Criteria]' 

In the above code, simply replace trainer with the name of your fields you wish to return. Then replace trainer_table with the name of your table and finally replace Limiting Criteria with your limiting text, be it 1 or 2 or 3 or any combination.

If you want more specific help, an example of your own table structure would be helpful.