SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2002
    Location
    Launceston, Australia
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Nested Loop help for months/dates

    Hi again!

    I am trying to come up with a table that outputs like this:

    February

    20 February 2000
    27 February 2000

    March

    5 March 2000
    12 March 2000
    19 March 2000

    April

    4 April 2000 etc etc...

    I have already written a loop that gets the dates out of my database as above, but I'm having trouble setting it up to do each of the months. I'm pretty sure some kind of nested loop is needed here, and I've tried a couple of different things, but I'm not very good at php yet

    Could anyone help me on this one?

    Thanking you

    Jake

  2. #2
    Custom User Title v1.0 FireFly's Avatar
    Join Date
    Aug 2001
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How are the dates stored in the database?

    Anyway check this code out:
    PHP Code:
    $result=mysql_query("SELECT day,month,year FROM dates");
    $lastmonth='';
    while (
    $row=mysql_fetch_array($result)) {
      if (
    $lastmonth!=$row['month']) {
        echo 
    $row['month'];
      }
      echo 
    "$row[day] $row[month] $row[year]";
      
    $lastmonth=$row['month'];


  3. #3
    SitePoint Zealot
    Join Date
    Jan 2002
    Location
    Launceston, Australia
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a DATE field in my table, which is taking dates as 2000-02-02 etc.

    I can get those dates to display ok by using a query like "SELECT DATE_FORMAT(date, '%e %M %Y') as thedate from mytable". I'm then saying:
    <?php
    while ($row = mysql_fetch_array($result))
    {
    $thismonthsdate = $row ["thedate"];
    ?>
    html...
    <?php echo $thismonthsdate?>
    html..
    <?php
    } //end while loop
    ?>

    which is printing out as 2 February 2000 and so on..

    I can sort of see where you're coming from, but how would I get it to work with what I have above?

    Thanks for your assistance and patience!

    Jake

  4. #4
    Custom User Title v1.0 FireFly's Avatar
    Join Date
    Aug 2001
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this:
    PHP Code:
    <?php

    $result
    =mysql_query("SELECT DATE_FORMAT(date,'%e %M %Y') AS thedate,DATE_FORMAT(date,'%M') AS themonth FROM mytable");
    $lastMonth='';

    while (
    $row=mysql_fetch_array($result)) {
        if (
    $lastMonth!=$row['themonth']) {
            echo <
    b>$row['themonth']</b>;
        }
        
    ?>
    html...
        <?php
        
    echo $row['thedate'];
        
    ?>
    html..
        <?php
        $lastMonth
    =$row['themonth'];
    //end while loop

    ?>

  5. #5
    SitePoint Guru
    Join Date
    Aug 2001
    Location
    Amsterdam
    Posts
    788
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Shouldn't you order or group by month???

    Greets,

    Peanuts
    the neigbours (free) WIFI makes it just a little more fun

  6. #6
    Custom User Title v1.0 FireFly's Avatar
    Join Date
    Aug 2001
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If your group the result by month you will only get one date per-month.

    But yes we must order by month! Thanks.
    PHP Code:
    $result=mysql_query("SELECT DATE_FORMAT(date,'%e %M %Y') AS thedate,DATE_FORMAT(date,'%M') AS themonth FROM mytable ORDER BY DATE_FORMAT(date,'%c')"); 

  7. #7
    SitePoint Zealot
    Join Date
    Jan 2002
    Location
    Launceston, Australia
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much for your help - I now have it working well, after a bit of tweaking around with your code, and my html...

    Turns out I didn't need the order by month. I assume you had this here to cover multiple years, but I have in my sql condition WHERE DATE_FORMAT(date,'%Y')=$year (and $year is passed by URL from previous page), to just list things for one year on one page.

    Perhaps when I've finished it all, I might list the site for review!

    Cheers

    Jake

  8. #8
    SitePoint Zealot
    Join Date
    Jan 2002
    Location
    Launceston, Australia
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ummmm, I spoke too soon about not needing that order by statement! Turns out I do need it after all - I'd been putting all my data into the database in date order, but when I put some in out of order, things mucked up... aah well, live and learn!

    Cheers

    Jake


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
  •