nezbo Posted June 4, 2008 Share Posted June 4, 2008 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... Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted June 4, 2008 Share Posted June 4, 2008 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? Quote Link to comment Share on other sites More sharing options...
nezbo Posted June 4, 2008 Author Share Posted June 4, 2008 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 Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted June 4, 2008 Share Posted June 4, 2008 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 />'; } Quote Link to comment Share on other sites More sharing options...
nezbo Posted June 4, 2008 Author Share Posted June 4, 2008 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. Quote Link to comment Share on other sites More sharing options...
nezbo Posted June 4, 2008 Author Share Posted June 4, 2008 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.