[eluser]Daniel H[/eluser]
Hi guys,
I'm trying to create some very simple events calendaring functionality for a site but I can't get my head round a problem.
Events have a DateFrom and DateTo fields, and what I need to do is for a specified month, return all the days that have events currently on. Therefore, and event with DateFrom of 2008-03-10 and DateTo of 2008-03-13 will return 10, 11, 12, and 13 if the month is March. A more complex example would be a DateFrom of 2008-02-21 to 2008-03-02 will return 1 and 2, while events that span m-1 to m+1 will return all days from 1 to 31.
Imagine somebody already has this functionality either as an SQL query or some PHP so if you could help me out that'd be great.
Thanks,
Dan.
[eluser]MadZad[/eluser]
dajaniel,
I'm not sure that I'm grasping all your needs, but when dealing with date ranges spanning months and years, I usually find solutions in PHP functions. For example, to get the date 3 days from now: strtotime("+3 day")
Then I would use date to see if the result has a different month or year.
i.e. if (date("m") != date("m", $strtotime("+3 day")) //different month than today
So, perhaps for your example, you could loop through an event's days and reject the ones where the month/year does not match your target month/year.
Check out php.net, functions like date, getdate, strtotime, mktime, plus the other related functions that will be in the left nav. Indispensable on occasion, mktime can automatically turn dates like "January 32" into "February 1". Also, I often find the user-posed examples that follow the official documentation to be very useful. Best of luck!
[eluser]Daniel H[/eluser]
Thanks m. I've been wrangling with these for a while but doesn't achieve the right result. Essentially what I need to do in silly pseudo code is this:
- For the given month, how many days are there
- For each of these days (e.g. 1 to 31), identify whether any of the events in the db land on this day.
- If so, add an element to the array with the day number as the key and a link as its content.
- Otherwise, move onto the next day of the month.
It's the 2nd step that I'm struggling with for spanning events. For example, say that the current day in the while loop is 2, how can I check that an event's DateFrom and DateTo span across that day?
[eluser]MadZad[/eluser]
As always, your mileage may vary, but my initial thought is to do this work in timestamps. When storing an event from Feb 27 to Mar 2 in the DB, I would store the beginning timestamp as mktime(0, 0, 0, 2, 27, 2008) and the ending timestamp as mktime(23, 59, 59, 3, 2, 2008), assuming you are only concerned about starting dates, not times.
Then, so see what events exist for Mar 1, I'd loop through the events with this pseudocode:
Code: $target_ts = mktime(12, 0, 0, 3, 1, 2008);
foreach ($event_results as $this_event) {
if ( ($this_event['start_timestamp'] >= $target_ts) and
($this_event['end_timestamp'] <= $target_ts)) {
//Add this event to the list for Mar 1
}
}
The loop through the days of the month would surround this code. If this is to inefficient, there are ways to be smarter:
* The event query could pick only relevant events by comparing timestamps in the where clause - highly recommended if you have a non-trivial number of events
* If above not feasible, pre-loop through the events and compare begin/end timestamps (compute timestamps if you're unable to get those added to db) and put the ones anywhere in the month into an array. Then loop above would only go through events in memory.
Also, if you haven't discovered it, the first point is covered by a function in CI's date helper, days_in_month.
Hope this helps...
[eluser]Daniel H[/eluser]
Very good point - I was flailing around using LIKE 2008-07-% before!! I'll give it a shot.
Thanks for your help - will let you know how I get on.
[eluser]Daniel H[/eluser]
Okay - that worked a treat. But now I have another issue!!
So to reiterate from the start, I have a table, called 'event'. Each event has a DateFrom and a DateTo field.
I want to return all events that fall on a specified month/year combination (basically to return 'Events currently on in xMonth xYear').
Let's take March 2008 as an example. There are 5 possible date combinations for events that could be returned for this month. Examples of these are:
- 2008-03-10 to 2008-03-10 (i.e. a single day event)
- 2008-03-05 to 2008-03-08 (i.e. a 'span' within the month)
- 2008-03-10 to 2008-04-10 (i.e. a span that breaches the 'upper
limit' of the month)
- 2008-02-10 to 2008-03-10 (i.e. a span that breaches the lower limit
of the month)
- 2008-02-10 to 2008-04-10 (i.e. a span that breaches both limits)
However these days could be any dates possible. (I.e. an event that spans 1 Jan to 30 Nov would be returned for March).
All dates are timestamps. Upper and lower limits are simply timestamps constructed by: (please excuse pseudo)
lower = timestamp(varYear, varMonth, 01)
upper = timestamp(varYear, varMonth, days_in(varMonth))
Can you think how the WHERE should be constructed? I can deal with the first two scenarios but anything else is beyond me.
I just want to return a list of events (rather than days as before) that lie in a given month (but which could span multiple months either side of the given month).
(Interestingly, this has confounded 5 obscenely talented technology consultants...)
Thanks,
Dan.
[eluser]Daniel H[/eluser]
Ignore that - just worked it out:
WHERE
(DATEFROM >= LOWERLIMIT AND DATETO <= UPPERLIMIT) OR
(DATEFROM >= LOWERLIMIT AND DATETO >= UPPERLIMIT AND DATEFROM <= UPPERLIMIT) OR
(DATEFROM <= LOWERLIMIT AND DATETO <= UPPERLIMIT AND DATETO >= LOWERLIMIT) OR
(DATEFROM <= LOWERLIMIT AND DATEFROM >= UPPERLIMIT)
[eluser]hotmeteor[/eluser]
Hey Dan
Please post here with your results for the Calendar. I've done quite a bit of work on this type of calendar, but got stuck on a few issues and would like to see how you resolved them. Also, mine is a bit messy so I'm sure I could learn a few things from your execution.
Thanks
[eluser]Daniel H[/eluser]
In the controller. Produced two calendars, this month and next:
Code: $this->load->library('calendar', $prefs);
$varCal = 0;
while ($varCal<=1) :
$varYear = (int) date('Y');
$varMonth = (int) date('m') + $varCal;
$varDays = $this->EventModel->getDays($varYear, $varMonth);
$varCalendars[] = $this->calendar->generate(
date('Y', mktime(0, 0, 0, $varMonth, date("d"), $varYear)),
date('m', mktime(0, 0, 0, $varMonth, date("d"), $varYear)),
$varDays);
$varCal++;
endwhile;
$varEventData['varCalendars'] = $varCalendars;
In the view:
Code: <?
foreach ($varCalendars as $varCalendar) :
?>
<?= $varCalendar ?>
<hr class="space"/>
<?
endforeach;
?>
The getDays() function in the event model. This is pretty inefficient but can't work out how to do it any other way:
Code: function getDays($varYear, $varMonth)
{
$varMonth = (int) $varMonth;
$varYear = (int) $varYear;
$varDaysInMonth = days_in_month($varMonth, $varYear);
$this->db->select('DateFrom, DateTo');
$this->db->order_by('EventName');
$query = $this->db->get('event');
if ($query->num_rows()>0) :
$varResults = $query->result_array();
$varDay = 1;
while ($varDay<=$varDaysInMonth) :
foreach ($varResults as $varResult) :
$varTargetTimestamp = mktime(0, 0, 0, $varMonth, $varDay, $varYear);
if ((strtotime($varResult['DateTo']) >= $varTargetTimestamp) &&
(strtotime($varResult['DateFrom']) <= $varTargetTimestamp)) :
//Add this event to the list for this day
$varDays[$varDay] = '/event/listing/' . $varYear . '/' . $varMonth . '/' . $varDay;
endif;
endforeach;
$varDay++;
endwhile;
if (!empty($varDays)) :
return $varDays;
else:
return false;
endif;
else:
return false;
endif;
}
|
