Jump to content

Archived

This topic is now archived and is closed to further replies.

adamwhiles

Blog Archives

Recommended Posts

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.


Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.