checking for changes

[AV1611]

I have a program that's been in place for a while.  Basically, it tracks jobs.

 

Once the job has been completed, a field is populated with a date.  At that point the presence of a date is how the job is "statused"

 

To the question, without changing the workflow, is there a way to pull from mysql the date of last change/edit of a record?  if there is, I could simply compare the two dates daily with a cron job to send an email to show that a change was made to a job that was previously marked as closed.  I really hope this makes sense.  I am only interested it sending an email (or other notification) that a job has changed SINCE THE DATE IT WAS CLOSED.  I don't care about the date beyond that.

 

I'm open to other ideas, but am trying to keep from doing too much recoding of the app.

 

Thanks.

 

[budimir]

if you posted your database structure it would be much easier.

 

But basicly you could do:

 

$sql = "SELECT * FROM table_name WHERE id='$id' ORDER BY date DESC";

 

So this would be the last date when the change was made for that ID. And then you could do the check to see when was last change was done and send an email.

 

[AV1611]

Sorry, perhaps I was not clear.

 

I need to know if there is a way to get MySQL via query to tell you when a record was last modified.  Not the date within the table.  There may be a change to the record that doesn't involve changing the date that was entered. 

 

 

[laffin]

add a new field, with datetime as the type

ALTER TABLE mytable ADD dtm TIMESTAMP

 

than create a triggers for inserts and updates

DELIMITER //

 

CREATE TRIGGER dtm_insert

BEFORE INSERT ON mytable

FOR EACH ROW

BEGIN

SET NEW.dtm = NOW();

END;//

 

CREATE TRIGGER dtm_update

BEFORE UPDATE ON mytable

FOR EACH ROW

BEGIN

SET NEW.dtm = NOW();

END;//

 

DELIMITER ;

 

I think that's right

now the date modified field is done automatically

[AV1611]

I'll have to read the man on your solution, but would there now be a field called TIMESTAMP (or would it be dtm )?

 

That field would be overwritten each time the record is altered, so how do I know if the table is altered? 

 

I guess I still don't see the answer :/

[laffin]

in the example i give, dtm is changed on INSERT & UPDATE queries,

SELECT dtm FROM mytable

query works

[AV1611]

wouldn't I still need to capture the value in that field to another table then check if it's been changed every time ANY record is updated to see if

1.  The record was ever closed

2.  When the closure ocurred

 

???

 

 

[revraz]

You guys are providing code to show him the last time that field was updated, but not if the record itself was ever modified.

 

So what you would have to do is a compare, and compare it to two dates/times.  Maybe a field called modified and that gets set to 1 and when you check which ones have been modified, it sets it back to 0.

[revraz]

Another thought is to have a completely seperate table that records all changes and to what record.

[AV1611]

I guess you answered my question.

 

I know how to how to capture the record and send an email by comparing.  It's a lot of work, but I've done it before.

 

What I really want to know is this:

 

Is there some variable that I can query from MySQL directly that will tell me the last time a record was edited?

 

I think that's the right question. 

 

[revraz]

I would ask Fenway in the MySQL forums.

[laffin]

why not just create a field with say

UPDATED tinyint default '0',

 

then when the record is updated, change it to 1

so processing wud need only check this field, and reset it to 0 when done

 

[revraz]

Um, that's exactly what I said above.