SkyRanger Posted May 1, 2007 Share Posted May 1, 2007 Is there a way to order entries by month when date = M d y So the output would be May 01 07 Link to comment https://forums.phpfreaks.com/topic/49477-solved-order-by-month/ Share on other sites More sharing options...
redarrow Posted May 1, 2007 Share Posted May 1, 2007 is it a timestamp in the database Link to comment https://forums.phpfreaks.com/topic/49477-solved-order-by-month/#findComment-242490 Share on other sites More sharing options...
SkyRanger Posted May 1, 2007 Author Share Posted May 1, 2007 Yeah, when a visitor posts something it posts: $postdate = date('M d y); Link to comment https://forums.phpfreaks.com/topic/49477-solved-order-by-month/#findComment-242492 Share on other sites More sharing options...
SkyRanger Posted May 1, 2007 Author Share Posted May 1, 2007 Ok, this is what I have so far but not sure how to display the months in the right order: SELECT users.*, office.* FROM users, office where id = omid and ostatus = 'Active' ORDER BY 'opdate' DESC $max opdate holds the dates like: Apr 29 07 Apr 30 07 May 01 07 I stuck in June 01 07 to see what would happen and it messed up the order May June April I am trying to figure out how to put it into the proper order etc... June 01 07 May 01 07 April 30 07 April 29 07 Mar 31 07 etc... Not sure if this is possible. Don't really wanna have to change all of the dates manually from Month = May to Month = 05 The $max code is for my pagination Link to comment https://forums.phpfreaks.com/topic/49477-solved-order-by-month/#findComment-242566 Share on other sites More sharing options...
AndyB Posted May 1, 2007 Share Posted May 1, 2007 Your life (and coding) will be much simpler if you store dates in a rational format that can be sorted, compared, etc, i.e. date("Y-m-d") - use the DATE field type. You can display dates in whatever format you want. Link to comment https://forums.phpfreaks.com/topic/49477-solved-order-by-month/#findComment-242582 Share on other sites More sharing options...
SkyRanger Posted May 1, 2007 Author Share Posted May 1, 2007 Yeah, I should have thought about that earlier...lol, But I now have almost 300 posts and it is going to be a pain going through and changing them all manually. That is why I was hoping there was a simpler way for me to fix this without having to do that. Link to comment https://forums.phpfreaks.com/topic/49477-solved-order-by-month/#findComment-242585 Share on other sites More sharing options...
obsidian Posted May 1, 2007 Share Posted May 1, 2007 That is why I was hoping there was a simpler way for me to fix this without having to do that. Honestly, having had to fix some messes similar to that you are facing, AndyB's suggestion will be much easier in the long run than trying to circumvent what the data types are meant to do. If you fix the problem now, rather than trying to quiet the noise and pretend the problem doesn't exist, your script will be much more efficient, and if you ever expand it, you'll be in a much better boat down the road. Link to comment https://forums.phpfreaks.com/topic/49477-solved-order-by-month/#findComment-242604 Share on other sites More sharing options...
SkyRanger Posted May 1, 2007 Author Share Posted May 1, 2007 Ok, finally finished converting all the dates to Y-m-d, is there a way to change the output display to Month Day Year for Example: May 01 2007 Link to comment https://forums.phpfreaks.com/topic/49477-solved-order-by-month/#findComment-242728 Share on other sites More sharing options...
obsidian Posted May 1, 2007 Share Posted May 1, 2007 Two options: 1) Use MySQL DATE_FORMAT() in your SELECT 2) Use date() and strtotime() combination within PHP Link to comment https://forums.phpfreaks.com/topic/49477-solved-order-by-month/#findComment-242763 Share on other sites More sharing options...
SkyRanger Posted May 1, 2007 Author Share Posted May 1, 2007 Ok, I tried using the DATE_FORMAT() option that you mentioned. Thought it looked easy enough. Boy was I wrong..lol, especially when I am using the sql select syntax that I require. Here is what I tried to do: $query = "SELECT users.*, office.*, office.date_format('opdate','%b %e, %Y') FROM users, office where id = omid and ostatus = 'Active' ORDER BY 'opdate' DESC $max"; $result = mysql_query($query) or die ("Query failed"); You can probably figure out what the outcome was. Not good...lol What am I doing wrong there? or should I just convert: $row['opdate']; Link to comment https://forums.phpfreaks.com/topic/49477-solved-order-by-month/#findComment-242802 Share on other sites More sharing options...
SkyRanger Posted May 1, 2007 Author Share Posted May 1, 2007 Think I figured it out: $date = new DateTime('".$row['opdate']."'); echo $date->format('M d, Y H:i'); Link to comment https://forums.phpfreaks.com/topic/49477-solved-order-by-month/#findComment-242817 Share on other sites More sharing options...
SkyRanger Posted May 1, 2007 Author Share Posted May 1, 2007 Nope, that didn't work, got some nasty errors with that code. Link to comment https://forums.phpfreaks.com/topic/49477-solved-order-by-month/#findComment-242831 Share on other sites More sharing options...
SkyRanger Posted May 1, 2007 Author Share Posted May 1, 2007 Got it, thanks guys for all of you help: $pullrdate = $row['opdate']; $postdate = new DateTime($pullrdate); echo $postdate->format('M d, Y H:i'); Link to comment https://forums.phpfreaks.com/topic/49477-solved-order-by-month/#findComment-242835 Share on other sites More sharing options...
obsidian Posted May 2, 2007 Share Posted May 2, 2007 If you choose to revisit the SQL method, try this: SELECT *, date_format(office.opdate,'%b %e, %Y') AS formatted FROM users, office where id = omid and ostatus = 'Active' ORDER BY opdate DESC; Link to comment https://forums.phpfreaks.com/topic/49477-solved-order-by-month/#findComment-243153 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.