SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Wizard
    Join Date
    Oct 2005
    Location
    London
    Posts
    1,678
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    grouping entries by month?

    arrghh ive googled this and looked through a few books but cant find an answer. I want to create a archive section ofr my site and i want to group articles by the month they were posted.....how do i do this?

    Thanks for any help

  2. #2
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Not entirely sure this would work but:

    Code:
    select 
    	month(field_for_date) as month_order,
    	other_field_1,
    	other_field_2
    from
    	table
    order by
    	month_order
    It might work.........
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  3. #3
    SitePoint Addict
    Join Date
    Sep 2005
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It really depends on how you are storing your data. You can store articles in a database with a date field. The date field will be 20070505 which you can then sort by the date. However, that is a lot of overhead for either mysql or php to have to look at every date field and run a function against it to find the month. If you know the month prior to the query then you can include a "where" clause where articledate >= '200705 and articledate < 200706. Another way is to store the year and month in separate fields and then perform your where clause on the year and month. The benefit of this is that you can run a "select distinct" query to find out which years and months actually have articles.

    Finally, you can create a table of years and months and assign a unique id to each row (year/month). Then, assign the unique year/month id to each article.
    PHP Shopping Cart Software Easy Ecommerce Shopping Cart Script.
    PHP Super Cart is 100% template driven.

  4. #4
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I assume your storing the articles in a database with a field that specifies the month of insertion.

    So just call your database query ordered by the month field

    SELECT article
    FROM database
    WHERE something = somethingelse
    ORDER BY month, articleID;

    edit: Wow, crowded in here tonight, whos round is it ??
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  5. #5
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    yours
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  6. #6
    SitePoint Evangelist catweasel's Avatar
    Join Date
    Apr 2007
    Location
    Goldfields, VIC, Australia
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    don't store date information in text/varchar fields as month,year etc.. store them in date or datetime fields and use spike's method of ordering by the month.

    SELECT MONTH(your_date_field) as month, * FROM your_table ORDER BY month

  7. #7
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by spikeZ View Post
    yours
    Typical
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  8. #8
    SitePoint Wizard
    Join Date
    Oct 2005
    Location
    London
    Posts
    1,678
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats great thanks guys....now i have them ordered by date but what i want to do is have the month then all the article titles below from that month and then the next month and so on....also how do i order it by year also.....ive tried to do this but just cant think of a way to get it going

  9. #9
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    How does your query look as it stands now?
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  10. #10
    SitePoint Wizard
    Join Date
    Oct 2005
    Location
    London
    Posts
    1,678
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Spike, thanks for the reply

    Well this is what the whole thig looks like:

    PHP Code:
    $sql= "SELECT title, blog_id, upload, month(date_submitted) as month_order FROM entries ORDER BY month_order";
       $query = mysql_query($sql);
       while($row = mysql_fetch_array($query)) {
           $title = $row['title'];
           $blog_id = $row['blog_id'];
           $month = $row['month_order'];
           $upload = $row['upload'];
       ?>

       <?php if($month == '1') { echo 'january';
       }
       
    ?>

       <li><?php if(!isset($upload)) {
          echo 
    '<a style="text-decoration: none;" href="individualarticle.php?f=0&blog_id=' .$row['blog_id'] .'">';
                         } 
                         else {
        echo 
    '<a style="text-decoration: none;" href="individualarticle.php?f=1&blog_id=' .$blog_id .'">';
                         }
                         
    ?>
              <?php echo $title?></a></li>
         
          
      
       </ul>
       <?php 
       

       
    ?>
    (Sorry about the state of the code.....its wip)

    Ive had it working after a fashion....ive had all the articles ordered by month and had the month no next to each article but thats about as far as i got....icant think of a good way to marry month nos with names ( an array inline would do it i suppose) and then i cant think how to list ther month then all the articles in that month.....im sure im making this more complicated than this is...

    any ideas?

  11. #11
    SitePoint Evangelist catweasel's Avatar
    Join Date
    Apr 2007
    Location
    Goldfields, VIC, Australia
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can in fact get the month name by using -

    select *,date_format(date_submitted,"%M") as month from entries order by month

    the trouble with this is that if you have an entry in December 2006 and one in January 2007 you will get December coming before January.. which is how it's supposed to work but it sounds like you just want a table listing January ... December and showing all entries for those months.

    Have you considered whats going to happen when you wrap around to a new year? Are you going to empty the database and start again? Maybe you should be testing for Year as well as month.

    Since selecting MONTH(date_submitted) gives you the month as a number you could easily map those numbers to an array-

    $months = array(1=>'january',2=>'february',....,12=>'december');

    echo $months[$row['month_order']];


    But like I say.. entries made in May last year will appear along side those made in May this year without any difference between them.

  12. #12
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Why not just order by date_submitted ??
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  13. #13
    SitePoint Wizard
    Join Date
    Oct 2005
    Location
    London
    Posts
    1,678
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can in fact get the month name by using -

    select *,date_format(date_submitted,"&#37;M") as month from entries order by month

    the trouble with this is that if you have an entry in December 2006 and one in January 2007 you will get December coming before January.. which is how it's supposed to work but it sounds like you just want a table listing January ... December and showing all entries for those months.

    Have you considered whats going to happen when you wrap around to a new year? Are you going to empty the database and start again? Maybe you should be testing for Year as well as month.

    Since selecting MONTH(date_submitted) gives you the month as a number you could easily map those numbers to an array-

    $months = array(1=>'january',2=>'february',....,12=>'december');

    echo $months[$row['month_order']];


    But like I say.. entries made in May last year will appear along side those made in May this year without any difference between them.
    catweasel is offline Report Post Reply With Quote
    right this sound like what i need to do but i do want to test for year also...but how?
    would the query be like this:

    PHP Code:
    select *,date_format(date_submitted,"%M, %Y") as month from entries order by month 
    IM really not sure.

    Why not just order by date_submitted ??
    Id love if it were that simple but as ive said i want the month name followed by all articles posted in that month and then the next month and so on....i also want it ordered by year

  14. #14
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    NL, Rotterdam
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post

    You're probably looking for something like this:

    Code:
    SELECT
      *,
      YEAR(date_field) AS date_year,
      MONTH(date_field) AS date_month
    FROM
      data_table
    ORDER BY
      date_year ASC,
      date_month ASC

  15. #15
    SitePoint Wizard
    Join Date
    Oct 2005
    Location
    London
    Posts
    1,678
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right yeah that works...thanks for that but all it gives me is a big list of post titles....i still cant figure a way to get january as a heading then all the posts for january uderneath and then february as a heading with all febs posts and so on

    any ideas anyone

  16. #16
    SitePoint Evangelist catweasel's Avatar
    Join Date
    Apr 2007
    Location
    Goldfields, VIC, Australia
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by elduderino View Post
    Right yeah that works...thanks for that but all it gives me is a big list of post titles....i still cant figure a way to get january as a heading then all the posts for january uderneath and then february as a heading with all febs posts and so on

    any ideas anyone
    I haven't tested this but it's based on the code you posted earlier -
    PHP Code:
    <?php
    $query 
    'SELECT
             *,
             YEAR(date_field) AS year,
             date_format(date_submitted,"%M") AS month
             FROM data_table
             ORDER BY
             date_field'
    ;

    if (
    $result mysql_query($query)) {
        
    $monthHeader '';
        while (
    $row mysql_fetch_assoc($result)) {
            
    extract($row);
            if (
    $month != $monthHeader) echo "$month - $year<UL>\n";
            empty(
    $upload) ? $f $f=1;
            
    printf("<li><a style='text-decoration: none;' href='individualarticle.php?f=%s&blog_id=%s'>%s</a></li>\n",$f,$blog_id,$title);
            if (
    $month != $monthHeader) {
                echo 
    "</UL>\n";
                
    $monthHeader $month;
            }
        }
    }
    ?>

  17. #17
    SitePoint Wizard
    Join Date
    Oct 2005
    Location
    London
    Posts
    1,678
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats great catweasel....i learnt two new functions from that also.........extract and printf.......very useful although i understand in some circumstances it isnt very secure.....perfect for mysql_fetches though right?!

    anyways your code works great ( but it does close the </ul> after the first article title-the rest are just enclosed in <ul></ul>) but can i just clarify i understand it.

    the way i see it $monthheader is assigned to ' '. Then if $month is not empty then echo the month and year.........then echo the list out....then if $month is equal to nothing ( you have a != but is that a mistake?) then close the list....the only bit i dont get is the last $monthheader = $month ? Do i have this right

  18. #18
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    almost!

    $monthHeader = ''; sets an empty flag to avoid undefined index errors

    while the loop is running through the results, $monthHeader is assigned the $month variable value.
    ie: $monthHeader = $month;

    So when the month changes in the database $monthHeader is no longer equal to the $month variable value so it prints the next $month and reassigns the value of $monthHeader and off it goes again!
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •