Jump to content


Photo

Sort by Month


  • Please log in to reply
4 replies to this topic

#1 oldschool

oldschool
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 30 April 2006 - 05:04 PM

What's the most appropiate way of sorting the records in to Months.

For example, we're in April, so results would appears as follows

April 2006
[records]
May 2006
[records]
...
...
Decemeber 2006
[records]
January 2007
[records]
....
March 2007
[records]


Thanks in advance for any help :)

#2 toplay

toplay
  • Staff Alumni
  • Advanced Member
  • 973 posts

Posted 30 April 2006 - 05:18 PM

You didn't provide the context around this sorting thing you want. Is data in MySQL, in a file, or just in an array?

If it's MySQL, basically you can use the "ORDER BY" clause to sort in MySQL.

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * FROM table_name ORDER BY YEAR(date_column) ASC, MONTH(date_column) ASC [!--sql2--][/div][!--sql3--]


Select syntax showing order by clause:
[a href=\"http://dev.mysql.com/doc/refman/4.1/en/select.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/4.1/en/select.html[/a]

Date and time functions in MySQL:
[a href=\"http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/4.1/en/dat...-functions.html[/a]

#3 oldschool

oldschool
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 30 April 2006 - 05:29 PM

Thanks ^

Sorry, yes, it's MySQL

How would I combine the above with this

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]SELECT DISTINCT MONTHNAME( gig_date ) AS MONTH FROM gigs WHERE (gig_date >= CURDATE( )) ORDER BY MONTH DESC[/quote]

which will return the months listed in the database.

So for example, the above currently lists April & May because the records contain dates in these months
How can I attach each record(s) to each month?

#4 toplay

toplay
  • Staff Alumni
  • Advanced Member
  • 973 posts

Posted 01 May 2006 - 01:38 AM

It looks like the table holds events and when they occur. So, you probably don't want to use distinct. Example:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] *, MONTHNAME(`gig_date`) AS gig_month_name, MONTH(`gig_date`) AS gig_month_nbr, YEAR(`gig_date`) as gig_year
FROM `gigs` WHERE (`gig_date` >= CURDATE( ))
ORDER BY gig_year ASC, gig_month_nbr ASC [!--sql2--][/div][!--sql3--]

That SQL will return all rows sorted by year then month in ascending order. Then it's just a matter of only displaying the month and year heading once or when you want to. Like when it changes. Example:

// open, select DB, and query table using order by and put in $result

// Initialize to something that the data won't contain
$saved_year = 0;   
$saved_month = 0;

while ($row = mysql_fetch_assoc($result)) {

    // Determine if data has changed and display one time (heading)
    if (($row['gig_year'] != $saved_year) ||
        ($row['gig_month_nbr'] != $saved_month)) {

        echo $row['gig_month_name'], ' ', $row['gig_year'], '<br/>';

        $saved_year    = $row['gig_year'];
        $saved_month = $row['gig_month_nbr'];
    }

    // Display other column data
    echo $row['event'], '<br/>';

}


#5 oldschool

oldschool
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 01 May 2006 - 05:44 PM

to play, thank you very much

sorry for taking so long to reply

full credit added to script :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users