[SOLVED] MySQL 5.0 PHP TimeStamp Issues.. GRR!!

[monkeytooth]

Ok I am getting sleepy, wanna hit the sack very soon but my stuborn mind wont let me til I resolve one issue..

 

I am tempting to use MYSQL timestamps for the first time, which seems all well and nice.. but the problem I have is mostly cosmetic. My timestamps are set to update on well update..

 

It caches the timestamp in this format 2008-06-11 01:40:11. What I want to do is convert it from that to something like June 11, 2008 1:40a any insite from anyone.. I am way to fried right now to try and put together what is most likely an easy fix with mktime or something.. so help is greatly appreciated.

[MadTechie]

if you mean from SQL then try this

SELECT DATE_FORMAT(theDateField, '%M %d, %Y %l:%i%p') as FormattedDate FROM table

[hamza]

if you mean from php then you the date() function

 

  date (" over here define the formate you want  " ,  strtotime(  define the time stamp over here )  );

[monkeytooth]

Nope.. I think this DATE_FORMAT function is a leap in the right direction.. but I cant get it right..

 

The TableName is: tolland_about

The TableColumn is: stamptime

 

Im wanting to Take the timestamp that MySQL automaticly generates and inserts into the database and convert it to a more reader friendly view. The automaticly generated timestamps look and are formated like: 2008-06-11 01:40:11

 

I have used the above example with no sucess.. started doing searches via google for more indepth views on the function all see to point generally towards the same example above but I dont know where I went wrong..

 

my last attempt was (23rd attempt with yet another spin to make it work...)

$sql_datey= "SELECT DATE_FORMAT(stamptime, '%b %e %Y') FROM tolland_about";
$datey= mysql_query($sql_datey) or die("Error in query: $sql_datey. " . mysql_error());
echo $datey;

 

as previously mentioned I am at my wits end, fried, but my damn stuborn brain wont let me sleep til I can get this figured out.. So any help I am greatly thankful for..

[monkeytooth]

anyone?

[craygo]

<?php
$sql_datey= "SELECT DATE_FORMAT(`stamptime`, '%b %e %Y') AS `tstamp` FROM `tolland_about`";
$datey= mysql_query($sql_datey) or die("Error in query: $sql_datey. " . mysql_error());
while($r = mysql_fetch_assoc($datey)){
echo $r['tstamp']."<br />\n";
}
?>

 

Ray

[monkeytooth]

HAH! Thank you!!! To think I tried it that way earlier or atleast something remotely close to the effect there of.. and didn't do the while part right.. but you my friend are on the money, Thank you ever soo much.

[craygo]

The while is only there because I assume you are grabbing all of the records. You query does not have a where clause so all records will be returned. Anytime you want to retrieve more than one record you would use a while statement. If you are retrieving only one record you would use

<?php
$sql_datey= "SELECT DATE_FORMAT(`stamptime`, '%b %e %Y') AS `tstamp` FROM `tolland_about` WHERE `somefield` = 'somevalue' LIMIT 1";
$datey= mysql_query($sql_datey) or die("Error in query: $sql_datey. " . mysql_error());
$r = mysql_fetch_assoc($datey);
echo $r['tstamp']."<br />\n";
?>

 

Ray

[monkeytooth]

Ah.. true, but there is gonna be once or twice that i use a where clause...