Marking and Sorting by Edited Fields

[rwreplog]

I am working on a database driven registration software I created for the school corporation that I work for. It is coded in PHP, and is tied to a MySQL database storing all of the information for the students within the corporation. The program is set up so that each student has a log in account, and once logged in, their demographics and etc. are displayed on the screen. If all the data looks correct they click on 'confirm' and it stores a variable in the database to show that the data was confirmed.

 

However, if a field needs to be changed and updated, it is set up so that all the fields then become editable, and after being changed the user clicks 'save changes' and once again, a variable is stored in the database to show that the user made a change to their data.

 

The administrator account, when logged in, gives the administrator the opportunity to pull up the entire database showing only the students who had made a change to their account, and it displays the demographic information stored within their profile. My question is, is there a way that I can make this table only show the fields that were actually changed, instead of all of the information stored in their account? Would I have to include a timestamp for each individual field, and have the administrator's page call up only fields that have the timestamp marked on them? Is there any scripts that would assist me in this? I have been searching all week, and decided to join a forum to test my luck further!

 

Thanks in advance for any help!

[aggrav8d]

For simplicity's sake let's assume there's only one admin and that the students have a fixed, unchanging number of fields they can edit.  You'll have a table students and then a table students_info, which has id, student_id, updated, and the info.  When updating a student's info your easiest solution is to INSERT a new record instead of an UPDATE.  that way when the admin logs in to check for changes you can say "which student_info have updated since X" and then you can get student_info (where updated<=X) and compare field-by-field to student_info (latest version).  Whatever doesn't match has been updated.

 

Alternately you can store each piece of student_info data as a separate entity (id, student_id, field_id, field_value, updated) and then scan for all student_info updated since time X.  In this second method you can use either the UPDATE or INSERT methods.  If you use UPDATE you won't be able to roll back a user's changes but you also run less risk of causing your student_info_id to roll over.