Sort by Month

[oldschool]

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 :)

[toplay]

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] * [color=green]FROM[/color] [color=orange]table_name[/color] [color=green]ORDER BY[/color] YEAR(date_column) ASC, MONTH(date_column) [color=green]ASC[/color] [!--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]

[oldschool]

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?

[toplay]

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`) [color=green]AS[/color] gig_month_name, MONTH(`gig_date`) [color=green]AS[/color] gig_month_nbr, YEAR(`gig_date`) [color=green]as[/color] gig_year
[color=green]FROM[/color] [color=orange]`gigs`[/color] [color=green]WHERE[/color] (`gig_date` [color=orange]>[/color][color=orange]=[/color] CURDATE( ))
[color=green]ORDER BY[/color] gig_year ASC, gig_month_nbr [color=green]ASC[/color] [!--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:

[code]
// 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/>';

}
[/code]

[oldschool]

to play, thank you very much

sorry for taking so long to reply

full credit added to script :)