Creating a robust event search facility for Derby Museums

It’s been a while since I’ve posted anything here because I’ve been busy working on the prototype for the new Derby Museums website. The prototype has come together very well but there are always going to be a few head-scratching moments. For me, one of these centred around how to search and filter events. The tricky part is that we have single-day events, multi-day exhibitions, three locations and lots of other filters.

I had initially thought of performing specific searches on the database based on the above conditions. So I’d grab the conditions of the users search e.g. ‘Show events between tomorrow and next Thursday’ or ‘Show events for children at Pickford’s House’ and search on that. But it became clear pretty quickly that there are far too many conditions that could come into play to make this clean so I changed tack. Instead I decided to pull in results for all events and exhibitions from a single search and then filter them out with if/else conditions inside a while loop.

Finding events that span multiple dates

Another aspect that increased the complexity a little is searching for events that span multiple dates. As you’d expect the event is stored with it’s start and end dates in the database. Matching an event on a specific day is easy. If I search for events that are running on June 30th all I need to do is match events with start dates on or before June 30th which also have end dates of on or after June 30th. This would turn up any single-day events on that date or any multi-date events running on that date. But what about multi-date event that start and end after our search range? Or events that start before our first search date and that end before our end search date? That condition could legitimately match events that are running for the start of our search range, but it would also find events from any point in the past.

What are all the event search conditions we need to match?

I found it easiest to draw this out visually. In the following example we want to match events 1 – 4 but not return results for events 5 and 6.

[  jan  ][  feb  ][  mar  ][  apr  ][  may  ]
         [ ———————— event1 ———————— ]
         [ ——— event2 ——— ]
                        [ ——— event3 ——— ]
                    

[event4]

[event5]

[event6]

[———search———]

I worked out the following set of search conditions to achieve this:

Condition 1: Finds event1

if event start date is before first search date AND event end date is after last search date

Condition 2: Finds event1 & event2

if event start date is before first search date AND event end date is after first search date

Condition 3: Finds event3

if event start date is before end search date AND event end date is after last search date

Condition 4: Finds event4

if event start date is after first search date AND event end date is before last search date

We can discard Condition 1 as Condition 2 returns those results too. Now that we have our logic we can translate this to code. I put the following inside a PHP while loop.

[codeview]if ( (isset($filter_date_from)) && (isset($filter_date_to)) ) {

if (
( ($eventdate <= $filter_from) && ($enddate >= $filter_from) )
||
( ($eventdate <= $filter_to) && ($enddate >= $filter_to) )
||
( ($eventdate >= $filter_from) && ($enddate <= $filter_to) )
) {
// show results
} else {
// nothing found so skip to next iteration
continue;
}

}[/codeview]

This assumes you’ve set variables based on a user’s desired date range and pulled your events from the database and set variables for start and end dates.

All of the code for displaying the event listing comes after the above code. If an event doesn’t match one of the conditions the continue function will jump the loop to the next iteration.

What the above doesn’t take into account is that Museum locations are shut on Sundays and Mondays. Currently you could search for events on a Monday and receive results based on the above logic. The event is ‘technically’ on for the date you’ve searched but the Museum itself is closed. I’m not sure whether to return these results and point out that the Museum is closed or not to show them at all. That’s one for another day.