Have an account? Sign in
Login  Register  Facebook
Can anyone help me out? How to archive by month and year?
Hi guys can anyone help me out how to archive by month and year?
and this data came from my database MySql. And if you click that date it will display the content corresponds to that.

Example :

News for 2010
November 10, 2010
October 10, 2010


News for 2009
November 10, 2009
October 10, 2009
Started: September 22, 2011 Latest Activity: September 22, 2011 phparchives
4 Answers
How to display data using this method?
<?php include("db.php");
$result = mysql_query("SELECT YEAR(local_date) AS year ,MONTH(local_date) AS month ,count(ID) AS posts FROM tbl_localnews
        GROUP BY YEAR(local_date), MONTH(local_date) ORDER BY local_date DESC");
if(mysql_num_rows($result))
	{
while ($row = mysql_fetch_array($result)) {
	print("<br><a href='news.php?year=".$row['year']."&month=".$row['month']."'>News for ".$row['month'].",".$row['year']."</a>");	
 }
}
?>

Posted: roly
In: September 22, 2011

What is the wrong with this guys? Why is it that when i view the month of November 2010 it also view the month of 2009?Can anyone help me?
<?php include("db.php");

$query = mysql_query("SELECT distinct YEAR(local_date) as year FROM tbl_localnews order by local_date desc");
while($row = mysql_fetch_array($query)) {
      //$row = array_unique($r);
      $unique_year = $row['year'];
      echo($unique_year)."<br>";
      
      $query2 = mysql_query("select distinct monthname(local_date) as month from tbl_localnews where local_date like '$unique_year%' order by local_date desc");
      while($r2 = mysql_fetch_array($query2)) {
              //$row2 = array_unique($r2);
            $unique_month = $r2['month'];
			print("<a href='news.php?month=$unique_month'&year=$unique_year>News for $unique_month</a><br>");	
              //echo($unique_month); 
      }

}
?>

Posted:
In: September 22, 2011

use my method will show you the year and each month has posts
September 22, 2011

try this method
SELECT YEAR( post_date ) AS `year` ,
            MONTH( post_date ) AS `month` ,
                count( ID ) AS posts
    FROM posts_table
        GROUP BY YEAR( post_date ) , MONTH( post_date )
             ORDER BY post_date DESC
this will show in example:
year  |  month  |  posts
2010  |  8      |  15
2010  |  9      |  11
this is the wordpress method to show the full archive but if you want a simple method like show all the posts under each month like:

2009

  • 12.11. Title
  • 03.03. Title
  • 01.01. Title

2008

  • 11.12. Title
  • 04.03. Title
  • 02.03. Title
  • 16.02. Title
and this is that metohd with the smarty
$rows = $db->query(\'select year, title from news\');
$smarty->assign(\'news\', $rows);
{if count($rows) > 0}
  {assign var=lastYear value=0}
  <ul><li>
  {foreach name=foo from=$rows item=item}
    {if $smarty.foreach.foo.first}
      {$row.year}<ul>
    {elseif $lastYear != $row.year}
      </ul></li><li>{$row.year}<ul>
    {/if}
    {assign var=lastYear value=$row.year}
    <li>{$row.title}</li>
  {/foreach}
  </ul></li></ul>
{/if}
The other one is to group the values in php:
$rows = $db->query(\'select year, title from news\');
$news = array();
foreach($rows as $row) {
  $news[$row[\'year\']][] = $row[\'title\'];
}

$smarty->assign(\'news\', $news);
<ul>
{foreach from=news key=year item=rows}
  <li>{$year}<ul>
  {foreach from=$rows item=row}
    <li>{$row}</li>
  {/foreach}
  </ul></li>
{/foreach}
</ul>

Posted: MacOS
In: September 22, 2011

I don't get it. :(
September 22, 2011

do you want a full year/months archive like the wordpress one?
September 22, 2011

what about
GROUP BY YEAR(record_date), MONTH(record_date)
Check out the date and time functions in MySQL.

Posted: greentree
In: September 22, 2011

this is the right method for that
September 22, 2011

Your Answer

xDo you want to answer this question? Please login or create an account to post your answer