2 replies to this topic

[pirateXcore]

Okay, I made a few mistakes when starting to make a forum (more than a few)
I've started to get them worked out, I have the index page to where it's working right. (all of it was the order things were displayed was off)
it updates the date and time of the topic every time a post is submitted, now, in trying to display these topics by the date and time in descending order, it doesn't work.
Now, each time and date looks like m/d/yyyy h:m AP

I'm thinking that when ordering them, that slashes and colons f*** it up?
I'd rather not go through EVERY topic and change this to be the right way, so, can anyone help with a solution? :\
I don't know what ways around this I have...and it's a bummer that it's still doing this, i thought i had it fixed.

Thanks, peace!


Edit: I decided to use the time() function, and add another field...then compare that instead. (had to manually update each post & topic though)

Edited by tgs, 22 August 2007 - 12:42 AM.

[.CJ]

In your "topics" table, have a field called "lastposted" and when inserting, use time().

When querying it, just use

Quote

$query = mysql_query("SELECT * FROM topics WHERE id = '$id' ORDER BY lastposted DESC");

Obviously change to your own code.

Sorry if I'm wrong or mis-understood.

- Chris.

[Demonslay]

For best practices, you should really be using a DATETIME or TIMESTAMP field, and inserting anything returned by functions in PHP such as time() with the MySQL function FROM_UNIXTIME(). Then, retrieve it by UNIX_TIMESTAMP() and an alias. This will allow you to mess with alot of useful MySQL date and time functions for things such as sorting and comparing (MySQL Date and Time Functions).

Example SQL queries.

Inserting (with PHP time() function as the $time variable).

INSERT INTO `table`(`datetime_column`) VALUES(FROM_UNIXTIME($time));

And retrieving it back.

SELECT UNIX_TIMESTAMP(`datetime_column`) AS date FROM `table`

Edited by Demonslay, 22 August 2007 - 05:02 PM.