SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Sep 2004
    Location
    Walsall
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Group results by month

    Firstly, have been using this site almost religously over the last few weeks and have been learning a ell of a lot from everyone so cheers. However, I think I'm finally stumped!

    Basically I have a list of gigs, with dates across a year (defined by ?year=2004). At the moment I can achieve the following output:

    January 2004
    Fri 30th Jan - The Jailhouse

    January 2004
    Fri 23rd Jan - Actress and Bishop, Hockley

    March 2004
    Thu 18th Mar - The Flapper and Firkin

    March 2004
    Fri 19th Mar - The Red Rose

    May 2004
    Sun 9th May - St. Johns College

    However what I want to achieve is the following :-

    January 2004
    Fri 30th Jan - The Jailhouse
    Fri 23rd Jan - Actress and Bishop, Hockley

    March 2004
    Thu 18th Mar - The Flapper and Firkin
    Fri 19th Mar - The Red Rose

    May 2004
    Sun 9th May - St. Johns College

    ---

    My SQL stuff is as follows:-

    PHP Code:
    <?php
    // Get year string
    $gigYear_rstGigs date("Y");

    if (isset(
    $HTTP_GET_VARS['year'])) {
        
    $gigYear_rstGigs $HTTP_GET_VARS['year'];
    }

    // Query results
    $query_rstGigs "
        SELECT *
        FROM thegetaways_gigs
        WHERE DATE_FORMAT(gig_date, '%Y') = "
    .$gigYear_rstGigs."
        ORDER BY gig_date "
    ;
        
    $rstGigs mysql_query($query_rstGigs$dbc) or die(mysql_error());
    $row_rstGigs mysql_fetch_assoc($rstGigs);
    $totalRows_rstGigs mysql_num_rows($rstGigs);

    ?>
    And my repeat is:-

    PHP Code:
    <?php do { ; ?>
    <h2><?php echo date('F Y'strtotime($row_rstGigs['gig_date'])); ?></h2>
    <?php echo $row_rstGigs2['gig_date']; ?>
    <dl>
        <dt><?php echo date('D jS M'strtotime($row_rstGigs['gig_date'])); ?> - <?php if ($row_rstGigs['gig_active'] == 0) {echo '<s>' $row_rstGigs['gig_venue'] . '</s>';} else {echo $row_rstGigs['gig_venue'];} ?></dt>
    <?php if ($row_rstItems['gig_additional'] != NULL) : ?>
        <dd><?php echo date('D jS M'strtotime($row_rstGigs['gig_additional'])); ?></dd>
    <?php endif ; ?>
        <dd><?php if ($row_rstGigs['gig_active'] == 0) {echo 'THIS GIG HAS BEEN CANCELLED';} ?></dd>
    </dl>
    <?php } while ($row_rstGigs mysql_fetch_assoc($rstGigs));?>
    Now this is probably very simple - but go on embarass me please - I need to know! Cheers

  2. #2
    SitePoint Wizard
    Join Date
    Aug 2004
    Location
    California
    Posts
    1,672
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $lastdate '';
    while (
    $row_rstGigs mysql_fetch_assoc($rstGigs)) {
        if (
    $row_rstGigs['gig_date'] != $lastdate) {
    // show the heading
            
    $lastdate $row_rstGigs['gig_date'];
        }
    // show the content
    }
    ?> 
    PS - Fix your zany code. Too many <?php ?>.

  3. #3
    SitePoint Member
    Join Date
    Sep 2004
    Location
    Walsall
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php
    $lastdate 
    '';
    while (
    $row_rstGigs mysql_fetch_assoc($rstGigs)) { 
        if (
    $row_rstGigs['gig_date'] != $lastdate) {
            echo 
    '<h2>' date('F Y'strtotime($row_rstGigs['gig_date'])) . '</h2>';
            
    $lastdate $row_rstGigs['gig_date']; 
           }
        echo 
    $row_rstGigs2['gig_date'] . '<dl>';
        echo 
    '    <dt>' date('D jS M'strtotime($row_rstGigs['gig_date'])) . '-';
        if (
    $row_rstGigs['gig_active'] == 0) {
            echo 
    '<s>' $row_rstGigs['gig_venue'] . '</s>';
        }
        else {echo 
    $row_rstGigs['gig_venue'];
        }
        echo 
    '</dt>'
        if (
    $row_rstItems['gig_additional'] != NULL) {
            echo 
    '    <dd>' date('D jS M'strtotime($row_rstGigs['gig_additional'])) . '<dd>';
        }
        echo 
    '<dd>';
        if (
    $row_rstGigs['gig_active'] == 0) {
            echo 
    'THIS GIG HAS BEEN CANCELLED</dd>';
        }
        echo 
    '</dl>';
    }
    ?>
    Looks good, but I get a parse error on line 33 which I've figured out to be this line:-

    echo '<h2>' . date('F Y', strtotime($row_rstGigs['gig_date'])) . '</h2>';

    Any ideas?

  4. #4
    SitePoint Member
    Join Date
    Sep 2004
    Location
    Walsall
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My mate gave me this solution

    I don't understand why you're using strtotime(""), this looks like a hack you've copied off someone because they didn't know how to format the dates properly. What you need to do is change your SQL to include a format for the date. Only problem is I can't quite remeber what the actual syntax is, I've done this before, so know it works.... It's somthing like....

    PHP Code:
    SELECT field1field2DATE_FORMAT('gig_date''%d%M%Y') AS formated_date  WHERE DATE_FORMAT(gig_date'%Y') = ".$gigYear_rstGigs." ORDER BY gig_date "; 
    you can now use $rst_whatever['formated_date']; to output the correctly formatted date.

    But as for the grouping by months problem, hmmmmm.... It should be working using the ORDER BY gig_date, try using ORDER BY formated_date.

    However this doesn't work either... HELP!!!

    Anyway, let me know, it may be that none of this works at all. I'm still a little rusty.

  5. #5
    SitePoint Zealot ssttoo's Avatar
    Join Date
    Jan 2004
    Location
    LA, California
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My suggestion is that you use some basic pre-processing of the results. You can use array keys to store the repetitive month names, then add gigs as sub-arrays. So the meta-code would be:

    Code:
    shows_array = new empty array;
    for each database_row {
        $array[month][] = show_date . " - " . show_venue;
    }
    At the end of the loop your $shows_array will look like:
    Code:
    $shows_array = array (
        'January 2004' => array (
                '0' => 'Fri 30th Jan - The Jailhouse',
                '1' => 'Fri 23rd Jan - Actress and Bishop, Hockley'
            ),
        'March 2004' => array (
                '0' => 'Thu 18th Mar - The Flapper and Firkin',
                '1' => 'Fri 19th Mar - The Red Rose'
            ),
        'May 2004' => array (
                '0' => 'Sun 9th May - St. Johns College'
            ),
    );
    Now for the code:
    1. You SQL query should contain the gig date and the gig month that we need for the grouping. So this will do:
    PHP Code:
    $query_rstGigs "
        SELECT *,
              DATE_FORMAT('gig_date', '%d%M%Y') AS formatted_date,
              DATE_FORMAT('gig_date', '%M%Y') AS month
        FROM 
             thegetaways_gigs
        WHERE 
             DATE_FORMAT(gig_date, '%Y') = "
    .$gigYear_rstGigs."
        ORDER BY 
             gig_date "

    2. The pre-processing routine
    PHP Code:
    $shows_array = array();
    while (
    $row mysql_fetch_assoc($rstGigs)) {
        
    $shows_array[$row['month']][] = $row['formatted_date'] . " - " $row['gig_venue'];

    3. The display (use your formatting, this is just the base output)
    PHP Code:
    foreach ($shows_array AS $month=>$gigs) {
        echo 
    "<b>" $month "</b><br />";
        echo 
    implode("<br />",$gigs); 
        echo 
    "<br />";

    I hope that gives you a hint and I hope that there are not too many syntax errors


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
  •