Reputation: 8705
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>
Upvotes: 1
Views: 1440
Reputation: 15044
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.
Upvotes: 2