Jump to content


Photo

Blog Archives


  • Please log in to reply
6 replies to this topic

#1 adamwhiles

adamwhiles
  • Members
  • PipPipPip
  • Advanced Member
  • 54 posts

Posted 25 June 2006 - 07:35 PM

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.




#2 dptr1988

dptr1988
  • Members
  • PipPipPip
  • Advanced Member
  • 372 posts

Posted 25 June 2006 - 08:08 PM

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

SELECT items_you_want_to_select FROM your_table WHERE MONTH(date) = month_you_want

Hope that helps.


Need more help with your project? One of the thousands of programmers, web designers or artists at <a href="http://www.rentacode...d_6764522">Rent A Coder</a> would be happy to help.

Disclaimer: Free advice is usually worth what you paid for it. ( or at least when it's coming from me! )

#3 adamwhiles

adamwhiles
  • Members
  • PipPipPip
  • Advanced Member
  • 54 posts

Posted 25 June 2006 - 08:20 PM

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

#4 adamwhiles

adamwhiles
  • Members
  • PipPipPip
  • Advanced Member
  • 54 posts

Posted 25 June 2006 - 09:02 PM

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

#5 dptr1988

dptr1988
  • Members
  • PipPipPip
  • Advanced Member
  • 372 posts

Posted 25 June 2006 - 09:35 PM

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.
Need more help with your project? One of the thousands of programmers, web designers or artists at <a href="http://www.rentacode...d_6764522">Rent A Coder</a> would be happy to help.

Disclaimer: Free advice is usually worth what you paid for it. ( or at least when it's coming from me! )

#6 adamwhiles

adamwhiles
  • Members
  • PipPipPip
  • Advanced Member
  • 54 posts

Posted 25 June 2006 - 09:38 PM

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

$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++;
}

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.

#7 adamwhiles

adamwhiles
  • Members
  • PipPipPip
  • Advanced Member
  • 54 posts

Posted 26 June 2006 - 04:57 PM

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

$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++;

}





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users