[SOLVED] Unix_TIMESTAMP() and DATE()

[StillAlive]

I have a MySQL Table qith a TIMESTAMP column which fills in the timestamp once something is added or edited to a row. now I'm trying to display this information in a comprenhensive format. I'm trying to use the following query:

 

$sql = "select *, UNIX_TIMESTAMP(timestamp) from $news_table order by id desc";

 

and to display the timestamp...

 

$timedate = $row['timestamp'];

 

when displayed..I get something like 2009-03-04 16:12:37

 

I would like to format that into something like 03-04-2009 4:12:37 (month-day-year 12h-min-sec)

 

here is the code for that specific case:

 

<?php

 

$connection = @mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());

$db = @mysql_select_db($db_name, $connection) or die(mysql_error());

 

$sql = "select *, UNIX_TIMESTAMP(timestamp) from $news_table order by id desc";

$result = @mysql_query($sql, $connection) or die(mysql_error());

 

while ($row = mysql_fetch_array($result)) {

$id = stripslashes($row['id']);

$postmonth = stripslashes($row['month']);

$postday = stripslashes($row['day']);

$title = stripslashes($row['title']);

$timedate = $row['timestamp'];

$article = stripslashes($row['article']);

 

$newstext = "

 

<div class=\"entry-header\">

<div class=\"postdate\">

<p class=\"postmonth\">$postmonth</p>

<p class=\"postday\">$postday</p>

</div>

 

<div class=\"newstitle\">$title <a href=\"deletenews.php?id=$id\" title=\"Delete This Article\" onclick=\"return confirm('Do you really wish to delete this article?')\" ><img border=\"0\" src=\"images/delete.png\" alt=\"Delete\" /></a> <a href=\"editnews.php?id=$id\" title=\"Edit This Article\"><img border=\"0\" src=\"images/edit.png\" alt=\"Edit\" /></a></div>

<div class=\"newsaddtime\">Added/Last Edited: $timedate</div>

</div>

 

<div class=\"article\">

<p>$article</p>

</div><br /><br />

 

";

echo $newstext;

}

 

 

 

?>

 

 

[Mchl]

date

[StillAlive]

I had already tried date() but after trying again just now I realized my MySQL query is not returning the converted unix timestamp I'm asking for..therefore date() returns the wrong date..something like "December 31, 1969, 7:33 pm"

 

what I want to do with that query is select everything from $news_table (which is the equivalent of the table name) and at the same time format the timestamps on the "timestamp" column into a unix timestamp so I can format it using date(). is this possible? or do I have to go and select column by column and use UNIX_TIMESTAMP() when it comes to selecte the "timestamp" column?

[PFMaBiSmAd]

A mysql TIMESTAMP data type is not a Unix Timestamp. Don't use the UNIX_TIMESTAMP() function on it.

 

Use the mysql DATE_FORMAT() function in your SELECT query to format a msyql DATE/DATETIME/TIMESTAMP in any format that you want.

[StillAlive]

ah! awesome, thank you very much. I got it working with the following query:

 

$sql = "select *, DATE_FORMAT(timestamp, '%a %b %D %Y %r') as formatted_date from $news_table order by id desc";

...

 

$timedate = stripslashes($row['formatted_date']);

 

which gives me something like "Wed Mar 4th 2009 04:12:37 PM"

 

Again, thank you