Jump to content

Correctly ordering by month or date in MySQL?


t.bo

Recommended Posts

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
[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]
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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.