[RESOLVED] group entries by year?

v3nt

i've got a timestamp but can't seem to groupd them by year;

$queryy = "SELECT * FROM run_content WHERE kind ='news' GROUP BY 'datecreated[2]datecreated[3]' ORDER BY datecreated DESC";

//i was hoping this would group up 03, 04, 05 etc from 20051005063752

$resulty = mysql_query($queryy) or die ("Error in query: $query. " . mysql_error());

while ($row = mysql_fetch_array($resulty)) {
$datecreated = $row['datecreated'];
$years = "20$datecreated[2]$datecreated[3]";
$dts++;
echo "$years $dts";
}

is there a comand, ie GROUP BY datecreate'YEAR' ?

many thanks...

_RL_Ian

v3nt wrote:
i've got a timestamp but can't seem to groupd them by year;

$queryy = "SELECT * FROM run_content WHERE kind ='news' GROUP BY 'datecreated[2]datecreated[3]' ORDER BY datecreated DESC";

//i was hoping this would group up 03, 04, 05 etc from 20051005063752

$resulty = mysql_query($queryy) or die ("Error in query: $query. " . mysql_error());

while ($row = mysql_fetch_array($resulty)) {
$datecreated = $row['datecreated'];
$years = "20$datecreated[2]$datecreated[3]";
$dts++;
echo "$years $dts";
}

is there a comand, ie GROUP BY datecreate'YEAR' ?

many thanks...

Hey v3nt,

You're almost there!

All you need to do is run the date_format function inside mysql to convert the date to a year.

$queryy = "SELECT * FROM run_content WHERE kind ='news' GROUP BY date_format(datecreated, %Y) ORDER BY datecreated DESC";

v3nt

thanx for the reply but -»
date_format(datecreated,%Y) ah still an error;
You have an error in your SQL syntax near '%Y) ORDER BY datecreated DESC' at line 1
?
is date_format the right command i can't find it in php.net?

v3nt

ah! - date_format('datecreated','%Y'), stops the error, but it only shows one result?

v3nt

ok= date_format(datecreated,'%Y'), without the commas aroun the field name!
works a treat...

_RL_Ian

v3nt wrote:
ok= date_format(datecreated,'%Y'), without the commas aroun the field name!
works a treat...

Sorry about that. I just typed it in the window here and it's hard to tell the syntax 🙂

Glad it worked.