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
shiznatix
DevNet Master
Posts: 2745 Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:
Post
by shiznatix » Mon Jan 31, 2005 4:13 pm
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 » Mon Jan 31, 2005 4:21 pm
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]);
}
?>
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 » Mon Jan 31, 2005 4:25 pm
ignore this
Last edited by
Wldrumstcs on Mon Jan 31, 2005 4:31 pm, edited 1 time in total.
shiznatix
DevNet Master
Posts: 2745 Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:
Post
by shiznatix » Mon Jan 31, 2005 4:29 pm
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 » Mon Jan 31, 2005 4:31 pm
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 ().
shiznatix
DevNet Master
Posts: 2745 Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:
Post
by shiznatix » Mon Jan 31, 2005 4:32 pm
cool ill give it a shot, thanks man
Wldrumstcs
Forum Commoner
Posts: 98 Joined: Wed Nov 26, 2003 8:41 pm
Post
by Wldrumstcs » Mon Jan 31, 2005 4:33 pm
Sure. Good luck!
shiznatix
DevNet Master
Posts: 2745 Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:
Post
by shiznatix » Wed Feb 02, 2005 5:27 pm
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 » Wed Feb 02, 2005 8:47 pm
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.