PHP: Format and sort the dates from MySQL Database

Question

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

Answer 1

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.

Answer 2

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.