I want to create an archive list like this:

    *Post 1
    *Post 2
     *Post 1

My config file is:

//database credentials
$db = new PDO("mysql:host=".DBHOST.";dbname=".DBNAME, DBUSER, DBPASS);
//set timezone

the table I m using is having postDate as Datetime. postSlug is used to get a clean url. The coding I am using now is:

<hr />
$stmt = $db->query("SELECT Month(postDate) as Month, Year(postDate) as Year,
postTitle, postSlug
FROM blog_posts_seo
GROUP BY Month(postDate), Year(postDate)
ORDER BY postDate DESC");
while($row = $stmt->fetch()){
$monthName = date("F", mktime(0, 0, 0, $row['Month'], 10));
$slug = 'a-'.$row['Month'].'-'.$row['Year'];
echo "<li><a href='$slug'>$monthName</a></li>";
echo '<li class="active"><a href="c-'.$row['postSlug'].'">'.$row['postTitle'].'</a></li>';

The result im getting is without year but as follows:

  • May
  • Post first out 4
  • April
  • Post first out of 3

I am am beginner in php and mysql. Therefore it would be of great help if you can help me in the complete coding if you know the solution. Thanks!

Re: archive list (blog style) using php and mysql 80 80

I hope I understood OK what you want. I suggest you do it as a single unordered list and use css styling (classes) to handle indent. I am posting an example with code for complete page so you can see what I mean. You can adapt CSS to suit your taste. Note that I removed the GROUP BY clause form the query (otherwise you can not display all the posts).

<!DOCTYPE html>
li.cl-year {list-style-type: none; font-weight: bold; margin-left: 0; width: 90%; background-color: silver;}
li.cl-month {list-style-type: none; font-weight: normal; margin-left: 1em;}
li.cl-posts {list-style-type: none; font-style: italic; margin-left: 2em;}
<hr />
$stmt = $db->query("SELECT Month(postDate) as Month, Year(postDate) as Year,
postTitle, postSlug FROM blog_posts_seo ORDER BY postDate DESC");

// you will store current month here to control when the month changes
$currentMonth = 0;

// you will store current year here to control when the year changes
$currentYear = 0;

while($row = $stmt->fetch()){

    // if the year changes you have to display another year
    if($row['Year'] != $currentYear) {

        // reinitialize current month
        $currentMonth = 0;

        // display the current year
        echo "<li class=\"cl-year\">{$row['Year']}</li>";

        // change the current year
        $currentYear = $row['Year'];

    // if the month changes you have to display another month
    if($row['Month'] != $currentMonth) {

        // display the current month
        $monthName = date("F", mktime(0, 0, 0, $row['Month'], 10));
        echo "<li class=\"cl-month\">$monthName</li>";

        // change the current month
        $currentMonth = $row['Month'];

    // display posts within the current month
    $slug = 'a-'.$row['Month'].'-'.$row['Year'];
    echo "<li class=\"cl-posts\"><a href='$slug'>$monthName</a></li>";
    echo '<li class="cl-posts active"><a href="c-'.$row['postSlug'].'">'.$row['postTitle'].'</a></li>';

I don not know if posts themselves are displayed as you imagined. I just copied your code from lines 13 and 14.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.