Group by in Active Record

[eluser]mattnewark[/eluser]
Hi Everyone,

I'm trying to group dates together and show them in a view as previous news items,

Controller

Code:

$data['pnews'] = $this->site_model->get_previous_news();

Model

Code:

$this->db->order_by('date', 'asc');
        $this->db->group_by('Month(date)');
        $this->db->where('category', 1);
        $query = $this->db->get('articles');
        if ($query->num_rows() > 0)
        {
            return $query->row();
        }
        return FALSE;

View

Code:

<?php foreach ($pnews as $p) { ?>
     <li><a href="#">&lt;?php echo date('F Y', strtotime($p['date'])) ?&gt;</a></li>
                                    &lt;?php } ?&gt;

I maybe doing something very wrong but I have no idea what it is, what is shown in the view is a list of dates but do not really relate to my test entries. Some of the dates are from January 1970 but there are more entries being produced than entries in db.

Does any one have any idea what I am doing wrong??

Thanks

[eluser]InsiteFX[/eluser]
Your query is going to depend on how you stored the datetime in your table,
if it's a datetime field or timestamp then you will need to use an sql query to
get your information sorted the way you want.

In a blog I usually store it like time month day year all in separate fields.

Code:

$this->db->group_by(array("month", "date"));

[eluser]mattnewark[/eluser]
Hi,

Thanks for the info, unfortunately this didn't work.

The date is datestamp.

That is why I have done the below:

Code:

group_by('month(date)')

Which I assumed would get the month of the timestamp.

I'm just unsure where it is getting the other dates 'January 1970' from as I do not have these in the DB.

Thanks

[eluser]mattnewark[/eluser]
Hi,

I have worked it out, something silly;

it should have been this:

Code:

return $query->result_array();

instead of this:

Code:

if ($query->num_rows() > 0)
            {
            return $query->row();
            }
            return FALSE;

But anyway I think it will work a treat now.