Monthly based pagination

[eluser]Slowcheetah[/eluser]
Does anybody have an idea how to start making a month based pagination?

Something like:
<< Previous Month | Selected Month | Next Month >>

This is my current model:

Code:

function retrieve_all_agenda_items() {
        $this->db->select('productions.id, productions.title, languages.language, dates.date, dates.time, dates.description');
        $this->db->from('productions', 'languages', 'dates', 'productions_has_dates');
        $this->db->join('languages', 'languages.id = productions.languages_id');
        $this->db->join('productions_has_dates', 'productions_has_dates.productions_id = productions.id');
        $this->db->join('dates', 'dates.id = productions_has_dates.dates_id');
        $query = $this->db->get();
        return $query->result();
    }

This is the result:

Code:

Array
(
    [0] => stdClass Object
        (
            [id] => 60
            [title] => aaaa
            [language] => NL
            [date] => 2009-08-26
            [time] => 12:09:00
            [description] => 2133
        )

    [1] => stdClass Object
        (
            [id] => 60
            [title] => aaaa
            [language] => NL
            [date] => 2009-08-26
            [time] => 12:09:00
            [description] => 2133
        )

Is started to play something arround but i got stuck. First i started with something like;

Code:

$this->db->limit($num, $offset);
$where = "dates.date >= CURDATE()";
$this->db->where($where);

But then i realised that in this case i could not go to next or previous month.

Then i started something like this:

Code:

$where = "dates.date LIKE '____-".$selectedmonth."-__'";
$this->db->where($where);

But this is not working for multiple years.

I don't really now where to start to solve this headbreaker.

Thanks in advance

[eluser]jedd[/eluser]
Hey Slowcheetah, this looks like an interesting problem.

I think the big problem most people have when looking at new algorithms is they think in terms of existing ways of doing things. I think in this case because you want some links that look a bit like pagination links, that it's going to be handled similarly. I think you also need to really spell out how the system will work under various conditions - I find that helps a lot in the design process.

So, for example, what happens if you have no data for 2009-04 - and you're currently showing page 2009-05 ? Does the previous-month link go to an empty page, or does the previous month link identify, and take you to, 2009-03?

What happens if, for 2009-03, you have more records than will fit on a single page - do you offer real pagination within each month selection?

My initial thinking is that I'd create controller method that showed me a given months' worth of data. It'd be something like show ($month=NULL) and the first line would be to check if NULL, and if so, set $month = $thisyear-$thismonth. I'd be consistent with the ISO8601 approach of MS order, hyphen optional (though I'd probably keep it as it makes it more obvious to users).

In my model I'd have a function that would return a given month's worth of data, and also one that would return the count() for a given month. In my controller I'd do a model->count() for the previous/next month, and cycle forward and backwards either x months, or until I exhausted my table (this is another question you need to answer about how you know you've hit the end, either end - is there a gap that can appear to be an end?

I think this approach is fairly neat, and it allows you to utilise pagination within each month's data set.

[eluser]Slowcheetah[/eluser]
Hey Jedd,

i have a few certainties:
- i think if a certain month is empty the page just says "no events this date", or something like that.
- there never will be more than 31 events in 1 month. So they will always fit one 1 page.

Im currently working to a sollution that looks like its going to work..

Code:

function retrieve_all_agenda_items($currentmonth) {
        $this->db->select('productions.id, productions.title, languages.language, dates.date, dates.time, dates.description');
        $this->db->from('productions', 'languages', 'dates', 'productions_has_dates');
        $this->db->join('languages', 'languages.id = productions.languages_id');
        $this->db->join('productions_has_dates', 'productions_has_dates.productions_id = productions.id');
        $this->db->join('dates', 'dates.id = productions_has_dates.dates_id');
        $where = "MONTH(dates.date) = MONTH(CURDATE()) +".$currentmonth;
        $this->db->where($where);
        $query = $this->db->get();
        return $query->result();
    }

The current month is always 0
Next month is 1
Month after next one is 2
Previous month is -1
etc

So "http://www.domainname.com/agenda/-2" takes you to month before previous.

The pagination links:

Code:

Previous Month
<?php echo base_url(); ?>agenda/<?php echo end($this->uri->segments) -1 ?>

Current Month
<?php echo base_url(); ?>agenda/0

Next Month
<?php echo base_url(); ?>agenda/<?php echo end($this->uri->segments) +1 ?>

Still have to check how this will react to multiple years.

[eluser]jedd[/eluser]

So "http://www.domainname.com/agenda/-2" takes you to month before previous.

I think this will be where your approach really falls down.

It means you don't have longevity, or consistency, of your URL's. That is, you can't someone a link to a particular page, and be sure that data will be valid in a month from now (or, depending when in the month you send it, a few hours from now).

[eluser]Slowcheetah[/eluser]
@jedd

True about that one, but the “http://www.domainname.com/agenda/-2” is actualy a AJAX call. So it's not a visible human URL. Mine is indeed not the best sollution, but for now it works.