Mysql Timestamp

[Shit4Brains]

yo, im having problems with sql timestamps. In my database the field is set to timestamp so when i add new stuff, the long date number (20030323234254) is saved.

I want to be able to pul this out of the database and show it as a normal date.

Iv'e read about using the date_format function in the sql statement.

SELECT date_format(lastaccesstime, '%M %e, %Y') FROM user WHERE userid = 'hecky';

but im using the sql in a loop, to display a list of results ( its a news script )

heres my code:

$db = mysql_connect("$host", "$user", "$pass");

mysql_select_db("$database",$db) or die("<font size=\"1\"> !! Error connecting to database !! ");

$result = mysql_query("SELECT * FROM `news` ORDER BY `id` DESC LIMIT 0, 4") or die("<font size=\"1\"> !! Could not select from Table !! ");

if ($myrow = mysql_fetch_array($result)) {

  do {

$headline = $myrow["headline"];
$news = $myrow["news"];
$date = $myrow["date"];

print("<div align=\"left\">
  <center>
  <table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" height=\"21\" width=\"70%\">
    <tr>
      <td width=\"100%\" bgcolor=\"#ffffff\">
     <p class=\"newstext\" align=\"justify\">
	  <strong class=\"blackfont\">$headline</strong><br>
	  $news
	  <br><strong>$date</strong><br>
      </p>
      </td>
     </tr>
</table>
  </center>
</div>  

<BR>");

  } while ($myrow = mysql_fetch_array($result));

} else {

  // no records to display

  echo "<p align=\"center\">Sorry, no news items were found!<br><br>";	

}

the code works fine, and displays all the news. But the dates come up as the long number. I want to be able to format that number using PHP not SQL.

is it possible???

[craigh]

SELECT date format(lastaccesstime, '%M %e, %Y') as good_time FROM user WHERE userid='hecky';

then refer the the fieldname good_time

[Shit4Brains]

i dont wanna do it with SQL!!! 🙁

cant i use php to manipulate the number?

[OhLordy]

strftime()

check out the php manual because there are loads f different things you can do with it but basically it converts a timestamp into a string as described in the first parameter.

eg

$now = time();

//places the weekday of the $now timestamp in $now_weekday
$now_weekday = strtime("%a", $now);

//places the date in the format dd/mm/yyyy in $now_date
$now_date = strtime("%d/%m/%y", $now);

Hope this helps
Good luck
Rob