sorting with date

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

sorting with date

Post by shiznatix »

ok i have a database with rows last_post. every time a user enters a post the last_post is updated with the time in format day/month hour:minute am/pm.

what i want is the posts to be ordered by the newest first then descending to the last post. iv used this but because of the format of last_post it messes up depending on the day and time of the post.

iv tried this:

Code: Select all

mysql_query("SELECT * FROM threads WHERE id='$fid' ORDER BY last_post DESC");
but that obviously dosnt work like i said. is there any way i could format the date so it would always do this or what? any ideas?
Wldrumstcs
Forum Commoner
Posts: 98
Joined: Wed Nov 26, 2003 8:41 pm

Post by Wldrumstcs »

You could use the mktime function to store it in your database.

Code: Select all

$date = mktime(date("H"),date("i"),date("s"),date("m"),date("d"),date("Y"));
That orders it by month/day/year/hours/minutes/seconds.

Then to display it, you could do:

Code: Select all

<?
$query  = "SELECT * FROM table";         
$result = mysql_query($query) or die("Error: " . mysql_error()); 

while($row = mysql_fetch_array($result))&#123; 
$date = date("m-d-Y:H-i-s",$row&#1111;date]);
&#125;
?>
Last edited by Wldrumstcs on Mon Jan 31, 2005 4:35 pm, edited 5 times in total.
Wldrumstcs
Forum Commoner
Posts: 98
Joined: Wed Nov 26, 2003 8:41 pm

Post by Wldrumstcs »

ignore this
Last edited by Wldrumstcs on Mon Jan 31, 2005 4:31 pm, edited 1 time in total.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

so your saying the date in the db should be made with mktime then i can order it by mktime DESC and then view it correctly with date()?
Wldrumstcs
Forum Commoner
Posts: 98
Joined: Wed Nov 26, 2003 8:41 pm

Post by Wldrumstcs »

Yea, what mktime does is make the date into a string of numbers that allows you to order it. Then to get it out of that format, use the date ().
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

cool ill give it a shot, thanks man
Wldrumstcs
Forum Commoner
Posts: 98
Joined: Wed Nov 26, 2003 8:41 pm

Post by Wldrumstcs »

Sure. Good luck!
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

errr ummm i am using this query

Code: Select all

mysql_query("SELECT * FROM threads WHERE forum='$fid' AND sticky='0' ORDER BY last_post DESC LIMIT $limitvalue, $limit")
and its giving me a strange way or ordering the pages, as if the mktime thing is kinda messed up as the days went by or when the month changed to febuary. any help?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

now the real solution is to use a valid date type for your sql dbms, fe mysql has datetime and timestamp. this way, ordering simply works.

and everytime you request data, and you want it in the day/month whatever format, you use the DATE_FORMAT in your query to get it that way.

very simple and effective.
Post Reply