Sorting by Date in a set format.

[brianbehrens]

I'm interested in taking a date field defaulted to 0000-00-00

 

and have the items display sorted in the following manner:

 

for example the following entries

 

2007-04-03 | My Title 1

2007-05-03 | My Title 2

 

Would sort and display the following

 

2007

 

May

My Title 2

 

April

My Title 1

 

It's kind of an odd request, but the only way I know how is by putting the dates in separate fields and that's still kind of iffy.  I'm a little lost here and any help would be greatly appreciated.

 

 

[Barand]

SELECT YEAR(datefield) as yr, MONTHNAME(datefield) as mth, mytitle

FROM tablename

ORDER BY datefield DESC

[brianbehrens]

Thank you.  This worked well.

 

require_once ('includes/databaseConnection.inc');
$sql = "SELECT YEAR(date) as yr, MONTHNAME(date) as mth, title FROM BLOG_ENTRIES ORDER BY date";
$entries = @mysql_query($sql)  or die (mysql_error());
if (@mysql_num_rows($entries) < 1) {
echo "There are no entries available at this time. Please check back for updates.";
} else {
$yearRepeat = "";
$monthRepeat = "";
while ($row = @mysql_fetch_array($entries)) {
	$year = $row['yr'];
	$month = $row['mth'];
	$title = $row['title'];
	//$body = $row['body'];

	if($year != $yearRepeat){
		echo "<h1>$year</h1>";
	}
	if($month != $monthRepeat){
		echo "<h3>$month</h3>";
	}
	echo "$title <br />";
	$yearRepeat = $year;
	$monthRepeat = $month;
}
}

[Barand]

I'd make a small change to your code, outputting the month also when the year changes. Prob unlikely but just in case you date goes from 2007 April to 2006 Apr then the code as you have it misses the month heading.

 

	if($year != $yearRepeat){
		echo "<h1>$year</h1>";
		echo "<h3>$month</h3>";
	}
	elseif($month != $monthRepeat){
		echo "<h3>$month</h3>";
	}

 

When you have nested levels like that, if a level changes then the lesser levels should also be assumed changed.