SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2006
    Location
    Syracuse, NY
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help displaying a query...

    I'm not sure the best way to do this... but here it goes.

    I've made a news section where admin can add news stories to the site. So lets say a user adds 3 stories for the month of august. In that "news" table, there are now 3 rows, all listed with august as the date. Now they write 2 stories in October, skipping September. Thats 5 total stories and 2 months.

    Now how do i pull just those two months of the database? I want to make an archive listing that just lists the months that stories have been listed it. So it needs to display August and October once. I have the rest all set up, i just dont know how to pull those two months out without displaying them multiple times(because of the multiple stories they have).

    Thanks in advance

  2. #2
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT MONTH(date) AS month, YEAR(date) AS year
    FROM newstable
    GROUP BY year, month
    This will additionally separate August 2007 from August 2006 since I added the year as well.
    PHP questions? RTFM
    MySQL questions? RTFM

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2006
    Location
    Syracuse, NY
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $sql = "SELECT MONTH(date) AS month, YEAR(date) AS year ";
    $sql .= "FROM items ";
    $sql .= "GROUP BY n_year, n_month";


    Which one of the months do i have to specify the actual field name? (they're n_month, n_year)

  4. #4
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, didn't realize you weren't using a standard DATE or DATETIME column. In that case replace "MONTH(date) AS month" with n_month and do the same with the year.
    PHP questions? RTFM
    MySQL questions? RTFM


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
  •