在mysql查询中使用UNIX_TIMESTAMP（）更新列

Question

After doing more reading and research today I am changing the way I store timestamps in my db so they are all unix timestamps. UNIX_TIMESTAMP() in the below query is doing nothing and not sure why. There is no error, but the current value does not change when the query is executed.

Expected result is unix timestamp at the moment of the update (ex 1400899111). dtCurrLogin is type INT, not null, default 0.

$stmt = $db->prepare("UPDATE admin SET lastIP = currIP, currIP = ?, dtLastLogin = dtCurrLogin , dtCurrLogin = UNIX_TIMESTAMP() WHERE account_id = ?");
$stmt->execute(array($_SERVER['REMOTE_ADDR'], $account_id));

This really does not make any sense... I can directly enter the query into phpmyadmin and it works... minus the binding and prepared statement. So, there must be something going on with that aspect in my php causing the problem.

Answer 1

I think what you want is to have dtCurrLogin = the current unix timestamp.

UNIX_TIMESTAMP(now()) 

so:

$stmt = $db->prepare("UPDATE admin SET lastIP = currIP, currIP = ?, dtLastLogin = dtCurrLogin , dtCurrLogin = UNIX_TIMESTAMP(now())  WHERE account_id = ?");
$stmt->execute(array($_SERVER['REMOTE_ADDR'], $account_id));