HOW TO compare data before and after update for changes

Talk and info about common issues

Moderator: alexandrleonenko

administrator
Site Admin
Site Admin
Posts: 6004
Joined: Jan 7th, '09, 23:18
Contact:

HOW TO compare data before and after update for changes

Postby administrator » Oct 5th, '10, 12:32

To compare data before and after update, you have to store it before updating in some array and then compare it with the new data after the updating.

HINT: remember to use
for PDO: $dSet->fetch() and $dSet->columnCount()
for PEAR: $dSet->fetchRow() and $dSet->numCols()

Step 1. We save old data. (put this code after $dgrid->DataSource() method)

Code: Select all

if($mode == "update" && $rid != "-1"){
   $row_before_array = array();
   $dSet = $dgrid->ExecuteSQL("SELECT * FROM ven_sku WHERE id=".$rid);
   if($row = $dSet->fetchRow()){
     foreach($row as $key => $val){
        $row_before_array[$key] = $val;
     }
   }
   $dgrid->DataSource('PDO', 'mysql', $DB_HOST, $DB_NAME, $DB_USER, $DB_PASS, $sql, $default_order);
}



Step 2. We compare saved data with new data (put this code after $dgrid->Bind() method).

Code: Select all

if($mode == "update" && $rid != "-1"){

    $Title = "Record #".$rid." was updated";
    $message = "Updated fields and values:<br>";
    $message .= "Time: ".date("Y-m-d H:i")."<br><br>";

    $dSet = $dgrid->ExecuteSQL("SELECT * FROM ven_sku WHERE id=".$rid);
    if($row = $dSet->fetchRow()){
   foreach($row as $key => $val){
      if($row_before_array[$key] != $val){
         $message .= "<b>".$em_columns[$key]['header']."</b>:<br>---------------<br>";
         $message .= "Old Value: ".$row_before_array[$key]."<br>";
         $message .= "New Value: ".$val."<br><br>";
      }
      if($key == "VEN_SKU") $req_VEN_SKU = $val;
   }
    }
   
    $Title = ": ". $vendorname." :: ".$req_VEN_SKU." :: UPDATED :: ".date("Y-m-d H:i");
    $msg = new Email($Recipiant, $Sender, $Title);
}         

administrator
Site Admin
Site Admin
Posts: 6004
Joined: Jan 7th, '09, 23:18
Contact:

Re: HOW TO compare data before and after update for changes

Postby administrator » Apr 24th, '12, 07:24

CORRECTION (provided by kasumovic)

First of all, $em_columns can only be accessed with field_name and not an index ($em_columns ['customer_id'] , and not $em_columns[0])

Step 1. We save old data with real field names !!!. (put this code after $dgrid->DataSource() method)

Code: Select all

if($r_mode == "update" && $r_rid != "-1"){
     $row_before_array = array();
     // Get column names from the needed table
     $dSet2 = $dgrid->ExecuteSQL("select column_name from information_schema.columns where table_name='customer'");
     $dSet = $dgrid->ExecuteSQL("SELECT * FROM customer WHERE customer_id =".$r_rid);
     $row  = $dSet->fetchRow() ;
     $row_col = -1 ;
     while ($field = $dSet2->fetchRow()){
          $row_col++ ;
          // $row_before_array[ $row_col] [0] holds real field name from the value
          // $row_before_array[ $row_col] [1] holds the value from this field
          $row_before_array [$row_col] = array($field[0],$row[$row_col]) ;
     } ;
}


Step 2. We compare saved data with new data, i have add TRIM to avoid spaces at end of field value. I have adapt the code with the changes in step 1. (put this code after $dgrid->Bind() method).

Code: Select all

if($r_mode == "update" && $r_rid != "-1"){
     $message = "<u>There are found following changes :</u><br>";
     $dSet = $dgrid->ExecuteSQL("SELECT * FROM customer WHERE customer_id=".$r_rid);
     if($row = $dSet->fetchRow()){
          foreach($row as $key => $val){
               if(trim($row_before_array[$key][1]) != trim($val)){
                    $message .= "<b>".$em_columns[$row_before_array[$key][0]]['header']."</b>: \"".$row_before_array[$key][1]."\" ==> \"".$val."\"<br>";
               }
          }
     }
}


Return to “ApPHP DataGrid {HOW TO}”