473,323 Members | 1,570 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Getting the date when a database table is last updated

pradeepjain
563 512MB
i am using mysql..and i am using php forms ..i need to display the date when the table was last updated for accuracy ..so any idea on this


Thanks,
Pradeep
Jan 14 '08 #1
8 2819
nathj
938 Expert 512MB
i am using mysql..and i am using php forms ..i need to display the date when the table was last updated for accuracy ..so any idea on this


Thanks,
Pradeep
Hi Pradeep

The simple solution to this is to have two fields on every table - createDate and editDate. the first is populated when a record is first created. The second is updated every time a record is altered - including first creation.

So when a table update is done the editDate is always set to the current date. Then you simply have to write some basic SQL to find out when a record was last updated or even the last time anything was updated in the table.

If you need some code just give me a shout and I'll draft some basic SQL for you.

Cheers
nathj
Jan 14 '08 #2
pradeepjain
563 512MB
as per u i need to add to fields now..ok but for this i need to modify all tables now....is there any other way or should i use the same way

Thanks,
Pradeep
Jan 16 '08 #3
nathj
938 Expert 512MB
as per u i need to add to fields now..ok but for this i need to modify all tables now....is there any other way or should i use the same way

Thanks,
Pradeep
Pradeep,

I defintely think the extra fields solution is the best way to go. I add these fields to every table I ever build. If you need to do this retreospectivley it should be possible to work through a list of tables (possibly retreived from the database) and issue an apprpriate ALTER TABLE command on each one to add the two fields.

Cheers
nathj
Jan 16 '08 #4
ifedi
60
Hi Nathj,

Please do provide the example code you promised. I need that kind of thing now.
I know TIMESTAMP fields that reflect INSERT time, but as for reflecting UPDATE and DELETE times, I' not sure I'm now certain about that.
Right now, I can kind of imagine adding timestamp update lines to all update and queries using PHP. But I imagine there's got to be an easier MySQL internal mechanism.
So, can we have it sir?
Thanks in advance.
Ifedi.
Jan 17 '08 #5
lotus18
866 512MB
Hi ifedi

If I got your problem, try this

Expand|Select|Wrap|Line Numbers
  1. $today = date("F j, Y g:i a");
This will get the current date : )

Rey Sean
Jan 17 '08 #6
nathj
938 Expert 512MB
Hi Nathj,

Please do provide the example code you promised. I need that kind of thing now.
I know TIMESTAMP fields that reflect INSERT time, but as for reflecting UPDATE and DELETE times, I' not sure I'm now certain about that.
Right now, I can kind of imagine adding timestamp update lines to all update and queries using PHP. But I imagine there's got to be an easier MySQL internal mechanism.
So, can we have it sir?
Thanks in advance.
Ifedi.
Hi,

To get the the date a particular record was updated the SQL would be:
Expand|Select|Wrap|Line Numbers
  1. SELECT editDate FROM table where id=x 
  2.  
The WHERE clause can be anything you like really it just dtermines whcih records are being checked. If you want to select the record most recently updated try something like:
Expand|Select|Wrap|Line Numbers
  1. SELECT a.is, max(editDate) as latestEdit FROM table wherex group by a.id, a.editDate
  2.  
You'll need to play around with this psuedo code to get the where clause and group by clause correct but hopefully this should get you going.

when selecting out dates it is also possible to use date_format in MySQL to get the result in the format you want.

nathj
Jan 17 '08 #7
pradeepjain
563 512MB
i finally found the solution to it..


Use SHOW TABLE STATUS and get the value for Update_time column. This method is not applicable on InnoDB tables.

Thanks,
Pradeep
Jan 30 '08 #8
nathj
938 Expert 512MB
i finally found the solution to it..


Use SHOW TABLE STATUS and get the value for Update_time column. This method is not applicable on InnoDB tables.

Thanks,
Pradeep
Well I've learnt something there and that's always nice so thank you for posting back your final solution.

nathj
Jan 30 '08 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Raphael Gluck | last post by:
Hi, Is it possible for one to program one's pages as such that when a database table is updated over the web, via a form, that an e-mail confirmation is sent to a specified address, notifying...
2
by: Mark Reed | last post by:
Hi all, Scenario: I have about 10 people using a front end database which only 2 have the privilege to update the information. This is done by pasting the contents of a notepad report into a...
1
by: eal1 | last post by:
Does anyone know how to have Access put the "last updated" date on a report? I have a table of our university's athletic events, grouped by each sport per page. I'm trying to get the report to...
2
oszapped
by: oszapped | last post by:
I am new to creating SQL queries and need some help. I am trying to query from three tables. 1 - A table with a list of Drawings (DRAWINGS) 2 - A table with the Revision history of the Drawings...
10
by: Cliff72 | last post by:
Is there a way in VBA to check if a linked table in the database has been updated? Example: I have a table "LedgerTemp" which is a direct link to a text file on the LAN "Ledger.txt" This text...
3
pradeepjain
by: pradeepjain | last post by:
i am using mysql..and i am using php forms ..i need to display the date when the table was last updated for accuracy ..so any idea on this......any sql macro for this.. Thanks, ...
4
by: babamc4 | last post by:
Hi All I'm creating a new database for a patient log to include patients who have been screened for trials who are eligible and not eligible. Every month I need to run a report saying who is...
9
vikas251074
by: vikas251074 | last post by:
I am not getting date value in spite of my good effort. This code was working in my last office where I work. Now I am trying to work at my home pc. but not getting date value. Any can help me why...
2
by: ellechim03 | last post by:
Hello. Below is a perl script wherein it has to display date when was the last update/change on the directory. Code: #!/usr/bin/perl use File::stat; use Time::localtime;
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.