Jump to content

Archived

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

t.bo

Correctly ordering by month or date in MySQL?

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

Share this post


Link to post
Share on other sites
[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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
And is it possible to input the MM in numbers and output in a word?

Example I put in 07 , and it outputs July

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
thanks a lot man , im gonna try it out later!!

grtz

Share this post


Link to post
Share on other sites

×

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.