Date value not showing, shows zeros

[Tunnleram]

I'm having a strange issue that I'm sure has a reasonable answer, but it's making me nuts.

I am using the code below, but no matter what I do, on the host's server it displays the created_date as '0000-00-00 00:00:00'. I get the correct response locally. If I go to the server and run a query in phpmyadmin it returns fine, if I run it locally it works fine.

Local version: 5.1.37
Server version: 5.0.96-community

Is there so much different between these versions that the code below won't work?

$data = mysql_query("select id, created_date, title
        from posts
        ORDER by id DESC") or die(mysql_error()); 
     $num = mysql_num_rows($data); 

     while($info = mysql_fetch_array( $data )) 
     { 

     $postid = $info['id'];
     $created_date = $info['created_date'];
     $title = $info['title'];

     }

     echo 'postid: ' . $postid;
     echo '<br />';
     echo 'created_date: ' . $created_date;
     echo '<br />';
     echo 'title: ' . $title;
     echo '<br />';

Here is what I get from the echo command:

postid: 16
created_date: 0000-00-00 00:00:00
title: New Site Design

but the following is expected

postid: 16
created_date: 2012-12-10 21:45:00
title: New Site Design

Table code:

CREATE TABLE IF NOT EXISTS `posts` (
  `id` int(11) NOT NULL auto_increment,
  `created_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `modified_date` datetime NOT NULL,
  `author_id` int(11) NOT NULL,
  `title` varchar(250) NOT NULL,
  `content` longtext NOT NULL,
  `excerpt` mediumtext NOT NULL,
  `image_filename` varchar(150) NOT NULL,
  `status` varchar(150) NOT NULL,
  `category` varchar(500) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=263 ;

--
-- Dumping data for table `posts`
--

INSERT INTO `posts` (`id`, `created_date`, `modified_date`, `author_id`, `title`, `content`, `excerpt`, `image_filename`, `status`, `category`) VALUES
(16, '2012-12-15 09:45:00', '2012-12-15 04:45:00', 5, 'New Site Design', '<p>I''m starting to setup the new site design. Unfortunately I stayed with the Worpress design too long and not only did it get stale, but it got hacked which is common. It''s not that they have poor security, but since everyone uses them, it''s inevitable that someone will break into your site. Hopefully my custom job will be better anyway. <a href="www.paulille.com">Testing Link</a></p>', '', 'testimage200.png', 'active', 'Website');

Any help is much appreciated. Thank you in advance.

[urtrivedi]

I think on your server created date is 000000.

[facarroll]

I think I agree with urtrivedi. Does your host server run a timeclock? Perhaps not.

[Tunnleram]

I'm not sure I understand what you mean.

If I run the query in phpmyadmin or view the value in the table I see '2012-12-15 09:45:00'.

How would it pull the server time? What do you mean by timeclock?

[Tunnleram]

Anyone have any ideas?

[drjohn]

I just created your table on my local server, moved the } in the while loop to AFTER the last ECHO, so I could see the row you used and a new row I inserted without entering a created_date. I got exactly the expected result - two sets of data, your original single row data set and my new row with the correct created timestamp.

My local server version: 5.0.24a-community-nt - older than yours and it worked.

I'm afraid this simply makes it even stranger, as your table definition worked, your insert worked, my manual insert via phpMyAdmin worked, and your php code worked!!!

I can't see in a single row table the position of that closing } of the while making a difference, but at least try moving it to the correct place and see what happens - otherwise you will always only see the last row found's data.

[Tunnleram]

I spoke with my host who suspects the issue is the version of mysql on the host, but they moved it and I have the same situation. I've asked them to verify that it's been moved.

[Tunnleram]

I don't know what it was specifically, but the issue was resolved by moving my site to another server with a different version of mySql on it.