SitePoint Sponsor

User Tag List

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

    diplsaying content categorized by month

    hi,

    Ive googled this but im not getting the answer im after....basically i want to make an archive page so that i can show all the title of my articles in order of which month they were posted in.

    Im using mysql NOW() function to insert the date in to the db so is there any easy way i can select from the db and order by month.

    Basically what i want is to have a definition list with the month and then a list of article titles from that month below..

    any ideas anyone??

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select year(articledate) as yy
         , month(articledate) as mm
         , other columns
      from ...
    order
        by articledate desc
    then check for control break on yy and mm as you loop over the query results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Oct 2005
    Location
    London
    Posts
    1,678
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi rudy, thanks for the quick reply and the query.......now i cant think how to loop through with php and seperate the articles by month...... i know you cant help as you dont do PHP!

  4. #4
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    PHP Code:
    while($rows mysql_fetch_assoc($query)) {
        if(
    $flag !=$rows['mm']) {
            echo 
    $rows['mm'] .'<br />';
        }
    // echo the months
        
    echo $rows['article_title'] .'<br />';

        
    $flag $rows['mm'];

    I have just chucked in some br's, format as you wish
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  5. #5
    SitePoint Wizard
    Join Date
    Oct 2005
    Location
    London
    Posts
    1,678
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    aha i see...thanks spikez


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
  •