Jump to content


Photo

Correctly ordering by month or date in MySQL?


  • Please log in to reply
6 replies to this topic

#1 t.bo

t.bo
  • Members
  • PipPipPip
  • Advanced Member
  • 57 posts

Posted 18 July 2006 - 03:56 PM

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

Hope someone can help.
Thanks in advance

#2 willfitch

willfitch
  • Members
  • PipPipPip
  • Advanced Member
  • 109 posts
  • LocationNew Hope, PA

Posted 18 July 2006 - 04:00 PM

SELECT * FROM agenda ORDER BY monthfield, date; 

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

Zend Certified Engineer (ZEND001989)
www.willfitch.com

#3 t.bo

t.bo
  • Members
  • PipPipPip
  • Advanced Member
  • 57 posts

Posted 18 July 2006 - 04:15 PM

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.


#4 willfitch

willfitch
  • Members
  • PipPipPip
  • Advanced Member
  • 109 posts
  • LocationNew Hope, PA

Posted 18 July 2006 - 04:44 PM

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)
Zend Certified Engineer (ZEND001989)
www.willfitch.com

#5 t.bo

t.bo
  • Members
  • PipPipPip
  • Advanced Member
  • 57 posts

Posted 18 July 2006 - 04:48 PM

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

Example I put in 07 , and it outputs July

#6 willfitch

willfitch
  • Members
  • PipPipPip
  • Advanced Member
  • 109 posts
  • LocationNew Hope, PA

Posted 18 July 2006 - 04:51 PM

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

Zend Certified Engineer (ZEND001989)
www.willfitch.com

#7 t.bo

t.bo
  • Members
  • PipPipPip
  • Advanced Member
  • 57 posts

Posted 18 July 2006 - 06:56 PM

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

grtz




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users