[SOLVED] Order by month

[SkyRanger]

Is there a way to order entries by month when date = M d y

 

So the output would be May 01 07

[redarrow]

is it a timestamp in the database

[SkyRanger]

Yeah, when a visitor posts something it posts:

 

$postdate = date('M d y); 

[SkyRanger]

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

[AndyB]

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.

[SkyRanger]

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.

[obsidian]

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.

[SkyRanger]

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

[obsidian]

Two options:

 

1) Use MySQL DATE_FORMAT() in your SELECT

 

2) Use date() and strtotime() combination within PHP

[SkyRanger]

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'];

[SkyRanger]

Think I figured it out:

 

$date = new DateTime('".$row['opdate']."');
echo $date->format('M d, Y  H:i'); 

[SkyRanger]

Nope, that didn't work, got some nasty errors with that code.

[SkyRanger]

Got it, thanks guys for all of you help:

 

$pullrdate = $row['opdate'];
$postdate = new DateTime($pullrdate);
echo $postdate->format('M d, Y  H:i'); 

[obsidian]

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;