Hi,
We have a problem with different behavior between executing mysql-query in phpmyadmin and Codeigniter. In the same UPDATE-Query we would like do both save an old timestamp (means: "move" to an other field) and set the new timestamp. That doesn't work with Codeigniter. Codeigniter sets both fields with the new value.
Why that?
One thing we recognize: It works with Codeigniter, If we stop using the mysql-function "ON UPDATE CURRENT_TIMESTAMP". Actual "ON UPDATE CURRENT_TIMESTAMP" is a "core-function" of our system, because we do not implement a modified=NOW() statement at all.
Code:
CREATE TABLE `test` (
`id` int(6) NOT NULL,
`name` char(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`created` datetime DEFAULT NULL,
`modified2` datetime DEFAULT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `test` (`id`, `name`, `created`, `modified2`, `modified`) VALUES
(1, 'Test1', NULL, NULL, '2018-11-29 11:50:39'),
(2, 'Test2', NULL, NULL, '2018-11-29 11:50:39');
in phpmyadmin
Code:
UPDATE
test
SET
modified2 = modified,
modified = NOW()
WHERE
id = 1
results (as expected):
(1, 'Test1', NULL, '
2018-11-29 12:50:39', '2018-11-29 11:52:44')
in Codeigniter
PHP Code:
$this->db->query('
UPDATE
test
SET
modified2 = modified,
modified = NOW()
WHERE
id = 1
');
results (not expected):
(1, 'Test1', NULL, '
2018-11-29 11:52:44', '2018-11-29 11:52:44')
It should
- set "modified2" with the old value of "modified" (2018-11-29 12:50:39)
- set "modified" with the new value
How could that be?
Best,
kbs170