Forum
Moderator: General Moderators
- dull1554
- Forum Regular
- Posts: 680
- Joined: Sat Nov 22, 2003 11:26 am
- Location: 42:21:35.359N, 76:02:20.688W
Forum
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
thanks a million
- Sevengraff
- Forum Contributor
- Posts: 232
- Joined: Thu Apr 25, 2002 9:34 pm
- Location: California USA
- Contact:
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);
?>- Sevengraff
- Forum Contributor
- Posts: 232
- Joined: Thu Apr 25, 2002 9:34 pm
- Location: California USA
- Contact:
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
I havn't used mysql's timestamp stuff, but you would probally do something like this:
i wouldn't know how to format it.
Code: Select all
if( $time_from_db < strtotime("-1 day") ) {
echo "you may attack";
}Code: Select all
CREATE TABLE `sef` (
`date` TIMESTAMP NOT NULL
);
INSERT INTO `sef` ( `date` )
VALUES (
NOW( )
);- dull1554
- Forum Regular
- Posts: 680
- Joined: Sat Nov 22, 2003 11:26 am
- Location: 42:21:35.359N, 76:02:20.688W
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
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
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;
}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
Just do this: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?!?!
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);
?>- dull1554
- Forum Regular
- Posts: 680
- Joined: Sat Nov 22, 2003 11:26 am
- Location: 42:21:35.359N, 76:02:20.688W
then to update the timestamp can i just do this
Code: Select all
mysql_query("UPDATE topics SET date WHERE topic_id='$topic_id'");- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
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:
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
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_fieldMac