i need to update the php timestamp column type automatically whenever some update is done on its particular certain row. can it be done?
the format of the timestamp is "0000-00-00 00:00:00"
i also want to compare the current(system) timestamp with the value already set in the column. for example, i want to compare the current system timestamp with the DB value and if the current value is more than 10 minutes ahead of the DB value, do a certain action. how can i check this?
The easiest way is to set on update CURRENT_TIMESTAMP on the row in the DB (assuming you are using MYSQL). The other way is to insert the value of NOW() when you do your insert.
When you query the DB, instead of just writing SELECT timestamp FROM... write SELECT UNIX_TIMESTAMP(timestamp) as stamp FROM.... This will give you it back as a UNIX time stamp. You can then compare that to the value from time(), and check if it's more than 600 seconds from the DB timestamp.
The MySQL function NOW() will always give you the current timnestamp.
This will set the modified field to the current timestamp:
UPDATE user SET realname='John Smith', modified=NOW()
or this will give you the records modified in the last 10 minutes.
SELECT * FROM user WHERE modified > TIMESTAMPADD(MINUTE, -10, NOW())
A 'timestamp column' is NOT php. THat's mysql. MySQL will, by default, auto-update the first timestamp column in any given table anytime the record is changed or inserted.
To select based on that timestamp, you'd do
SELECT field,field,field
FROM yourtable
WHERE (timestampfield, DATE_SUB(now(), INTERVAL 10 MINUTE)
