Codeigniter 2.1 - Count data in DB for every month

Question

I have small calendar page where you can click on particular month and get all article from that month. There are some months with no article and I want to avoid part where user click on that month only to discover that it is empty. How can I count all articles for every month in one year and latter present data in view part?

DB for article goes like this:

articles
id_articles -> **number**
title -> **varchar**
text -> **text**
date_created -> timestamp (CURRENT_TIMESTAMP - MM/DD/YYYY HH:MM:SS )

HTML code:

<nav id="arcive" class="clearfix">
        <ul>
            <li>
                <a role=slide rel=2012 class="current" >2012</a>
                <ul rel=2012 class="month">
                    <li>
                        <a href="#">December</a>
                        <a href="#">November</a>
                        <a href="#">October</a>
                        <a href="#">September</a>
                        <a href="#">August</a>
                        <a href="#">July</a>
                        <a href="#">Jun</a>
                        <a href="#">May</a>
                        <a href="#">April</a>
                        <a href="#">March</a>
                        <a href="#">February</a>
                        <a href="#">January</a>
                    </li>
                </ul>
            </li>
    </ul>
    </nav>

Can you post the table structure? =o) Just so we know what types of fields were are dealing with since this will likely require using build in MySQL functions to calculate the month. Also the accompanying HTML form for the calendar and any PHP code that pertains to it. We need more than a list of table columns to be able to do this. — user1592648
– user1592648, Commented Dec 11, 2012 at 22:38
@cryptic I added the HTML code. Table structure from above is all that there is for the articles. — Sasha
– Sasha, Commented Dec 11, 2012 at 23:25

score 2 · Accepted Answer · 2012-12-12 20:05:46Z

2

The PHP to dynamically create the menu would be this:

$year = '2012'; // set this to whatever the year to be queried is

$sql = 'SELECT GROUP_CONCAT(MONTH(`date_created`)) `date_created`  '
     . 'FROM `articles` '
     . 'WHERE YEAR(`date_created`) = ' . (int) $year; // typecast for security

// run query to return list of numeric months with articles
$query = $this->db->query($sql);  
$result = $query->row();
$months = explode(',', $result->date_created);

$articles_per_month = array_count_values($months); // get count of articles per each month    

for ($i = 1; $i <= 12; $i++) // Loop Through 12-months
{
    $month_name = date('F', mktime(0, 0, 0, $i)); // Get the name of the month from the numeric month

    echo (in_array($i, $months)) // Check to see if articles for month, if so create link otherwise SPAN
        ? "<a href='/calendar/month/$i'>$month_name (" . $articles_per_month[$i] . ")</a>\n" // print link with number of articles
        : "<span>$month_name</span>\n";
}

This Will create the menu and if a month does not have articles will print out the month name within SPAN tags, if it does have articles will print out the month as a link along with the number of articles for that month.

edited Dec 12, 2012 at 20:05

answered Dec 12, 2012 at 0:41

user1592648

Sign up to request clarification or add additional context in comments.

7 Comments

MD. Sahib Bin Mahboob Over a year ago

nifty solution with headaches and flu ;)

user1592648 Over a year ago

@MD.SahibBinMahboob thank you, however I was not satisfied with the answer and redid it, Much nicer now =o)

Sasha Over a year ago

@cryptic Sorry to keep you waiting, even noob programmers need to sleep :D. Thank you for the solution :)

Sasha Over a year ago

@cryptic One quick question. Is there any way to count number of articles per month?

Sasha Over a year ago

@cryptic Thank you a lot :). You are a real life saver. If you ever come to Pirot, call me for a drink :)

|

Collectives™ on Stack Overflow

Codeigniter 2.1 - Count data in DB for every month

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related