Adding code to log changes to tables

Questions related to customising nuBuilder Forte with Javascript or PHP.

Adding code to log changes to tables

Postby nac » Fri Mar 16, 2018 12:33 am

I would like to implement a simple audit feature to track changes to data tables. There are many examples of this approach, in which an 'audit log' table is used to record the table name, field name, primary key value, the date, the user name and the old value. Many of these examples use database (MySQL) level triggers but I would prefer to use nuBuilder as it would provide more control e.g. to record the nuBuilder user ID rather than just $nuConfigDBUser and to allow the selection of which fields to log. I can see that some code in BS 'Before Save' would achieve the desired result. However, for this to work, I assume I would need an array that has, for each data field that will be updated when the 'Save' button is clicked,:
  • the table name
  • the field name
  • the primary key expression
With this it should be possible to build some statements to capture the current values before they are modified by the nuBuilder Save action. (I am assuming that the Save action only updates the fields that have been changed.)

So my questions are: How do I get this array? Is this even a valid approach? Has anyone implemented anything similar to this?
I did find that the matter was discussed in the version 3 forum (https://forums.nubuilder.com/viewtopic.php?f=13&t=8864) but it did not get me very far unfortunately.

Thanks,

Neil
nac
 
Posts: 28
Joined: Wed Dec 13, 2017 7:58 am

Re: Adding code to log changes to tables

Postby admin » Fri Mar 16, 2018 3:07 am

Neil,

Good question.

You can get all the information you need (except for the original field value) from the functions, nuSubformObject().

The same function name is used in both PHP and Javascript.

https://wiki.nubuilder.net/nubuilderforte/index.php/PHP#nuSubformObject

https://wiki.nubuilder.net/nubuilderforte/index.php/Javascript#nuSubformObject

Here is an instance of the Javascript function being run in the browser's console - the red dots show the field I edited.

edited_form.PNG
edited_form.PNG (7.41 KiB) Viewed 434 times


nusubformobject.PNG
nusubformobject.PNG (35.08 KiB) Viewed 434 times


By passing an empty string you will get the main Edit Form as a Subform object with just one row.


You can the do something like this in Before Save...

Code: Select all

$o = nuSubformObject('');

for($i = 0 ; $i < count($o->rows) ; $i++){
   
    $p = $o->rows[$i][0];
    $s = "SELECT * FROM $o->table WHERE $o->primary_key = '$p'";
    $t = nuRunQuery($s);
    $r = db_fetch_array($t);
   
    for($I = 1 ; $I < count($o->rows[$i]) ; $I++){
       
        $N = nuID();
        $T = $o->table;
        $F = $o->fields[$I];
        $P = $p;
        $B = $r[$o->fields[$I]];
        $A = $o->rows[$i][$I];
        $S = "INSERT INTO my_log_table (my_log_table_id, log_table, log_field, log_pk, log_before, log_after) VALUES ('$N', '$T', '$F', '$P', '$B', '$A')";

        nuRunQuery($S);

    }

}





Steven
admin
Site Admin
 
Posts: 2842
Joined: Mon Jun 15, 2009 9:53 am

Re: Adding code to log changes to tables

Postby nac » Fri Mar 16, 2018 5:47 am

Steven,

Thank you very much for the prompt and extremely helpful response. I will certainly have a go at using this . It will probably be a few days due to other work pressure and as nuBuilder is my first foray into PHP. But in the meantime, thanks once again.

Neil
nac
 
Posts: 28
Joined: Wed Dec 13, 2017 7:58 am

Re: Adding code to log changes to tables

Postby admin » Fri Mar 16, 2018 8:26 am

Neil,

Let me know how you go.

Steven
admin
Site Admin
 
Posts: 2842
Joined: Mon Jun 15, 2009 9:53 am

Re: Adding code to log changes to tables

Postby nac » Fri Mar 16, 2018 6:56 pm

Steven,

The code you suggested looked so close to the final product that I could not resist having a go. I only had to add two extra features: first to select only the fields that had changed and secondly to filter out the objects on the form that are not fields in the table. The result is a piece of code that seems to do exactly what I require. Here is the code so far ..

Code: Select all
// the changelog table definition
CREATE TABLE `changelog` (
  `changelog_id` varchar(25) NOT NULL,
  `tablename` varchar(100) DEFAULT NULL,
  `fieldname` varchar(100) DEFAULT NULL,
  `pkvalue` varchar(25) DEFAULT NULL,
  `changedate` timestamp DEFAULT CURRENT_TIMESTAMP,
  `userid` varchar(25) DEFAULT NULL,
  `oldvalue` text,
  `newvalue` text,
  PRIMARY KEY (`changelog_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

// PHP code for 'Before Save'
$o = nuSubformObject('');

for ($i = 0; $i < count($o->rows); $i++) {
  $p = $o->rows[$i][0];
  $s = "SELECT * FROM $o->table WHERE $o->primary_key = '$p'";
  $t = nuRunQuery($s);
  $r = db_fetch_array($t);
  $Flds = db_field_names($o->table);
  for ($I = 1; $I < count($o->rows[$i]); $I++) {
    $ID = nuID();
    $Tbl = $o->table;
    $Fld = $o->fields[$I];
    $PK = $p;
    $Usr = "#USER_ID#";
    $OV = $r[$o->fields[$I]];
    $NV = $o->rows[$i][$I];
    if ($OV != $NV && in_array($Fld, $Flds)) {
      $S = "INSERT INTO changelog (changelog_id, tablename, fieldname, pkvalue, userid, oldvalue, newvalue)"
            ."VALUES ('$ID', '$Tbl', '$Fld', '$PK',  '$Usr', '$OV', '$NV')";
      nuRunQuery($S);
      }
    }
  }


The code is working exactly as I expected and so, once again, thanks for the all the great help on this.

Neil
nac
 
Posts: 28
Joined: Wed Dec 13, 2017 7:58 am

Re: Adding code to log changes to tables

Postby toms » Fri Mar 16, 2018 7:37 pm

Neil,

Thanks for sharing your modified version. I had also modified the code a bit so that you can log deleted records as well.
Either you call insertLog() in the "Before Save"-event with insertLog("","update");
or in the "Before-Delete"-event with insertLog("","delete"). In the latter case, all "log_after"-values are set to NULL and the additional column "log_type" will contain "delete"

Feel free to use parts of it...


Code: Select all
function insertLog($form, $type) {
   // $form: if empty: main form, otherwise subform
   // $type: update, delete
   $o = nuSubformObject($form);
   $u = "#USER_ID#";

   for($i = 0 ; $i < count($o->rows) ; $i++){
      
      $p = $o->rows[$i][0];
      $s = "SELECT * FROM $o->table WHERE $o->primary_key = '$p'";
      $t = nuRunQuery($s);
      $r = db_fetch_array($t);
      
      for($I = 1 ; $I < count($o->rows[$i]) ; $I++){
         
         $N = nuID();
         $T = $o->table;
         $F = $o->fields[$I];
         $P = $p;
         $U = $u;
         $B = $r[$o->fields[$I]];
         $A = $o->rows[$i][$I];
         $Y = $type;

            if ($A !== $B) {
         $S = "INSERT INTO my_log_table (my_log_table_id, log_type, log_table, log_field, log_pk, log_before, log_after, log_user_id)
              VALUES ('$N', '$Y', '$T', '$F', '$P', '$B',". (($Y=='delete')?"NULL":("'".$A."'")).", '$U')";   
   
              nuRunQuery($S);
             }

      }
   }
}
toms
 
Posts: 780
Joined: Wed Nov 08, 2017 8:57 pm

Re: Adding code to log changes to tables

Postby nac » Fri Mar 16, 2018 8:03 pm

toms,

It does make sense to include record deletions and it had crossed my mind to look into that at some point. My immediate requirement was to log the edits as I have a few people editing a table but they cannot delete records. Thanks for your code. This is certainly an interesting way to learn PHP. I guess it could also be improved by enumerating and iterating through all the subforms as well so that all changes to multiple tables are logged. Maybe when I have time...

I did find that the in_array($Fld, $Flds) was quite important as without it I was getting 'Display' objects in the changelog table. Of course the contents of $Flds could also be changed to include only the fields to be monitored.

Neil
nac
 
Posts: 28
Joined: Wed Dec 13, 2017 7:58 am

Re: Adding code to log changes to tables

Postby toms » Fri Mar 16, 2018 8:54 pm

nac wrote:I did find that the in_array($Fld, $Flds) was quite important as without it I was getting 'Display' objects in the changelog table.


You're making a valid point. This is certainly useful.
toms
 
Posts: 780
Joined: Wed Nov 08, 2017 8:57 pm

Re: Adding code to log changes to tables

Postby nac » Fri Mar 16, 2018 9:18 pm

toms,

You are very welcome. I might add that I have found many of your contributions to this forum very helpful as I learn nuBuilder. So thank you.

Neil
nac
 
Posts: 28
Joined: Wed Dec 13, 2017 7:58 am

Re: Adding code to log changes to tables

Postby admin » Sat Mar 17, 2018 3:21 am

.
admin
Site Admin
 
Posts: 2842
Joined: Mon Jun 15, 2009 9:53 am


Return to Custom Code