Page 1 of 1

Format and sort the dates from MySQL Database

Posted: Tue Sep 28, 2010 8:24 pm
by lauthiamkok
Hi,

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

Code: Select all

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?

Code: Select all

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

Code: Select all

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

Re: Format and sort the dates from MySQL Database

Posted: Tue Sep 28, 2010 10:03 pm
by twinedev
[text]SELECT DISTINCT DATE_FORMAT(pg_created,'%Y-%b') FROM root_pages ORDER BY pg_Created;[/text]

Then when you get the rults back, they will be like this:
[text]2009-Mar
2010-Sep[/text]

So loop though them, splitting it to Year/Month

Code: Select all

list($strYear,$strMonth) = explode('-',$strResult);
Now as you are looping though, when $strYear doesn't equal the last row's year, output a header for the year.

-Greg

Re: Format and sort the dates from MySQL Database

Posted: Tue Sep 28, 2010 10:10 pm
by lauthiamkok
thanks so much!

this is my version of it,

Code: Select all

$sql = "
	SELECT
	pg_url AS URL,
	EXTRACT(YEAR FROM pg_created) AS Year,
	EXTRACT(MONTH FROM pg_created) AS Month
	
	FROM root_pages
	
	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);

Code: Select all

<?php 
$year = null;
if ($total_items > 0)
{

	foreach($items as $item)
	{
		$pg_url = str_replace(' ', '-', strtolower($item['URL']));
		
		if ($year != $item['Year'])
		{
			if ($year) echo "</ul>";
			$year = $item['Year'];
			echo "<h2>$year</h2>\n";
			echo "<ul>\n";
		}
		
		echo "<li><a href='".HTTP_ROOT."newsletter/".$pg_url."' target='_blank'>".date("F", mktime(0, 0, 0,$item['Month']))."</a></li>\n";
	}
	if ($year) echo "</ul>";
}
?>
thanks :D