Adding code to log changes to tables

[nac]

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.cloud/viewtopic.php?f=13&t=8864) but it did not get me very far unfortunately.

Thanks,

Neil

[admin]

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.cloud/ ... formObject

https://wiki.nubuilder.cloud/ ... formObject

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

nusubformobject.PNG

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

[nac]

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

[admin]

Neil,

Let me know how you go.

Steven

[nac]

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

[toms]

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);
             }

		}
	}
}

[nac]

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

[toms]

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.

[nac]

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

[admin]

.