StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I have built this kind of calendar before. I found the best way to do it is to approach it the way that crons are scheduled. So in the database, make a field for minute, hour, day of month, month, and day of week. </p> <p>For an event every Friday in June and August at 10:00pm your entry would look like</p> <pre><code>Minute Hour DayOfMonth Month DayOfWeek 0 22 * 6,8 5 </code></pre> <p>You could then have a field that flags it as a one time event which will ignore this information and just use the start date and duration. For events that repeat that end eventually (say every weekend for 3 months) you just need to add an end date field.</p> <p>This will allow you to select it back easily and reduce the amount of data that needs to be stored. It simplifies your queries as well.</p> <p>I don't think there is a need to create temporary tables. To select back the relevant events you would select them by the calendar view. If your calendar view is by the month, your select would look something like:</p> <pre><code>SELECT Events.* FROM Events WHERE (Month LIKE '%,'.$current_month.',%' OR Month = '*') AND DATE(StartDate) >= "'.date('Y-m-d', $firstDayOfCurrentMonth).'" AND DATE(EndDate) <= "'.date('Y-m-d', $lastDayOfCurrentMonth).'" </code></pre> <p>Obviously this should be in a prepared statement. It also assumes that you have a comma before and after the first and last value in the comma separated list of months (ie. <code>,2,4,6,</code>). You could also create a <code>Month</code> table and a join table between the two if you would like. The rest can be parsed out by php when rendering your calendar.</p> <p>If you show a weekly view of your calendar you could select in this way:</p> <pre><code>SELECT Events.* FROM Events WHERE (DayOfMonth IN ('.implode(',', $days_this_week).','*') AND (Month LIKE '%,'.$current_month.',%' OR Month = '*')) AND DATE(StartDate) >= "'.date('Y-m-d', $firstDayOfCurrentMonth).'" AND DATE(EndDate) <= "'.date('Y-m-d', $lastDayOfCurrentMonth).'" </code></pre> <p>I haven't tested those queries so there maybe some messed up brackets or something. But that would be the general idea.</p> <p>So you could either run a select for each day that you are displaying or you could select back everything for the view (month, week, etc) and loop over the events for each day.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload