Correctly ordering by month or date in MySQL?

[t.bo]

hello everybody,

Im currently making an agenda with events, date of the event, and an URL of the event.
It should output the current month as a title with its events first and then the next months up to 5 months ahead.
Im having trouble ordering by month and current month and the date.

Code [code]<table width="100%"  border="1" cellspacing="0" cellpadding="0">

<?php
include('dbconnect.php');
$sql = mysql_query("select * from agenda order by monthfield") or die(mysql_error());

$prevCat='';
while($row = mysql_fetch_array($sql))
{
$event = $row["eventfield"];
$id = $row["idfield"];
$url = $row["urlfield"];
$date = $row["date"];
$month = $row["monthfield"];

// has category changed ?
// if so, print it
if ($month != $prevCat)  {
echo "<tr><td><h2>$month</h2></td></tr>";
}
echo  "<tr><td><a href='$url'>$event</a></td>";
echo  "<td>$date<td></tr>";
$prevCat = $month;
}
?>
</table>[/code]

Hope someone can help.
Thanks in advance

[willfitch]

[CODE]
SELECT * FROM agenda ORDER BY monthfield, date;
[/CODE]

Should work just fine.  It would order monthfield ASC then date ASC.  If you need them the opposite, then use:
[CODE]
SELECT * FROM agenda ORDER BY monthfield, date DESC;
[/CODE]

[t.bo]

What if the months are in another language?
And how should I input the date? (ex. 01/05/2006 or 5 januari 2006)

And when I use order by monthfield it just orders the months alfabetically.

[willfitch]

Depending on the characterset and language set of your MySQL installation, you may not have to do anything.  However, you should input the dates as:

YYYY-MM-DD

(i.e. 2006-07-17)

[t.bo]

And is it possible to input the MM in numbers and output in a word?

Example I put in 07 , and it outputs July

[willfitch]

Sure.  You would do this:

SELECT
DATE_FORMAT(date_field, '%W %M %Y')
FROM table_name

This would print something like Sunday January 2005.  Here is a complete list of values:

Specifier Description
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM 
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the first day of the week
%u Week (00..53), where Monday is the first day of the week
%V Week (01..53), where Sunday is the first day of the week; used with %X 
%v Week (01..53), where Monday is the first day of the week; used with %x 
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V 
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v 
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal ‘%’ character
%x x, for any ‘x’ not listed above

[t.bo]

thanks a lot man , im gonna try it out later!!

grtz