php - Algorithm to combine / merge date ranges

Question

I am trying to find the best way on how to merge date ranges into one database record (array element).

This is the data I have:

  Array
(
    [0] => Array
        (
            [id] => 18298
            [start_date] => 2011-07-09
            [end_date] => 2011-10-01
        )

    [1] => Array
        (
            [id] => 18297
            [start_date] => 2011-06-01
            [end_date] => 2011-06-30
        )

    [2] => Array
        (
            [id] => 17113
            [start_date] => 2011-03-31
            [end_date] => 2011-05-31
        )

    [3] => Array
        (
            [id] => 20555
            [start_date] => 2011-01-03
            [end_date] => 2011-03-31
        )
)

And after we combine them, array (or database) should look like this:

Array
(
    [0] => Array
        (
            [merged_ids] => 18298
            [start_date] => 2011-07-09
            [end_date] => 2011-10-01
        )

    [1] => Array
        (
            [merged_ids] => 18297, 17113, 20555
            [start_date] => 2011-01-03
            [end_date] => 2011-06-30
        )
)

Is there any algorithm to go through all elements/ranges and combine them? Which way is better/easier to do - through database (MYSQL) or coding (PHP)?

Any advise is highly appreciated.

Thanks!

UPDATE: Sorry, I didn't provide enough info: we should merge any continuous and overlapping date ranges.

Answer 1

Sort by start date.

Then iterate through and check for if the next item's start date is before or directly after the current one's end date. If it is, then merge the next one into the current one. Then continue.