I need to select data from a database based on a weekly event. The events are like this:
User1 data
Mon from 10pm to 2am (tue morning) is EventName1
Tue from 11am to 4pm is EventName2
Wed from 8am to 8pm is EventName3
Thu from 6am to 10am is EventName4
Friday from 5pm to 3am (sat morning) is EventName5
Sat from 4pm to 3am (sun morning) is EventName6
Sun from 5am to 5pm is EventName7
User2 data
Tue from 10am to 3pm is EventName15
User3 data
Tue from 2pm to 4pm is EventName19
User4 data
Tue from 1pm to 8pm is EventName28
20+ users will have entries like this, 7 per week but varying times that could include date overlap. Hopefully its just 1 event per day.
I want to craft a php/sql script to tell me what event is happening right now. PHP would get the current time and date, determine its "Tue at 1pm" and reply with the user having the event and the event name (User1:EventName2, User2:EventName15, User4:EventName28). If its Tue at 5pm, it returns no current events. What hurts my head is working out what happens Saturday at 1am. It should reply back with User1:EventName6
I have not setup a database for this yet as I am not sure how to format it. Do I go with 14 columns with "MonStart" "MonStop" "TueStart" "TueStop" (plus a user column and index column) or do I make 7 rows?
😕
