1

I want to maintain the history table for my application to track what all the fields were changed by the user.

The following is my bugs_history table structure - 

id, bugsid, userid, field_changed, old_value, new_value, created_on, created_by

So my query is, when I'll update my form and submit, how to get the field name that was changed along with old value and new value. And add the modified changes in the above history table.

I have googled a lot for this but didn't get as per my requirments. Please let me know how to achieve this.

Channaveer Hakari
  • 2,769
  • 3
  • 34
  • 45
  • http://stackoverflow.com/questions/3816839/mysql-almost-complete-auditing ..The thing which you looking is called auditing – Amar Agrawal May 25 '15 at 08:35
  • @Amar Can you please suggest any other solutions or links.. – Channaveer Hakari May 25 '15 at 09:28
  • how you want to do it ? You want to handle this through the PHP or through MySQL ? – Amar Agrawal May 25 '15 at 09:44
  • I would like to go with php – Channaveer Hakari May 25 '15 at 10:38
  • Any way when you are updating the form you might be getting it from the database.Store all these info into some variable and before inserting into the database new values compare it with previous values and accordingly insert into the `bug_history` . Auidting will always have performance issue whether you do it by yourself or using some library.So don't think what I am suggesting you is inefficent – Amar Agrawal May 25 '15 at 11:11

1 Answers1

0

If you know the field names (you have a HTML form containing them, so you probably know the names) then build a list of them that you then loop through, building a new SELECT query to get old_value and then an INSERT query to save it. The select would order on created_on DESC and LIMIT 1.

But I see a clear problem here: concurrency. What happens when two users try to edit the same bug (with the same bugsid) at the same time? They would expect the old_value to be the same for both? Or should the two operations be executed sequentially? Or should the last one to edit be warned that he's trying to edit stale data? Which one would get the "latest" created_on? This right here is your real problem, not writing the code that generates two SQL queries.

Sergiu Paraschiv
  • 9,929
  • 5
  • 36
  • 47
  • Ya i do agree with you...but if you think in this fashion then many of the web applications fail in handling this. Apart from concurrency problem, can you please suggest me some solution or links for my problem, I'll be very thankful to you. – Channaveer Hakari May 25 '15 at 09:29
  • But if you don't then eventually you'll get into trouble. Obviously most web applications are bad. Most programmers are bad :) – Sergiu Paraschiv May 25 '15 at 09:32
  • Yup your right...so whats the best solution according to you. If I want to continue with the existing code, which solution will u suggest me. – Channaveer Hakari May 25 '15 at 10:41
  • Do you really need `old_value`? I don't think so. Eliminating that resolves the audit concurrency issue. The concurrency issue regarding two people editing the same thing has multiple solutions. Either put the item in "edit" mode when someone starts editing and disallow anyone else from editing until he "unlocks" it or, when saving, check that the value has not already changed. If it did notify and ask how to proceed - overwrite or discard. You'll probably need transactions for this to work. – Sergiu Paraschiv May 25 '15 at 11:24