CODEWITHSUNDEEP
phpmysqltimestamp
Leonardo Amigoni
Leonardo Amigoni

Reputation: 2317

MySQL Timestamp when update a specific column with PHP

I want to put a timestamp when a specific column is updated.

For example:

column1: a value

dateColumn1: date column1 was updated


column2 : a value

dateColumn2: date column2 was updated

I use the function getTimestamp(), but it doesn't seem to work.

Can anyone advise me on how to do this in PHP and MYSQL?

Thanks.

Upvotes: 1

Views: 3669

Answers (4)

Sibin John Mattappallil
Sibin John Mattappallil

Reputation: 1789

Use a conditional statement. For example in the following trigger the 'password changed time' will be updated only when there is change in password column.

CREATE TRIGGER update_password 
  BEFORE UPDATE ON users
  FOR EACH ROW
    BEGIN
    IF OLD.password <> NEW.password THEN
    SET NEW.password_changed_on = NOW();
    END IF;
    END //

Upvotes: 0

Manuel Strausz
Manuel Strausz

Reputation: 226

If you want to do this only in the database, you could write a trigger that checks your conditions and updates specific timestamps if needed. But I'm assuming you don't want to fiddle around with triggers. Triggers have an advantage though: you can access the old and the new values of a row without having to write any php code.

Anyway, in case you need it here is some example code for a trigger (SQL, beware):


DELIMITER $$
DROP TRIGGER IF EXISTS Table1_UpdateTrigger $$
CREATE TRIGGER Table1_UpdateTrigger BEFORE UPDATE ON Table1
  FOR EACH ROW BEGIN
    IF OLD.column1 != NEW.column1 THEN
     SET NEW.dateColumn1 = NOW();
    END IF;
    IF OLD.column2 != NEW.column2 THEN
     SET NEW.dateColumn2 = NOW();
    END IF;
  END;
$$
DELIMITER ;

Substite Table1 with your real table names, column1, etc. with real column names.

The other way is to compare the old and the new values in php. E.g. do a query fetching the old data, compare the fields you want to check, and then do one update query per field that has changed to set the new timestamps.

Upvotes: 3

Stefan H Singer
Stefan H Singer

Reputation: 5504

I prefer using the MySQL function NOW(), like so:

UPDATE table1 SET column2 = value, dateColumn2 = NOW() WHERE somethingsomething

Upvotes: 1

Marc B
Marc B

Reputation: 360592

UPDATE table
SET column1='new value', timestampcolumn=NOW()
WHERE ...

is one way. If you don't mind the timestamp changing anytime anything in the record is updated, then use the native "timestamp" field type, which'll update itself to "now" when the record's inserted or changed.

Upvotes: 1

Related Questions