Blog Archives

[adamwhiles]

I am creating a custom blog script from scratch and I want to have an acrchives list on the side navigation area where it lists archives like so:

Month (# of entries in that month)

Like:

January (8)

January will link to a page and list all the entries in January.

I am having trouble figuring out how to create an accurate list of the months and number of posts for that month.

Each entry has a DATE field in the database in the format of YYYY-MM-DD

Any help is appreciated.

[dptr1988]

You could try this query replacing the italic text with your own values:

SELECT [i]items_you_want_to_select[/i] FROM [i]your_table[/i] WHERE MONTH(date) = [i]month_you_want[/i]

Hope that helps.

[adamwhiles]

ok but I only need to list the months I actually have posts for, like say I had 3 in January, 0 in feb, and 0 in march, and 5 in april it would list like this

January (3)
April (5)

Leaving out feb and march

[adamwhiles]

I know what needs to be done now I think. I need to get the script to run through each month jan through dec and count the posts, then if a month has 0 posts then I won't print that one, if its > 1 then it will print it. I just don't know how to code something like that. I would also like to break up years too. Like, at the beginning of 2007 the side bar menu would look like this.

January (2) <--- this being 2007
2006 Archive

[dptr1988]

This problem is beyond my knowledge of SQL. Also it seem more like a SQL problem. Maybe you should try on [a href=\"http://www.phpfreaks.com/forums/index.php?showforum=3\" target=\"_blank\"]MySQL Help[/a] There should be a query that you could use that would select the number of items and group them by the month.

[adamwhiles]

here is what i came up with but its not working correctly for some reason, no errors just not displaying data.

[code]
$months = array("January", "Febuary", "March", "April", "May", "June", "July",
                 "August", "September", "October", "November", "December");
foreach ($months AS $month) {
     $i=1;
     $sql1 = "SELECT COUNT(*) AS num_posts_archive FROM posts WHERE MONTH(posttime) = '$i'";
     $result1 = mysql_query($sql1);
     $row1 = mysql_fetch_assoc($result1);
           if($row1['num_posts_archive'] >= 1) {
?>
           <? echo $month ?> (<? echo $row1['num_posts_archive']; ?>)<BR>
<?
           }
     $i++;
}
[/code]

I have tweeked and tweeked on this code and just can't figure out how to get it to work. Any help is much appreciated.

[adamwhiles]

Finally got something to work, I thought I would post what I came up with in case anyone had a similiar problem.

[code]
$months = array("January"=>01, "Febuary"=>02, "March"=>03,
               "April"=>04, "May"=>05, "June"=>06, "July"=>07,
                  "August"=>08, "September"=>09, "October"=>10,
               "November"=>11, "December"=>12);
foreach ($months as $month_archive=>$key) {
    $i=1;
    $sql1 = "SELECT COUNT(*) AS num_posts_archive FROM posts WHERE MONTH(posttime) = '$key'";
    $result1 = mysql_query($sql1);
    $row1 = mysql_fetch_assoc($result1);
          if($row1['num_posts_archive'] >= 1) {

                ?>
                <? echo $month_archive ?> (<? echo $row1['num_posts_archive']; ?>)<BR>
                <?
          }
    $i++;

}[/code]