[Ed Colon]

Hi,

wonder if there´s a efficient way to save grid data to a pm table.

Scenario:
1. You have a trigger that recovers information from a table and put it into a grid so that info will be displayed.
2. Now you want to modify existant rows, add new ones and delete some of them.
3. You want to save all info (updated ones and new ones) and discard in database the one that you deleted.

Thanks!

[ronrich]

Hello,

this will help you to update your registry
imagine a pm table with fields(ID, NAME, LAST_NAME, EMAIL)
the grid fields are named the same (ID, NAME, LAST_NAME, EMAIL)

Code: Select all

foreach (@=your_Grid as $row) {
   $query = "UPDATE PMT_YOUR_TABLE SET NAME='{$row['NAME']}', LAST_NAME='{$row['LAST_NAME']}', EMAIL='{$row['EMAIL']}' WHERE ID='{$row['ID']}'";
   executeQuery($query);
}

you can use the same to insert new fields

Code: Select all

foreach (@=your_Grid as $row) {
   $query = "INSERT INTO PMT_YOUR_TABLE (NAME, LAST_NAME, EMAIL) VALUE ({$row['NAME']},{$row['LAST_NAME']},{$row['EMAIL']})";
   executeQuery($query);
}

but you will have to use an IF sentence in every row to compare an check if the registry already exists and to decide if it will update or insert a new one

I hope it helps

[Ed Colon]

It helps.

Thanks.

[donelson07]

Quick question around this solution.

If you leave the Update instruction as it is, then you will update all existing rows into the Grid and not just the ones that have changes and that could cost a lot in terms of efficiency (Say for example you have 100 rows and only 5 have changes).

Any suggestion around identifying the rows that have been changed?

Thanks in advance,
donelson07

[amosbatto]

donelson07,
First of all, the above code won't work if the user is allowed to delete rows in the grid, nor will it work if the user is adding rows or the value of the ID field is autogenerated by MySQL. It also doesn't maintain the order of the rows in the grid.

Here is the trigger code that you should use to write grid to a table:

Code: Select all

$dbConnection = 'workflow'; //set to ID of the DB Connection if using an external DB
$tableName = 'PMT_YOUR_TABLE'; //set to table name

// first clear the entire table:
executeQuery("DELETE FROM $tableName", $dbConnection);
//then write grid to the table:
foreach (@=yourGrid as $row) {
   $id = $row['ID']; //assuming that the ID is an integer.  
   $firstName = mysql_real_escape_string($row['FIRST_NAME']);
   $lastName = mysql_real_escape_string($row['LAST_NAME']);  
   $email       = mysql_real_escape_string($row['EMAIL']);
   
   //if a new row and MySQL should autogenerate the ID field:
   if (empty($id) and $id !== '0' and $id !== 0) {        
      $sql = "INSERT INTO $tableName (FIRST_NAME, LAST_NAME, EMAIL) ".
         "VALUES ('$firstName', '$lastName', '$email')"; 
   }
   else {
      $sql = "INSERT INTO $tableName (ID, FIRST_NAME, LAST_NAME, EMAIL) ".
         "VALUES ($id, '$firstName', '$lastName', '$email')"; 
   }
   @@result = executeQuery($sql,  $dbConnection); 
}

If you only have a 100 rows in your table, then you probably should worry about it, because MySQL is fast enough. If you have thousands, then it might be an issue, but there is no way to maintain the same order of rows in the grid as in the table. Is this important to you?

If you are really ordered about having to rewrite the entire table, then here is an alternative. What you can do is create a trigger before the DynaForm with the grid like this to create a copy of the grid, with keys based on the ID field:

Code: Select all

@=OriginalGridById = array();
foreach (@=yourGrid as $row) {
   //assuming that the ID field is unique.
   @=OriginalGridById [ $row['ID'] = $row;
}  

Then, create a second trigger after the DynaForm to compare the original grid with the updated grid and write any changes to the DB:

Code: Select all

$dbConnection = 'workflow'; //set to ID of the DB Connection if using an external DB
$tableName = 'PMT_YOUR_TABLE'; //set to table name

@=OriginalGridById = array();
foreach (@=yourGrid as $row) {
   //assuming that the ID field is unique.
   @=OriginalGridById [ $row['ID'] = $row;
}  

for ($i = 1; $i <= count(@=yourGrid); $i++) {
   $row = @=yourGrid[$i];
   $id = $row['ID'];
   //if an existing row
   if (key_exists($id, @=OriginalGridById)) {
       //if the row has been changed, then update it in the database:
       if (@=OriginalGridById[$id] != $row) {
          $firstName = mysql_real_escape_string($row['FIRST_NAME']);
          $lastName =  mysql_real_escape_string($row['LAST_NAME']);  
          $email = mysql_real_escape_string($row['EMAIL']);   
          $sql = "UPDATE $tableName SET FIRST_NAME='$firstName, LAST_NAME='$lastName', EMAIL='$email' "; 
         @@result = executeQuery($query, $dbConnection);
      }
   }
   else { //if a new row was added to the grid:
      $firstName = mysql_real_escape_string($row['FIRST_NAME']);
      $lastName = mysql_real_escape_string($row['LAST_NAME']);  
      $email       = mysql_real_escape_string($row['EMAIL']);
   
      //if ID field is empty and MySQL should autogenerate it:
      if (empty($id) and $id !== '0' and $id !== 0) {        
         $sql = "INSERT INTO $tableName (FIRST_NAME, LAST_NAME, EMAIL) ".
            "VALUES ('$firstName', '$lastName', '$email')"; 
      }
      else {
         $sql = "INSERT INTO $tableName (ID, FIRST_NAME, LAST_NAME, EMAIL) ".
            "VALUES ($id, '$firstName', '$lastName', '$email')"; 
      }
     @@result = executeQuery($sql,  $dbConnection); 
   }
}

The problem with this code is that it doesn't handle the situation if the user can delete rows in the grid. Then you have no idea that the row was deleted, so you don't know to delete that record in the database. The solution if you don't want to delete the entire table and rewrite it, is to add javascript to the onDeleteRow event to record the IDs of the deleted rows and store them in a hidden field. Then add code to your trigger to delete those rows.