SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Almeaty Member
    Join Date
    Jul 2000
    Location
    Nowhere
    Posts
    278
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello everyone,

    on my site there's a tip of the day for which I want to create an archive ordered by month. So the first page in the archive is displaying all the available months with their links to the tip's overview. So far, that's not the problem... here it comes:

    Every single tip is stored in a mySQL table with its date. With PHP I want to query the DB for all tips of that selected month. However the date is in the format "yyyy-mm-dd" and I'm sure that mySQL (or PHP?) understands what there's written as its saved as a date cell.

    Can you help?

  2. #2
    psycho
    Join Date
    May 2000
    Location
    London
    Posts
    283
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In your mySQL select statement, simply do

    SELECT * from table ORDER BY whatever-your-column-with-the-date DESC

    -j

  3. #3
    Almeaty Member
    Join Date
    Jul 2000
    Location
    Nowhere
    Posts
    278
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, I knew that

    Actually I meant displaying only one entire month on one page and for the next month one has to click for another page... If possible, I want pass the month's digit, so the script knows which month to display.

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    /*
    Use this to generate your query and just pass the month as a digit ie January = 1; So pass 1 for January.
    */
    //If month variable isn't set make it current month
    if(!isset($month)) $month = date("m");
    //Get the first and la;st days of the month
    $firstday = date("Y")."-".$month."-1";
    $lastday = date("Y")."-".$month."-".DATE("d", mktime(0,0,0,$month + 1, 0, date("Y")));
    //Create query
    $query = "select * from tablename WHERE datefield >= $firstday AND <= $lastday";
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    Almeaty Member
    Join Date
    Jul 2000
    Location
    Nowhere
    Posts
    278
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow thanks!! It works perfectly... there was only one thing to change: in the query I had to put the $firstday and $lastday into ' '

    thanks again!

  6. #6
    SitePoint Enthusiast
    Join Date
    Oct 2000
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why dont you use mysql's MONTH function to solve this
    SELECT whateveryouwanthere WHERE MONTH(datefield)=$month;
    but be carefull new year is so close
    Great Dane
    Gokhan ARLI

  7. #7
    Almeaty Member
    Join Date
    Jul 2000
    Location
    Nowhere
    Posts
    278
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey, that's much easier! In this forum, I really can learn alot... much faster than reading a book

  8. #8
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, I love this stuff, I learn something new everyday. Thanks
    Please don't PM me with questions.
    Use the forums, that is what they are here for.


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
  •