Format and sort the dates from MySQL Database

PHP
1

Hi,

I have these mysql dates in a table of my database,

2010-07-16 20:09:06
2010-08-16 20:19:43
2010-10-18 16:57:19
2009-09-18 16:57:42
2009-10-18 16:57:55
2009-12-24 14:59:21

How can I sort them into the result below so that I can have the end user browses monthly results?

<h2>2010</h2>
<ul>
	<li><a href="#">October</a></li>
	<li><a href="#">November</a></li>
	<li><a href="#">December</a></li>
</ul>

<h2>2009</h2>
<ul>
	<li><a href="#">September</a></li>
	<li><a href="#">October</a></li>
	<li><a href="#">November</a></li>
	<li><a href="#">December</a></li>
</ul>

can’t think of anything!

I usually use gmdate() to format the dates from mysql database, for instance,

<?php
	$sql = "
	SELECT *
	FROM root_pages

	WHERE root_pages.pg_hide != '1'

	ORDER BY pg_created DESC";

#instantiate the object of __database class
$object_items = new __database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$items = $object_items -> fetch_all($sql);

echo  gmdate('j/n/Y', strtotime($item['pg_created']));
?>

it would great if you can give me some hint to start!

thanks,
Lau

2

I got it sorted now thanks so much! :slight_smile:

3

If you only need the dates in one format in the PHP then you could use DATE_FORMAT in the SQL to reetrieve them in that format in the first place.

Alternatively if you need to process it in multiple formats then extract it from the database into a PHP date field and then use the format method to format it.