Page 1 of 1

Unix timestamps! Problems

Posted: Thu Jun 09, 2005 3:58 pm
by tomhoad
I have some unix timestamps in a mysql column named "date".

I am echoeing them using the strtotime() function in order to translate them into a readable date.

However, my dates are all saying December 31, 1969, 6:59 pm

I have checked some of my unix timestamps using converters on the net, and they all read correct dates and times. It seems to only give me this strange date when I echo it.

Really weird.

Any help?

Heres my coding.

Code: Select all

<?php
	  
$query   = &quote;SELECT title, content, date FROM news ORDER BY date&quote;; 
    $result  = mysql_query($query) or die('Error : ' . mysql_error()); 
    while ($row     = mysql_fetch_array($result, MYSQL_ASSOC)) { 
    
		$title   = $row&#1111;'title']; 
        $content   = $row&#1111;'content'];
		$date   = $row&#1111;'date'];

$date = date(&quote;F j, Y, g:i a&quote;, strtotime($row&#1111;'date']));
		
echo stripslashes ('<span class=&quote;date&quote;>'.$date.'</span><br><span class=&quote;title&quote;>'.$title.'</span><br><br><span class=&quote;content&quote;>'.$content.'</span><br><br><img src=&quote;line.jpg&quote; width=&quote;500&quote; height=&quote;10&quote;><br><br>');    }

?>

Posted: Thu Jun 09, 2005 4:13 pm
by hawleyjr
Just change the date in your query:


Code: Select all

$query   = "SELECT title, content, FROM_UNIXTIME(date, '%Y %m %d %H:%i:%s') as dttm 

FROM news ORDER BY date";

Posted: Thu Jun 09, 2005 4:30 pm
by tomhoad
When i do this it gives me all the dates as:

June 9, 2005, 12:00 am

Argh!

Posted: Thu Jun 09, 2005 4:31 pm
by hawleyjr
What does your insert look like?

Posted: Thu Jun 09, 2005 4:35 pm
by tomhoad

Code: Select all

<?php
if(isset($_POST&#1111;'save']))
{
   $title   = $_POST&#1111;'title'];
   $content = $_POST&#1111;'content'];
   $date=time();
   
   if(!get_magic_quotes_gpc())
   {
	  $title   = addslashes($title);
      $content = addslashes($content);
      $date = addslashes($date);
   }
   include 'config.php';
   include 'opendb.php';

if (isset($_POST&#1111;'content'])) {
$content = addslashes(nl2br($_POST&#1111;'content']));
}

$query = &quote; INSERT INTO news (title, content, date) &quote;.
           &quote; VALUES ('$title', '$content', '$date')&quote;;
  mysql_query($query) or die('Error ,query failed');

   include 'closedb.php';

   echo &quote;Article '$title' successfully added&quote;;
}
?>

Posted: Thu Jun 09, 2005 4:40 pm
by hawleyjr
You are inserting a date string from PHP. You need to insert a unix time stamp.

Try the mysql function:

UNIX_TIMESTAMP()

Posted: Thu Jun 09, 2005 4:46 pm
by tomhoad
this is great stuff thanks.

could you just elaborate a bit. how would i replace the date() function with this mysql one?

im a bit new as you may be able to tell :D (to the point that the mysql manual doesnt actaully make any sense).

Posted: Thu Jun 09, 2005 5:06 pm
by hawleyjr
$query = " INSERT INTO news (title, content, date) ".
" VALUES ('$title', '$content', UNIX_TIMESTAMP())";
mysql_query($query) or die('Error ,query failed');

Posted: Fri Jun 10, 2005 11:55 am
by tomhoad
:D Sorted.

Thank you so much. 8)

How would i get the date into GMT now?

I think i use the gmdate() function, but im not confident whether that goes in the select, insert, or a seperate query!