How to make an archive page

While you are developing a website with posts or news articles. You need to have an archive page. The reason is as time passes the posts get higher in number and its difficult to display all the news in a single page. Besides the loading time takes long. Also finding out articles on a particular date becomes very hard. For the reason we need to write an archive page. Here in this post we will be writing an archive page. So lets get started.

Random articles ->Archived articles

First of all, lets structure the table schema that we are going to use for our articles. The table schema I use for a simple article is as follows:

CREATE TABLE IF NOT EXISTS `post` (
  `id` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  `author` varchar(100) NOT NULL,
  `content` text NOT NULL,
  `tags` varchar(400) NOT NULL,
  `posted_on` datetime NOT NULL,
  `visibility` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

From the table structure you should be clear what are the fields we need to be inserted in the table. However not all play vital role for making archive. Basically we need the posted_on field and id or title to create the archive.And I believe you are capable of doing the insertion post. So, I want to jump write to creating the archive page.
The sql query that lists the years used by our posts is as follows:

"SELECT DATE_FORMAT(posted_on,  '%Y' ) as year FROM post WHERE visibility='1' GROUP BY DATE_FORMAT( posted_on,  '%Y' ) ORDER BY  `year` DESC LIMIT 0 , 30"

To let you know visibility=1 is to make sure all posts with visibility=1 are posts that should be visible those with visibility=0 should not be displayed.So the php code for it will be:

<?php
$sql="SELECT DATE_FORMAT(posted_on,  '%Y' ) as year FROM post WHERE visibility='1' GROUP BY DATE_FORMAT( posted_on,  '%Y' ) ORDER BY  `year` DESC =LIMIT 0 , 30";
$query=mysql_query($sql);
$result=mysql_fetch($query);
foreach($result as $res){ echo $res;}
 ?>