Forum

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

Post Reply
User avatar
dull1554
Forum Regular
Posts: 680
Joined: Sat Nov 22, 2003 11:26 am
Location: 42:21:35.359N, 76:02:20.688W

Forum

Post 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
User avatar
Sevengraff
Forum Contributor
Posts: 232
Joined: Thu Apr 25, 2002 9:34 pm
Location: California USA
Contact:

Post 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
User avatar
DuFF
Forum Contributor
Posts: 495
Joined: Tue Jun 24, 2003 7:49 pm
Location: USA

Post 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);
?>
User avatar
dull1554
Forum Regular
Posts: 680
Joined: Sat Nov 22, 2003 11:26 am
Location: 42:21:35.359N, 76:02:20.688W

Post 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???
User avatar
Sevengraff
Forum Contributor
Posts: 232
Joined: Thu Apr 25, 2002 9:34 pm
Location: California USA
Contact:

Post 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.
User avatar
dull1554
Forum Regular
Posts: 680
Joined: Sat Nov 22, 2003 11:26 am
Location: 42:21:35.359N, 76:02:20.688W

Post 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
User avatar
DuFF
Forum Contributor
Posts: 495
Joined: Tue Jun 24, 2003 7:49 pm
Location: USA

Post 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); 
?>
User avatar
dull1554
Forum Regular
Posts: 680
Joined: Sat Nov 22, 2003 11:26 am
Location: 42:21:35.359N, 76:02:20.688W

Post 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'");
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
Post Reply