Got me a table with a field that should contain the date & time the record was inserted/updated:
CREATE TABLE `ae_users` (
`location` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`item_name` tinytext COLLATE utf8_unicode_ci NOT NULL,
`item_cost` decimal(10,0) NOT NULL,
`item_percent` decimal(10,0) NOT NULL,
PRIMARY KEY (`location`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
However, the value for last_updated is “0000-00-00 00:00:00” after this query is run:
$query = sprintf("INSERT INTO ae_users (location,last_update,item_name,item_cost,item_percent)
VALUES ('%s','%d','%s','%d','%d')
ON DUPLICATE KEY UPDATE item_name=VALUES(item_name),item_cost=VALUES(item_cost),item_percent=VALUES(item_percent)",
mysql_real_escape_string($listing, $dbh),
time(),
mysql_real_escape_string($item_name, $dbh),
mysql_real_escape_string($item_cost, $dbh),
mysql_real_escape_string($item_percent, $dbh));
Same result for insert or update. Server version is 5.1.52. Any suggestions why last_updated doesn’t show the current timestamp?