[ic] Checking DB values aganist new values and recording changes

[Alma Nuker]

Hi list;

I am trying to do following:

I have a page on which you can enter "Request ID" in search box and it
will come back with all fields recorded in db for that prticular
Request ID. Now on this form I can modify detail of request (ie
name,date,time etc). Once when modified values are updated in DB table.
That all works nice.

Next I need to do is to check what has been modifed and if something has
been changed, record what has been changed and when in separate table
called history.

My history table has following fields:

Database  history  history.txt __SQLDSN__
ifdef SQLUSER
Database  history  USER         __SQLUSER__
endif
ifdef SQLPASS
Database  history  PASS         __SQLPASS__
endif
Database  history  KEY           history_id
Database  history  COLUMN_DEF   "history_id=INT(9) UNSIGNED
AUTO_INCREMENT PRIMARY KEY"
Database  history  COLUMN_DEF   "tracking_number=VARCHAR(50)"
Database  history  COLUMN_DEF   "date_of_change=date"
Database  history  COLUMN_DEF   "time_of_change=time"
Database  history  COLUMN_DEF   "type=text"
Database  history  COLUMN_DEF   "old_value=text"
Database  history  COLUMN_DEF   "new_value=text"

tracking_number is actally Request ID (this way I now which request has
been modified)
date and time will be hidden values (current time and date).
old_value (what ever was already ing DB for that request) and new_value
is what ever is it changed to. type should tell me what has been changed
ie, title, summary,description etc.

Do you guys have any ideas how to do this.

Basically what I would like to do is something like this:

Once whaen I have made modification that all changes are automatically
recorded to history table.

This is what my page where you can modify request looks like:

[set mv_data_enable]1[/set]
[tag flag write]supportrequestdb[/tag]
<FORM ACTION="[process]" METHOD="POST">
<INPUT TYPE=HIDDEN NAME="mv_data_table"    VALUE="supportrequestdb">
<INPUT TYPE=HIDDEN NAME="mv_data_key"      VALUE="tracking_number">
<INPUT TYPE=HIDDEN NAME="mv_data_function" VALUE="insert">
<INPUT TYPE=HIDDEN NAME="mv_nextpage"      VALUE=@@MV_PAGE@@>
<INPUT TYPE=hidden NAME="mv_update_empty" VALUE="1">
<INPUT TYPE=hidden NAME="mv_update_empty_key" VALUE="0">
<INPUT TYPE="HIDDEN" NAME="mv_data_fields"
VALUE="tracking_number,username,reporter, etc .....>
<INPUT TYPE="HIDDEN" NAME="mv_data_return_key" VALUE="tracking_number">
[if value tracking_id]
<INPUT TYPE=HIDDEN name="tracking_number" value="[value tracking_id]">
[else]
<INPUT TYPE=HIDDEN name="tracking_number" value="">
[/else]
[/if]
[calc]
        return unless $CGI->{tracking_id};
        $Values->{track_id} = $CGI->{tracking_id}
                unless $CGI->{tracking_id} eq 'NEW';
        for (qw/
                tracking_number
                username
                reporter
               etc ...

   /)
        {
                $Values->{$_} = '';
        }
[/calc]

[loop list=`$Values->{track_id} || 'NEW'`]

Here is one of th eexamples of form field:

  <INPUT TYPE=text NAME=username VALUE="[value name=username
default="[loop-data supportrequestdb username]"]" size=30>

  <INPUT TYPE=text NAME=reporter VALUE="[value name=reporter
default="[loop-data supportrequestdb reporter]"]" size=42>
...................

[set [L]Update[/L]]
mv_nextpage=ord/verify_modify_support_request
mv_todo=set
[/set]

[set [L]Clear[/L]]
tracking_id=NEW
track_id=
mv_todo=return
[/set]


        <INPUT TYPE=submit NAME=mv_click VALUE=[L]Update[/L]>
        <INPUT TYPE=submit NAME=mv_click VALUE=[L]Clear[/L]>


</FORM>
[/loop]

Thanks a lot.

Alma