Member Avatar for poochiteap

I want to create an archive list like this:

2014
  -March
  -Feb
  -Jan
    *Post 1
    *Post 2
2013
  -November
     *Post 1

My config file is:

<?php
ob_start();
session_start();
//database credentials
define('DBHOST','127.0.0.1');
define('DBUSER','name');
define('DBPASS','pass');
define('DBNAME','sample');
$db = new PDO("mysql:host=".DBHOST.";dbname=".DBNAME, DBUSER, DBPASS);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//set timezone
date_default_timezone_set('Europe/London');
?>`

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:

<h1>Archives</h1>
<hr />
<ul>
<?php
$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>';
}
?>
</ul>

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!

  • 2 Contributors
  • 1 Reply
  • 1K Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by broj1
Member Avatar for broj1

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>
<html>
<head>
<style>
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;}
</style>
</head>
<body>
<h1>Archives</h1>
<hr />
<ul>
<?php
$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>';
}
?>
</ul>
</body>
</html>

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

Edited by broj1
Be a part of the DaniWeb community

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