CODEWITHSUNDEEP
mysqlsql
user1716672
user1716672

Reputation: 1073

Mysql - timestamp column not updating

I have a User table with a column, last_login, of type timestamp. Structure:

CREATE TABLE IF NOT EXISTS `User` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(80) NOT NULL,
  `username` varchar(80) NOT NULL,
  `password` varchar(80) NOT NULL,
  `facebook_login` varchar(3) NOT NULL DEFAULT 'no',
  `facebook_id` varchar(50) NOT NULL,
  `last_login` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;

I want to update it when a user logs in with the current timestamp. I do:

 $sql = "Update User set last_login = ".time()."
         Where id = :id";

 $stmt = $db->prepare($sql);  
 $stmt->bindParam("id", $user->id);
 $stmt->execute();

In the mysql log file I can see this query is getting executed:

 Update User set last_login = 1381949425 Where id = '1'

But somehow, it says "no rows affected". How is this possible? There is a user in the table with id of 1...

Upvotes: 0

Views: 714

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179074

I'm a MySQL person, not a PHP (?) person but what that query should cause on the MySQL side is this:

ERROR 1292 (22007): Incorrect datetime value: '1381949425' for column 'last_login' at row 1

Timestamp columns store their value in epoch seconds, but they expect datetime literals, not integers.

$sql = "Update User set last_login = FROM_UNIXTIME(".time().")
     Where id = :id";

...or...

$sql = "Update User set last_login = NOW() Where id = :id";

Unless you have a reason to use the time() function in your application, the NOW() function in MySQL will do the same thing.

Upvotes: 3

Related Questions