PHP: Format and sort the dates from MySQL Database

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开发者_Python百科="#">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

There's plenty of ways to do this... Using MySQL, you could

SELECT DISTINCT YEAR(dt) y, MONTH(dt) m FROM t ORDER BY dt DESC

Then loop through the results:

$year = null;
foreach ($rows as $row)
{
  if ($year != $row->y)
  {
    if ($year) echo "</ul>";
    $year = $row->y;
    echo "<h2>$year</h2>\n";
    echo "<ul>\n";
  }

  echo "<li><a href='#'>{$month_name[$row->m]}</a></li>\n";
}
if ($year) echo "</ul>";

It assumes there is an array called $month_name that maps a month number to its name.

This method is most useful if you don't need the other data for that page. If you do need the full data, then you can drop the DISTINCT from the SQL, SELECT all the field you need, and add a variable to track the current $month throughout the iteration, as the above code does with $year.

Assume date is the date column you're interested in.

SELECT DATE_FORMAT(`date`, '%m-%Y') AS `formatted_date`
FROM `pages`
GROUP BY `formatted_date`
ORDER BY `date`

08-2010
09-2010
11-2010
12-2010
02-2011
03-2011

This gives you all the months that have pages. It should be simple enough to output this in some HTML. Some functions that may help along the way: explode, mktime, strtotime, date.

You can use my trick:

1 get all the info out of mysql as-is using some of the others comment's good advice.

2 make an array using strtotime:

<php? $r=array((int)strtotime('2010-07-16')=>'2010-07-16 20:09:06',) ?>

3 --sort it using

ksort($r,SORT_NUMERIC) or krsort,

Have fun with php sorting functions. http://www.php.net/manual/en/function.sort.php

Note. the only down fall to this approach is that in a 32 bit machine the numeric values have limits and numbers higher than (int)2147483647 have to be cast as (strings) or (float) to be used as array keys.

You can us a dot (string)'2147483647.'