Search code examples
phpmysqlarchive

MySQL PHP: Blog Post Archive Drop Down Menu

I am new to MySQL as well as PHP and just had a few questions.

Currently I am trying to create an archive menu for my posts that are looked up in my MySQL DB named "posts".

It should look like,

2010 (2)
   September (2)
     Bellavisa
     Mists of Netting

   July (1)
     Turkey is cool!

2009 (1)
   May (1)
     Cock of the Rock

but I am currently getting,

2010 (2)
   September (2)
     Bellavisa


   July (1)
     Turkey is cool!

2009 (1)
   May (1)
     Cock of the Rock

Hence, I am missing the second post title under September

Any help would be appreciated! My code is below!

$sql = "SELECT Month(time) as Month, Year(time) as Year,
title, COUNT(*) AS total FROM posts GROUP BY Year, Month ORDER BY time DESC";
$stmt = $conn->query($sql);

$currentMonth = 0;

$currentYear = 0;

if ($stmt->num_rows > 0) {
while($row = $stmt->fetch_assoc()){
  $title = $row["title"];
     // 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']} ({$row['total']})</li>";
echo "          <ul>";
echo "          <li onClick = 'show(\"{$row['Year']}\")' > <img src='images/triangle_closed.gif' id=img_{$row['Year']}>{$row['Year']} ({$row['total']})</li>\n"; 
echo "          <li>\n"; 
echo "          <ul id = {$row['Year']} style='display:none;'>\n"; 
#echo "</ul>";



        // 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 "<ul>";
echo "              <li onClick = 'show(\"{$row['Year']}$monthName\")' > <img src='images/triangle_closed.gif' id=img_{$row['Year']}$monthName>$monthName ({$row['total']})</li>\n"; 
echo "              <li>\n"; 
echo "                <ul id = {$row['Year']}$monthName style='display:none;'>\n"; 
echo "                  <li class='cl-posts active'><a href='\base\item.php?id=$title'>".$title."</a></li>\n"; 
echo "                </ul>\n"; 
echo "              </li>\n"; 
        #echo "<li class=\"cl-month\">$monthName ({$row['total']})</li>";

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

    // display posts within the current month
    #echo "<li class='cl-posts active'><a href='\base\item.php?id=$title'>".$title."</a></li>";
}
}

 $conn->close();
 ?>

Thank You!

Solution

  • Looking at your query, you're grouping by Year, so it's normal not to show the second title and also you're trying to count the number of years. I would suggest mixing a subQuery so you can have both results, so change

    SELECT Month(time) as Month, Year(time) as Year, title, COUNT(*) AS total FROM posts GROUP BY Year, Month ORDER BY time DESC

    to

    SELECT Month, Year, p.title, t.total
FROM posts p 
INNER JOIN (SELECT DISTINCT Year(time) Year, Month(time) Month, SUM(1) FROM posts GROUP BY Year, Month) t ON t.Year = Year(p.time) AND t.Month = Month(p.time)
ORDER BY time DESC

    Also, for the identation problem, you are opening new before closing the last, each time a Year or Month changes.

    Validate for $currentYear and $currentMonth, if they are non-zero, you must close the before opening a new one.