Date Formatting Question

[rahish]

Hi,

I am creating a simple CMS and want a date on each page to show the date the page was created.

I have a timestamp set in the database for each new page id which sets the current timestamp when each entry is created but on pages.php it shows up with today's date not the created date.

I have the page set up to retrieve the results of all page ids and I am trying to format it with the following example.


[code]<?php

include('header.inc');
$id = $_GET["page_number"];

$query = "SELECT * FROM pages where page_id ='$id'"; //querying the database, selects a single page id


$result = mysql_query($query) or die ("Couldn't execute query.");

// Populate cells using a mysql_fetch_array script
while ($row=mysql_fetch_array($result))
{

    $page_id=$row["page_id"];
    $date=$row["date"];
    $title=$row["title"];
    $content=$row["content"];
    

    
$date =date("F j, Y, g:i a");    


echo "<div id='content'><h3>$title</h3><br />$content<br /><p class='date'>Posted: $date</p></div>";//this prints the results from the mysql_fetch_array script on the screen

}

include('footer.inc');


?>
[/code]

Any ideas?

[trq]

[code]
$date = date("F j, Y, g:i a", $date);
[/code]

[rahish]

[!--quoteo(post=380925:date=Jun 7 2006, 03:59 AM:name=thorpe)--][div class=\'quotetop\']QUOTE(thorpe @ Jun 7 2006, 03:59 AM) [snapback]380925[/snapback][/div][div class=\'quotemain\'][!--quotec--]
[code]
$date = date("F j, Y, g:i a", $date);
[/code]
[/quote]


Thanks Thorpe,

I think that worked as expected and makes sense except the date is showing

Posted: January 1, 1970, 1:33 am and in phpMyadmin it shows 2006-06-05 09:43:21 in the date field.

I am running xammp on OSX localhost.

Any ideas?

[miz_luvly@hotmail.com]

[!--quoteo(post=380921:date=Jun 7 2006, 03:42 AM:name=rahish)--][div class=\'quotetop\']QUOTE(rahish @ Jun 7 2006, 03:42 AM) [snapback]380921[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Hi,

I am creating a simple CMS and want a date on each page to show the date the page was created.

I have a timestamp set in the database for each new page id which sets the current timestamp when each entry is created but on pages.php it shows up with today's date not the created date.

I have the page set up to retrieve the results of all page ids and I am trying to format it with the following example.
[code]<?php

include('header.inc');
$id = $_GET["page_number"];

$query = "SELECT * FROM pages where page_id ='$id'"; //querying the database, selects a single page id
$result = mysql_query($query) or die ("Couldn't execute query.");

// Populate cells using a mysql_fetch_array script
while ($row=mysql_fetch_array($result))
{

    $page_id=$row["page_id"];
    $date=$row["date"];
    $title=$row["title"];
    $content=$row["content"];
    

    
$date =date("F j, Y, g:i a");    
echo "<div id='content'><h3>$title</h3><br />$content<br /><p class='date'>Posted: $date</p></div>";//this prints the results from the mysql_fetch_array script on the screen

}

include('footer.inc');
?>
[/code]

Any ideas?
[/quote]

Hi

if you have timestamp column in your database you need to take it out and display here . something like
$date=$row["timestamp_col_name"];
it will be displayed yyyy/mm/dd format you can format it to dd/mm/yyyy using php.

"$date =date("F j, Y, g:i a");" is used to display current date.

hope that helps
sab

[rahish]

[!--quoteo(post=380930:date=Jun 7 2006, 04:10 AM:name=sab)--][div class=\'quotetop\']QUOTE(sab @ Jun 7 2006, 04:10 AM) [snapback]380930[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Hi

if you have timestamp column in your database you need to take it out and display here . something like
$date=$row["timestamp_col_name"];
it will be displayed yyyy/mm/dd format you can format it to dd/mm/yyyy using php.

"$date =date("F j, Y, g:i a");" is used to display current date.

hope that helps
sab
[/quote]


Not quite sure what you mean about Taking it out of the database.

inside the fetch array, I can see that it could be formatted with a timestamp somehow

[code]$date=$row["timestamp_col_name"]; [/code]

but in this case that would make it

[code]$date=$row["timestamp_date"];[/code]

Which doesn't work shouldn't it be something more like

[code]$date=$row(timestamp["date"]);[/code]

I know that's not right


Could the select statement itself could be formatted in this way to display post dates with certain formatting?

[miz_luvly@hotmail.com]

[!--quoteo(post=380934:date=Jun 7 2006, 04:45 AM:name=rahish)--][div class=\'quotetop\']QUOTE(rahish @ Jun 7 2006, 04:45 AM) [snapback]380934[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Not quite sure what you mean about Taking it out of the database.

inside the fetch array, I can see that it could be formatted with a timestamp somehow

[code]$date=$row["timestamp_col_name"]; [/code]

but in this case that would make it

[code]$date=$row["timestamp_date"];[/code]

Which doesn't work shouldn't it be something more like

[code]$date=$row(timestamp["date"]);[/code]

I know that's not right
Could the select statement itself could be formatted in this way to display post dates with certain formatting?
[/quote]

hi
u need to use DATE_FORMAT() function
yes u can use somethingl like:
select table_name.*, DATE_FORMAT(col_name, '%d-%m-%Y') AS some_name from table_name

hope it make sense this time sorry for the confusion.

[rahish]

[!--quoteo(post=380935:date=Jun 7 2006, 04:55 AM:name=sab)--][div class=\'quotetop\']QUOTE(sab @ Jun 7 2006, 04:55 AM) [snapback]380935[/snapback][/div][div class=\'quotemain\'][!--quotec--]
hi
u need to use DATE_FORMAT() function
yes u can use somethingl like:
select table_name.*, DATE_FORMAT(col_name, '%d-%m-%Y') AS some_name from table_name

hope it make sense this time sorry for the confusion.
[/quote]


Thanks Sab that worked fine, except that %d-%m-%Y

formats the result like this

05-06-2006

How do I change the letters to make it

5th June 2006, at 11am or somethink like that?

Rahish

[trq]

[code]
"%D %b %Y, at %l%p"
[/code]
There is a [a href=\"http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html\" target=\"_blank\"]manual[/a] you know?

[miz_luvly@hotmail.com]

[!--quoteo(post=380941:date=Jun 7 2006, 05:57 AM:name=rahish)--][div class=\'quotetop\']QUOTE(rahish @ Jun 7 2006, 05:57 AM) [snapback]380941[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Thanks Sab that worked fine, except that %d-%m-%Y

formats the result like this

05-06-2006

How do I change the letters to make it

5th June 2006, at 11am or somethink like that?

Rahish
[/quote]

good to hear that. OK here is a little secret (sssh)-- go here and choose whatever date format you like.
[a href=\"http://www.mysqlfreaks.com/statements/59.php\" target=\"_blank\"]http://www.mysqlfreaks.com/statements/59.php[/a]

[rahish]

Yes,

Point taken I could have worked that out for myself, thanks for the help anyway.

Rahish