Page 1 of 1

Forum

Posted: Fri Jan 23, 2004 4:10 pm
by dull1554
i've been working on a forum and i was wondering how i would take a list of dates formatted like this Sat 17 of January, 05:21 pm (if i have to format the data differently i can) and order the dates from newest to oldest. I'm pulling these dates out of a mysql database and these dates corrispond to posted topics or topic replies. any thoughts or examples as to how i might do this would be awesome, i've been tring to come up with a way to do this so the newest post shows up at the top of my forum......

thanks a million

Posted: Fri Jan 23, 2004 4:35 pm
by Sevengraff
I think you would need to change the way you store dates.

I've found that using a Unix timestamp is the best way to store dates. Using it you can easily get the most recent items from mysql by doing a ORDER BY date DESC

Posted: Fri Jan 23, 2004 5:28 pm
by DuFF
Storing them as a DATETIME field would also work well. Then, using PHP, you can change the format using the date function.

Code: Select all

<?php
$timestamp      = strtotime($datefromdatabase);
$date_format    = 'M j, Y g:i:s';  // Jan 23, 2004 6:28:31
$newdate         = date($date_format, $timestamp);
?>

Posted: Fri Jan 23, 2004 5:50 pm
by dull1554
@ Sevengraff/duff
im not on a unix machine....Windows unfourtnatly...can you elaborate how i would format the date and what type of field it would have to be stored in my database???

Posted: Fri Jan 23, 2004 6:51 pm
by Sevengraff
you can use unix timestamps on a windows machine. To store it in MySQL I juse use a BIGINT(10). time() will give you the timestamp for right now, and you can use date() to format it for output. Once you have the time that the user last attacked, you can compare it like

Code: Select all

if( $time_from_db < strtotime("-1 day") ) &#123; 
    echo "you may attack";
&#125;
I havn't used mysql's timestamp stuff, but you would probally do something like this:

Code: Select all

CREATE TABLE `sef` (
`date` TIMESTAMP NOT NULL
);

INSERT INTO `sef` ( `date` )
VALUES (
NOW( )
);
i wouldn't know how to format it.

Posted: Fri Jan 23, 2004 7:00 pm
by dull1554
now i got it all set up but i want to display the 10 last updated topics....i am doing this but am aware of the fact that it does not work

Code: Select all

$topic_num = "1";
Print <<< EOT
<table bordercolor='#000000' width=100% border=1 cellspacing=1>
<tr>
<td height=35>subject</td>
<td height=35>author</td>
<td height=35>replies</td>
<td height=35>views</td>
<td height=35>date</td>
<tr>
EOT;
while($topic_num<"10")
{
$query = mysql_query("SELECT * FROM topics ORDER by date DESC limit 10");
$array = mysql_fetch_array($query);
Print <<< EOT
<tr>
<td height=35>{$array['subject']}</td>
<td height=35>{$array['author']}</td>
<td height=35>{$array['replies']}</td>
<td height=35>{$array['views']}</td>
<td height=35>{$array['date']}</td>
<tr>
EOT;
$topic_num++;
}

Print <<< EOT
</table>
EOT;

}
any ideas,
and also i'm using timestamp in mysql so it will auto set the date/time but its in this format "20040123195737" and was wondering if there was a way to convert it to Jan 23, 2004 7:57:37?!?!
just wondering i think i will prolly do it a different way
but just thought i'd ask

Posted: Fri Jan 23, 2004 7:52 pm
by DuFF
and also i'm using timestamp in mysql so it will auto set the date/time but its in this format "20040123195737" and was wondering if there was a way to convert it to Jan 23, 2004 7:57:37?!?!
Just do this:

Code: Select all

<?php
$timestamp      = "20040123195737";
$date_format    = 'M j, Y g:i:s';  // Jan 1, 2004 0:00:00
$newdate         = date($date_format, $timestamp); 
?>

Posted: Fri Jan 23, 2004 7:59 pm
by dull1554
then to update the timestamp can i just do this

Code: Select all

mysql_query("UPDATE topics SET date WHERE topic_id='$topic_id'");

Posted: Mon Jan 26, 2004 3:16 am
by twigletmac
Firstly MySQL TIMESTAMP != UNIX timestamp so you can't use PHP's date() function to format it. You can however use the DATE_FORMAT() function (check out MySQL's Date and Time Functions for the formatting codes to use to get the result you want) in your SQL SELECT QUERY:

Code: Select all

SELECT field1, field2, field3, DATE_FORMAT(date_or timestamp_field, '%d/%m/%Y') AS formatted_date FROM your_table ORDER BY date_or_timestamp_field
Secondly, MySQL's timestamp field automatically updates itself when the row it is in is updated so you don't need to do anything else, it'll also add itself automatically when the row is first inserted.

Mac