A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2009 yearBest way to query calendar events? - page 1
User InfoPosts
Best way to query calendar events?#1
I(m creating a calendar that displays a timetable of events for a month. Each day has several parameters that determine if more events can be scheduled for this day (how many staff are available, how many times are available etc).

My database is set up using three tables:

Regular Schedule - this is used to create an array for each day of the week that outlines how many staff are available, what hours they are available etc
Schedule Variations - If there are variations for a date, this overrides the information from the regular schedule array.
Events - Existing events, referenced by the date.

At this stage, the code loops through the days in the month and checks two to three things for each day.

Are there any variations in the schedule (public holiday, shorter hours etc)?
What hours/number of staff are available for this day?
(If staff are available) How many events have already been scheduled for this day?

Step 1 and step 3 require a database query - assuming 30 days a month, that(s 60 queries per page view.

I(m worried about how this could scale, for a few users I don(t imagine that it would be much of a problem, but if 20 people try and load the page at the same time, then it jumps to 1200 queries...

Any ideas or suggestions on how to do this more efficiently would be greatly appreciated!


posted date: 2009-04-09 09:42:00

Re: Best way to query calendar events?#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2009-04-09 09:42:01

Re: Best way to query calendar events?#3
Create a table:t_month (day INT)INSERTINTO t_monthVALUES (1), (2), ... (31)Then query:SELECT *FROM t_month, t_scheduleWHERE schedule_date = (2009-03-01( + INTERVAL t_month.day DAY AND schedule_date < (2009-03-01( + INTERVAL 1 MONTH AND ...Instead of 30 queries you get just one with a JOIN.Other RDBMS(s allow you to generate rowsets on the fly, but MySQL doesn(t.You, though, can replace t_month with uglySELECT 1 AS month_dayUNION ALLSELECT 2UNION ALL...SELECT 31

posted date: 2009-04-09 09:46:00

Re: Best way to query calendar events?#4
I can(t think of a good reason you(d need to limit each query to one day. Surely you can just select all the values between a pair of dates.Similarly, you could use a join to get the number of events scheduled events for a given day.Then do the loop (for each day) on the array returned by the database query.

posted date: 2009-04-09 10:27:00

Re: Best way to query calendar events?#5
I faced the same sort of issue with http://rosterus.com and we just load most of the data into arrays at the top of the page, and then query the array for the relevant data. Pages loaded 10x faster after that.So run one or two wide queries that gather all the data you need, choose appropriate keys and store each result into an array. Then access the array instead of the database. PHP is very flexible with array indexing, you can using all sorts of things as keys... or several indexes.

posted date: 2009-04-15 22:08:00

Re: Best way to query calendar events?#6
Thanks for all of the answers. Putting into into array worked perfectly for my needs. Thanks again!

posted date: 2009-04-17 10:05:00

select page: « 1 »
Copyright ©2008-2017 www.momige.com, all rights reserved.