Create News archives

[eluser]Lykos22[/eluser]
I'd like some help please. I'd like to create a news archive in order to display it on the sidebar. This is what I 'm trying to achieve:

Code:

2014
   January (8) // number of news - months will be links
2013
   December (5)
   November (4)
   October  (15)
   ...

2012
   December (6)
// always show archives from last 3 years, so 2011 and before will not be available

These are the model functions:

Code:

// function get() is in MY_Model.php
public function get($id = null, $single = false){

        if ($id != null) {
            $filter = $this->_primary_filter;
            $id = $filter($id);
            $this->db->where($this->_primary_key, $id);
            $method = 'row';
        }
        elseif($single === true) {
            $method = 'row';
        }
        else {
            $method = 'result';
        }

        if (!count($this->db->ar_orderby)) {
            $this->db->order_by($this->_order_by);
        }
        return $this->db->get($this->_table_name)->$method();
    }

// function get_archieves() is in news_model.php
public function get_archives(){
        $this->db->select('YEAR(`pubdate`) AS year')->select('MONTHNAME(`pubdate`) AS month')
        ->select('COUNT(`news_id`) AS total')
        ->group_by('year, month');
        return parent::get();
    }

and this is my view:

Code:

<ul class="nav nav-sidebar">
       &lt;?php foreach ($archives as $archive): ?&gt;
              <li>
                       &lt;?php echo html_escape($archive->year) . ' (' . html_escape($archive->total). ')';  ?&gt;
                       <ul>
                                <li>&lt;?php echo html_escape($archive->month); ?&gt;</li>
                      </ul>
              </li>
       &lt;?php endforeach; ?&gt;
</ul>

The problem is I get the data like this:

Code:

Array
(
    [0] => stdClass Object
        (
            [year] => 2014
            [month] => April
            [total] => 1
        )

    [1] => stdClass Object
        (
            [year] => 2014
            [month] => March
            [total] => 7
        )

    [2] => stdClass Object
        (
            [year] => 2014
            [month] => February
            [total] => 2
        )

    [3] => stdClass Object
        (
            [year] => 2013
            [month] => December
            [total] => 1
        )
)

[eluser]Tim Brownlaw[/eluser]
Hi,

In your Model You can change this...
return $this->db->get($this->_table_name)->$method();

to

$query = $this->db->get($this->_table_name)->$method();
return $query->result();

Cheers

[eluser]Lykos22[/eluser]
[quote author="Tim Brownlaw" date="1397594078"]Hi,

$query = $this->db->get($this->_table_name)->$method();
return $query->result();

Cheers[/quote]

The method ($method) is set as the result() already, that's why I'm getting all records.

[eluser]Lykos22[/eluser]
The result should return like this:

Code:

Array
(
    [2014]
        (
            [month] => April
            [total] => 1
        )
        (
            [month] => March
            [total] => 7
        )
        (
            [month] => February
            [total] => 2
        )
       .......

    [2013]
        (
            [month] => December
            [total] => 1
        )
        .......
)

[eluser]InsiteFX[/eluser]
Try this.

Code:

public function get_archives(){
        $this->db->select('YEAR(`pubdate`) AS year')->select('MONTHNAME(`pubdate`) AS month')
        ->select('COUNT(`news_id`) AS total')
        ->group_by('year, month')
        ->order_by('year asc, month desc');
        return parent::get();
    }

NOTE: Not tested!

[eluser]CroNiX[/eluser]
[quote author="Lykos22" date="1401371744"]The result should return like this:

Code:

Array
(
    [2014]
        (
            [month] => April
            [total] => 1
        )
        (
            [month] => March
            [total] => 7
        )
        (
            [month] => February
            [total] => 2
        )
       .......

    [2013]
        (
            [month] => December
            [total] => 1
        )
        .......
)

[/quote]
There is nothing in native CI that will return a result set like that. You'd have to do that in your view while in your loop. Just create a variable to store the $current_year before the loop, and in the loop do a compare to see if ($archive->year == $current_year), if not you've hit a new year and set $current_year = $archive->year and then output the year in a new <li>. It's a bit more complex than that but hopefully that will get you started.

[eluser]Lykos22[/eluser]
@InsiteFX: Thanks! I did some small changes though

Code:

public function get_archives($limit_to_months = 12) {
  $this->db->select('YEAR(pubdate) AS year, MONTHNAME(pubdate) AS month, COUNT(article_id) AS total')
  ->where('pubdate >= NOW()-INTERVAL ' . $limit_to_months . ' MONTH')
  ->group_by('year, month');

  return parent::get();
}

This return results like this

Code:

Array
(
    [0] => stdClass Object
        (
            [year] => 2014
            [month] => May
            [count] => 2
        )

    [1] => stdClass Object
        (
            [year] => 2014
            [month] => April
            [count] => 1
        )

    [2] => stdClass Object
        (
            [year] => 2014
            [month] => March
            [count] => 3
        )

    [3] => stdClass Object
        (
            [year] => 2014
            [month] => February
            [count] => 2
        )

    [4] => stdClass Object
        (
            [year] => 2014
            [month] => January
            [count] => 3
        )

)

and this is how I display them on my vew

Code:

<ul>
&lt;?php foreach ($archives as $archive): ?&gt;
   <li>&lt;?php echo anchor('#', html_escape($archive->year) . ' ' . html_escape($archive->month)
     . ' (' . html_escape($archive->count) . ')'); ?&gt;</li>
&lt;?php endforeach; ?&gt;
</ul>

// ----------------------------------------------------------------------------

2014 May (2)
2014 April (1)
2014 March (3)
etc etc

[eluser]Lykos22[/eluser]
I have one more question, if possible. How can I display the data in two collumns? So that It will look like this

Code:

<ul>
<div class="row">
         <div class="col-md=6&gt;
                   &lt;li&gt;2014 May (2)&lt;/li&gt;
                   &lt;li&gt;2014 April (1)&lt;/li&gt;
                   &lt;li&gt;2014 March (3)&lt;/li&gt;
         &lt;/div&gt;
         &lt;div class="col-md=6>
                   <li>2014 February (2)</li>
                   <li>2014 January (1)</li>
                   <li>2013 December (3)</li>
         </div>
</div>
</ul>

[eluser]Tpojka[/eluser]
If want to use tw bootstrap >3.0,
there is type in code. Should be

Code:

<div class="col-md-6">

it's - not =

edit:
use mod or div functions for opening and closing tags

Code:

<div class="row">
  <div class="col-md-6">
<ul>
&lt;?php $half = ceil(count($archives / 2));?&gt;
&lt;?php foreach ($archives as $key => $archive): ?&gt;
  &lt;?php if ($key > 0 && $key % $half == 0): ?&gt;
  </div>
  <div class="col-md-6">
   <li>&lt;?php echo anchor('#', html_escape($archive->year) . ' ' . html_escape($archive->month)
     . ' (' . html_escape($archive->count) . ')'); ?&gt;</li>
  &lt;?php else: ?&gt;
  <li>&lt;?php echo anchor('#', html_escape($archive->year) . ' ' . html_escape($archive->month)
     . ' (' . html_escape($archive->count) . ')'); ?&gt;</li>
  &lt;?php endif;?&gt;
&lt;?php endforeach; ?&gt;
</ul>
</div> &lt;!-- right column --&gt;
</div> &lt;!-- row --&gt;

disclaimer: not tested