Updating MySQL TIMESTAMP field using time()?

Question

Here is the table of interest when exported via phpMyAdmin:

CREATE TABLE IF NOT EXISTS `users` (
  `ip` varchar(20) NOT NULL,
  `lastcheck` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `ip` (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Here is the query:

mysql_query("REPLACE INTO users SET ip = '$uip', lastcheck = '$tim'") or throwerror("part2 ".mysql_error());

$tim is set to be time();

Now for some reason lastcheck is still set as 0000-00-00 00:00:00.

Can anyone help? Thanks.

Answer 1

try using date('Y-m-d H:i:s') in stead of time()

time() in php returns a unix timestamp and that isn't a valid insert for MyySQL datetime

hope this helps

Answer 2

I am not sure if maybe I understand your problem, looking at column lastcheck declaration CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP that means when ever you update (ip = '$uip') this column an automatic date update will also happen lastcheck. Which mean you can write you update statment as follows :

REPLACE INTO users SET ip = '$uip';

That should also update the lastcheck field, and I think it's better to use the mysql date functions to store your date/time than writing them in php and saving them as string in the database...