[RESOLVED] Group by year

zzz

I have a list of items in a db that has a date stamp column in the following format 0000-00-00.

The list is getting long so I would like to out put the year first, and ask the user click the year to see the items for that year only.

How do I scan my table and output the years only? Is it something like:

SELECT DISTINCT YEAR(my_date) FROM mytable DESC

Looks like something is missing...

devinemke

did you try it?

zzz

yes, I got two years, which is correct, but they are both 2006...

zzz

Ok, I'm really lost. Not sure why I get twice the same year. Hre's the code I've got:

$list = $db->get_results("
		SELECT DISTINCT YEAR(my_date)
		FROM my_table");

foreach ($list as $l)
{
	$archivesBYyear = strtotime($l->my_date);
	$archBYyr = date("Y", $archivesBYyear);

echo $archBYyr ."<br />";
}

:bemused:

WDPEjoe

That's the correct usage of the query, but you're a little off in your implementation. First, how to fix it:

Change query to
SELECT DISTINCT YEAR(my_date) AS my_year FROM [table] ORDER BY my_year [DESC|ASC]

The result will have one mysql column named "my_year" which you can step through and echo all the years.

$list = $db->get_results("
        SELECT DISTINCT YEAR(my_date) AS my_year
        FROM my_table");

foreach ($list as $l)
{echo $l->my_year."<br />";}

Second, the explanation of why what you did was close, but not right.

The query is basically correct. However, when you use a function in the SELECT cause, the name of the column in the result will be FUNCTIONNAME(col) (in this case, the return column is YEAR(my_date) ). So, in $list, my_date doesn't exist, but YEAR(my_date) does.

You don't have to switch them through the strtotime()/date() functions. The result set will have valid four digit years, so you don't have to switch it through.

But by doing that, you added another little twist to confuse yourself. The parameters you pass to the two functions are nonexistant ($l->my_date) or invalid ($archivesByYear, the return from the invalid strtotime call). Every time the date() function was called without a valid parameter, it's just assuming that you are talking about the current date and time (hence, the two 2006 outputs).

zzz

Yep, I live up to my signature.

I tested -- works great!