SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    canada
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how do i generate list of months with entries

    i'm making a photo cms for my photos and i would like to be able to generate a list of months with photos. each entry has a date in standard mysql format (2003-05-18 12:45:00). I want to be able to query the database for all distinct months with entries. so a list of Jan 2003, March 2003 etc would be generated. If i could search only the 2003-05 part of the date i could do some kind of distinct query, right?

    I'm quite new to php/mysql so if anyone has any pointers as to where to start, i would greatly appreciate it

    thank you
    Scott
    http://somethingtoforget.ca/
    "I intentionally made it out to be an illegible mess"

  2. #2
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there some reason to have the time along with the date, or for your purposes would the date suffice?
    John

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    canada
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i could split it into two colums one for date one for time but i'd sitll have the day on the end of the date (2003-05-17)

  4. #4
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What I am asking is are you storing more information than you will use? For you site, do you just need the month and year, or do you really need the time and day also?
    John

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    canada
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    day is definiely something i want to have. time isn't so important

  6. #6
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    canada
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    could I use DATE_FORMAT?

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    canada
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    figured out a solution i think (it works anyway). Rather than doing a distinct i just loop through and if the date (month year) has changed i echo that result else i do nothing
    PHP Code:
           $month_list mysql_query("SELECT Entry_Date,
        DATE_FORMAT(Entry_Date,'%b %y') AS readable_date FROM entry
        ORDER BY Entry_Date ASC"
    );
        echo 
    "<ul>";
        while (
    $row mysql_fetch_array($month_list) ) {
            
    $date1 $row['readable_date'];
            if (
    $date1 != $date2) {
              echo 
    '<li><a href="index.php?id='.$readable_date.'">'.$date1.'</a> </li>';
            }
          
    $date2 $date1;
        };
        echo 
    "</ul>"


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
  •