Page 1 of 1
sorting with date
Posted: Mon Jan 31, 2005 4:13 pm
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?
Posted: Mon Jan 31, 2005 4:21 pm
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)){
$date = date("m-d-Y:H-i-s",$rowїdate]);
}
?>
Posted: Mon Jan 31, 2005 4:25 pm
by Wldrumstcs
ignore this
Posted: Mon Jan 31, 2005 4:29 pm
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()?
Posted: Mon Jan 31, 2005 4:31 pm
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 ().
Posted: Mon Jan 31, 2005 4:32 pm
by shiznatix
cool ill give it a shot, thanks man
Posted: Mon Jan 31, 2005 4:33 pm
by Wldrumstcs
Sure. Good luck!
Posted: Wed Feb 02, 2005 5:27 pm
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?
Posted: Wed Feb 02, 2005 8:47 pm
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.