CODEWITHSUNDEEP
phpmysqlsql
capcom
capcom

Reputation: 3337

How to get time since MySQL database last edited in PHP

I need to know the time since a table in my MySQL database was edited. Is there any way to do this in PHP? The only way I can think of is to get the update time, and compare it to the current time (which will be a little bothersome).

Upvotes: 1

Views: 1081

Answers (3)

eggyal
eggyal

Reputation: 125865

SELECT TIMEDIFF(CURRENT_TIME, UPDATE_TIME)
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'my_table'

Upvotes: 2

Sammitch
Sammitch

Reputation: 32232

It's a bit of a pain since SHOW TABLE STATUS doesn't seem to return a standard result set. Every way I tried to use it as a subquery failed. It seems like you'll need to figure it out programmatically.

SHOW TABLE STATUS
WHERE name = 'target_table';

And if you're concerned about the timezone just do a separate SELECT NOW() to get the time the mysql server has.

Note: This will only show you the time at which the table schema was updated. If you want to know when the last time a row was inserted/edited you'll have to add a timestamp column like Amirshk suggested.

Upvotes: 0

Amirshk
Amirshk

Reputation: 8258

One solution would be to add a timestamp field, that is automatically updated whenever a row is changed. Then you can find the last change time by selecting the last update value:

# Add a timestamp column:
ALTER TABLE [TABLENAME] add column `ts_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;

# Get the last update value:
SELECT MAX(ts_update) from [TABLENAME];

Upvotes: 1

Related Questions