forum like dat & times

[emrys404]

hello, I am building a community type forum and am ordering threads via their date and time (ie, newest post will be on top). Now i am having a bit of trouble choosing a method of formating the date and time so when i use the mysql function ORDER BY lastpost_date it will put, for example, 21:03 June 03 under 21:02 June 04.

I guess my main problem is having it order by time as well as date. I have tried using varchar and using the date() function but then it only organizes them by the biggest time.. and then looked into using a mysql column data type of DATE but then all the formats i see are YYYYMMM etc with the year first and is an unsightly format.

In other words i want to organize by date&time with the format HH:MM DD Month YY ie 20:03 16 June 04

any and all help is appreciated.

Thanks

-emrys

[nnichols]

SELECT *, DATE_FORMAT(lastpost_date,'%H:%i %d %M %y') AS f_lastpost_date
FROM table_name
ORDER BY lastpost_date DESC

Note: the changed alias for the formatted date so that the ORDER BY clause refers to the original column data not the formatted data

Hope this helps

[emrys404]

This looks to be what i need. However i'm a bit unlcear as to what kind of data does it read? in other words what method do you use to input the data to take advantage of this method?

Thanks
-emrys

[nnichols]

It is for accessing DATETIME or TIMESTAMP fields. To insert the current time into a DATETIME field you can use NOW() -

INSERT INTO table_name (id,......., lastpost_date) VALUES ( ,......., NOW());

and the TIMESTAMP will automatically be set when you insert a new row (as long as you don't pass it a value). The other thing to be careful about with MySQL's TIMESTAMP is that the first TIMESTAMP column of a table automatically updates when the data is updated unless you explicitly tell it not to by setting it to its previous value.

Hope this helps

[emrys404]

Okay, i almost have it, i think. Now i'm confused with this bit of code:

SELECT *, DATE_FORMAT(lastpost_date,'%H:%i %d %M %y') AS f_lastpost_date
FROM table_name
ORDER BY lastpost_date DESC

from what i can see this pulls the data out of the db according to f_lastpost_date. how can i use this formatted date to display on the page? ie

<?php echo f_lastpost_date; >?

thanks

[foolguitardude]

Easy. After executing the query, you use MYSQL_FETCH_ARRAY.

Example:

$row = mysql_fetch_array($result);

echo $row[f_lastpost_date] 

This should echo the formatted time.

Good luck.

[emrys404]

Thats exactly what i needed, thanks guys.

-emrys