[SOLVED] mktime grouping

[nezbo]

Hi all

 

I have a mysql database and i am trying to sort and group by the mktime, the problem that i am having is most if the times are difrent so it is maning loads of groupings.

 

so what i want to do is set the date to date("M-Y" mktime) so that i have it in a format like Jan-2008 then i would like to be able to group by that..

 

i have tried

$getTheUser = @mysql_query("SELECT * FROM date('M-Y-', dental_holidayrequest) WHERE timeStampStart > '$nowTime' || timeStampEnd > '$nowTime' && valid = 0 ORDER BY date('M-Y-', dental_holidayrequest) GROUP BY date('M-Y-', dental_holidayrequest)");

 

but that does not work. so i have been looking at sort(array) and i have been looking for a group(array) fundction but i am not to sure how to use them etc...

 

 

[GingerRobot]

Did you want something like this?

 

SELECT *,DATE_FORMAT(dental_holidayrequest,%b-%Y) as x FROM yourtable GROUP BY x ORDER BY x

 

Im assuming that dental_holidayrequest was the field with the date in. What field type is it?

[nezbo]

cool chears for that....

 

i know have this code :

$getTheUser = @mysql_query("SELECT *, DATE_FORMAT(timeStampStart,%b-%Y) as x FROM dental_holidayrequest GROUP BY x ORDER BY x ");
while ($getTheUser2 = @mysql_fetch_array($getTheUser))
{
	echo date("M-Y", $getTheUser2['timeStampStart']);
}

 

it is still not working

[GingerRobot]

1.) Dont surpress the errors if you're looking for them.

2.) The formatting has already been done for you.

 

Try this:

 

$sql = "SELECT *, DATE_FORMAT(timeStampStart,'%b-%Y') as x FROM dental_holidayrequest GROUP BY x ORDER BY x";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result)){
    echo $row['x'].'<br />';
}

[nezbo]

That is great cheers...

 

Just one more problem, the date that keeps coming out have a year of 2012, but all the dates are 2008 or 2009.

 

Jul-2012

Nov-2012

Oct-2012

 

 

is there any way the mktime will be diffrent than the DATE_FORMAT funtion on mySQL?

 

Cheers.

[nezbo]

Sorted it i think,

 

SELECT *, DATE_FORMAT(FROM_UNIXTIME(timeStampStart),'%b-%Y') as x FROM dental_holidayrequest GROUP BY x ORDER BY x

 

i think this is working

 

Cheers