Save and fetch comments from DB

[Richard]

Hi paramvir.

The project I'm working on is
Since the daily production plan data must be used for other purposes,
the data entered on the screen must be stored in the DB.
I have reviewed the information you gave yesterday and resolved the problematic part (DB save error).

Another problem to be solved
When the comment data of a specific cell is stored in the DB
When the server program queries this and wants to display it on the user's screen
I wonder how to make a string to be sent to the client program.

I cut the back part of the $response data as below
As a result of testing by adding pq_cellattr string, no data was displayed on the screen.

$response =  "{\"data\":".json_encode($products)."}";
$response = substr($response,   0, -3);
$response .= ", pq_cellattr: { qty1: { title: 'This is comment!!'}} }]}";

I'd appreciate it if you could let me know what the problem is.

[paramvir]

Hi Richard

Please follow these steps to display comments from a db table.

Add a column pq_cellattr ( dataType: VARCHAR(1000) ) in the db table.

Suppose one column name is "ProductName" and we want to display comments for this column in first row, then we save the following string value in the pq_cellattr field of the first row in table.

Code: [Select]

{"ProductName":{"title":"Hello"}}

Include pq_cellattr field in the select query.

That's all for the server side.

On the client side, implement dataModel.getData callback to deserialize string values to javascript objects.

Code: [Select]

                getData: function (response) {
                    debugger;
                    response.data.forEach(function(rd){
                        rd.pq_cellattr = JSON.parse(rd.pq_cellattr);
                    })
                    return response;
                }


[Richard]

Hi paramvir

Thanks to your help, the program is gradually being made into the form I want.
I really appreciate it.

The display of the comment data stored in the DB table on the screen has been resolved,
but the comment data entered on the screen is not stored in the DB, so we will contact you.

Please let me know how I should handle the pq_cellattr data.

The source code for this part is as follows.
Thank you for your review.


$total_day = date('t', strtotime($r['mt_product']));
$query  = "update pp2_month_new set pq_cellattr=?, ";

for($j=1; $j<=$total_day; $j++){
   $query .= "qty$j = ?, ";
}
$query   = substr($query, 0, -2);
$query .= " where id = ?";

$stmt = $pdo->prepare($query);
$result = $stmt->execute(array($r['pq_cellattr'],
   $r['qty1'],  $r['qty2'],  $r['qty3'],  $r['qty4'],  $r['qty5'],  $r['qty6'],  $r['qty7'],  $r['qty8'],  $r['qty9'],  $r['qty10'],
   $r['qty11'], $r['qty12'], $r['qty13'], $r['qty14'], $r['qty15'], $r['qty16'], $r['qty17'], $r['qty18'], $r['qty19'], $r['qty20'],
   $r['qty21'], $r['qty22'], $r['qty23'], $r['qty24'], $r['qty25'], $r['qty26'], $r['qty27'], $r['qty28'], $r['qty29'], $r['qty30'], $r['qty31'],
   $r['id']));
if($result == false) {
   throw new Exception(print_r($stmt->errorInfo(), 1).PHP_EOL.$query);
}

[paramvir]

Please follow these steps to post comments and styles from pqgrid to server.

In save button listener:

Code: [Select]

                    listener: function () {
                       
                        var arr = this.pageData().map(function(rd){
                            return {
                                id: rd.id, //id of the record.
                                pq_cellattr: JSON.stringify( rd.pq_cellattr),
                                pq_cellstyle: JSON.stringify( rd.pq_cellstyle )
                            }
                        });

                        //post comments array to server.
                        $.ajax({
                            url: URL,
                            data: {pq_meta: arr}
                        })
                    }

In server side code get the array from $_GET['pq_meta']

Iterate through the array and write update sql query to update pq_cellattr and pq_cellstyle fields corresponding to matching id of the records in db table.

[Richard]

Hi


As the attached file referring to the information you provided
the client program has been modified, but the comment data is not saved in the DB.
I'd appreciate it if you could tell me what the reason is.


The part that saves comments in the server-side php program is as follows.


$total_day = date('t', strtotime($r['mt_product']));
$query  = "update pp2_month_new set pq_cellattr=?, ";


for($j=1; $j<=$total_day; $j++){
   $query .= "qty$j = ?, ";
}
$query   = substr($query,   0, -2);


$query .= " where id = ?";


$pq_meta = $_GET['pq_meta'];


$stmt = $pdo->prepare($query);
$result = $stmt->execute(array($pq_meta,
   $r['qty1'],  $r['qty2'],  $r['qty3'],  $r['qty4'],  $r['qty5'],  $r['qty6'],  $r['qty7'],  $r['qty8'],  $r['qty9'],  $r['qty10'],
   $r['qty11'], $r['qty12'], $r['qty13'], $r['qty14'], $r['qty15'], $r['qty16'], $r['qty17'], $r['qty18'], $r['qty19'], $r['qty20'],
   $r['qty21'], $r['qty22'], $r['qty23'], $r['qty24'], $r['qty25'], $r['qty26'], $r['qty27'], $r['qty28'], $r['qty29'], $r['qty30'], $r['qty31'],
   $r['id']));
if($result == false) {
   throw new Exception(print_r($stmt->errorInfo(), 1).PHP_EOL.$query);
}

[paramvir]

pq_meta is posted independently of the data changes in the grid.

So it's handled with a separate query / queries.

Code: [Select]

$pq_meta = $_GET['pq_meta'];

foreach ($pq_meta as $meta)
{
    $query = "update table set pq_cellattr = ? where id = ?";
    //where id is primary key / unique field of the table.
   //get id and pq_cellattr from $meta and execute the query.
}


[Richard]

Hi

The following is what you have been informed so far:
I made a client program and a server program.
Comment data is not updated.

I would really appreciate it if you could give me a solution.

1) Client program
{
   type: 'button',
   icon: 'ui-icon-disk',
   label: 'Save comments',
   cls: 'changes',

   listener: function () {
      var arr = this.pageData().map(function(rd){
         return {
            id: rd.id, //id of the record.
            pq_cellattr: JSON.stringify( rd.pq_cellattr),
            pq_cellstyle: JSON.stringify( rd.pq_cellstyle )
         }
      });

      //post comments array to server.
      $.ajax({
         url: 'product_plan_save.php',
         data: {pq_meta: arr},
      })
   },
   options: { disabled: true },
},



2) Server side program (product_plan_save.php)
function updateSingle($pdo, $r)
{
   // 1) Update merged field.
   if($r['cd_kind'] == 0){
      $query  = "update pp2_month_new set qty_worker=?, eff_product=?, cycle_time=?, qty_not_ot=?, qty_ot=? ";
      $query .= "where cd_group = ?";
      $stmt = $pdo->prepare($query);
      $result = $stmt->execute(array($r['qty_worker'], $r['eff_product'], $r['cycle_time'], $r['qty_not_ot'], $r['qty_ot'], $r['cd_group']));
      if($result == false) {
         throw new Exception(print_r($stmt->errorInfo(), 1).PHP_EOL.$query);
      }
   }

   // 2) Update data of Day, Night, Shipment, Etc, Stock
   $total_day = date('t', strtotime($r['mt_product']));
   $query  = "update pp2_month_new set ";

   for($j=1; $j<=$total_day; $j++){
      $query .= "qty$j = ?, ";
   }
   $query = substr($query,   0, -2);

   $query .= " where id = ?";

   $stmt = $pdo->prepare($query);
   $result = $stmt->execute(array(
      $r['qty1'],  $r['qty2'],  $r['qty3'],  $r['qty4'],  $r['qty5'],  $r['qty6'],  $r['qty7'],  $r['qty8'],  $r['qty9'],  $r['qty10'],
      $r['qty11'], $r['qty12'], $r['qty13'], $r['qty14'], $r['qty15'], $r['qty16'], $r['qty17'], $r['qty18'], $r['qty19'], $r['qty20'],
      $r['qty21'], $r['qty22'], $r['qty23'], $r['qty24'], $r['qty25'], $r['qty26'], $r['qty27'], $r['qty28'], $r['qty29'], $r['qty30'], $r['qty31'],
      $r['id']));
   if($result == false) {
      throw new Exception(print_r($stmt->errorInfo(), 1).PHP_EOL.$query);
   }

   // 3) Update comments
   $pq_meta = $_GET['pq_meta'];

   foreach ($pq_meta as $meta){
      $query = "update pp2_month_new set pq_cellattr = ? where id = ?";

      //where id is primary key / unique field of the table.
      //get id and pq_cellattr from $meta and execute the query.

      $stmt = $pdo->prepare($query);
      $result = $stmt->execute($meta, $r['id']));
      if($result == false) {
         throw new Exception(print_r($stmt->errorInfo(), 1).PHP_EOL.$query);
      }
   }
}

[paramvir]

As mentioned earlier pq_meta is a separate $_GET request, so kindly handle it independently of your previous code.

[Richard]

Hi paramvir

Thanks to your sincere help, the part of saving the comment data in the DB was successful.
I really appreciate it.

I have a problem that needs to be addressed.
As shown in the attached image, there are two buttons.

When saving daily production data, I have to press button B,
and it is inconvenient to have to press button C to save the comment data.

But I made this into one button
When the Save button (B) is clicked,
I want to make the daily production data and daily comment data stored in the DB at the same time.

The program source is as below, but I would appreciate it if you could let me how.

1) Client program

toolbar: {
   cls: 'pq-toolbar-export',
   items: [
   { // 1) Button B
      type: 'button',
      icon: 'ui-icon-disk',
      label: 'Save',
      cls: 'changes',
      listener: saveChanges,
      options: { disabled: true },
   },
   { // 2) Button C
      type: 'button',
      icon: 'ui-icon-disk',
      label: 'Save comments',
      cls: 'changes',

      listener: function () {
         var grid = this;
         var arr = this.pageData().map(function(rd){
            return {
               id: rd.id, //id of the record.
               pq_cellattr: JSON.stringify( rd.pq_cellattr),
               pq_cellstyle: JSON.stringify( rd.pq_cellstyle )
            }
         });

         //post comments array to server.
         $.ajax({
            url: 'product_plan_save.php',
            data: {pq_meta: arr},
            success: function (changes){
               debugger;
               grid.history({ method: 'reset' });
            },
         })
      },
      options: { disabled: true },
   },


2) Server side program

//update single record in db.
function updateSingle($pdo, $r)
{
   // 1) Update merged field
   if($r['cd_kind'] == 0){
      $query  = "update pp2_month_new set qty_worker=?, eff_product=?, cycle_time=?, qty_not_ot=?, qty_ot=? ";
      $query .= "where cd_group = ?";
      $stmt = $pdo->prepare($query);
      $result = $stmt->execute(array($r['qty_worker'], $r['eff_product'], $r['cycle_time'], $r['qty_not_ot'], $r['qty_ot'], $r['cd_group']));
      if($result == false) {
         throw new Exception(print_r($stmt->errorInfo(), 1).PHP_EOL.$query);
      }
   }

   // 2) Update day, night, shipment, etc, stock
   $total_day = date('t', strtotime($r['mt_product']));
   $query  = "update pp2_month_new set ";

   for($j=1; $j<=$total_day; $j++){
      $query .= "qty$j = ?, ";
   }
   $query   = substr($query,   0, -2);
   $query .= " where id = ?";

   $stmt = $pdo->prepare($query);
   $result = $stmt->execute(array(
      $r['qty1'],  $r['qty2'],  $r['qty3'],  $r['qty4'],  $r['qty5'],  $r['qty6'],  $r['qty7'],  $r['qty8'],  $r['qty9'],  $r['qty10'],
      $r['qty11'], $r['qty12'], $r['qty13'], $r['qty14'], $r['qty15'], $r['qty16'], $r['qty17'], $r['qty18'], $r['qty19'], $r['qty20'],
      $r['qty21'], $r['qty22'], $r['qty23'], $r['qty24'], $r['qty25'], $r['qty26'], $r['qty27'], $r['qty28'], $r['qty29'], $r['qty30'], $r['qty31'],
      $r['id']));
   if($result == false) {
      throw new Exception(print_r($stmt->errorInfo(), 1).PHP_EOL.$query);
   }
}

//Update comment data in db.
function updateComments($pdo)
{
   $pq_meta = $_GET['pq_meta'];

   foreach ($pq_meta as $meta){
      $query = "update pp2_month_new set pq_cellattr = ? where id = ?";

      $stmt = $pdo->prepare($query);
      $result = $stmt->execute(array($meta['pq_cellattr'], $meta['id']));
      if($result == false) {
         throw new Exception(print_r($stmt->errorInfo(), 1).PHP_EOL.$query);
      }
   }
}

function updateList($updateList)
{
   $pdo = getDBH();
   foreach ($updateList as $key => $val){
      updateSingle($pdo, $val);
   }
}

if( isset($_GET["pq_update"])){ // Update
   updateSingle(getDBH(), $_GET);
   $response =  "{\"result\": \"success\"}";
}
elseif( isset($_GET["pq_meta"])){ // Update comment data
   updateComments(getDBH());
   $response =  "{\"result\": \"success\"}";
}
elseif( isset($_GET["pq_batch"])){ // Batch
   $dlist = $_POST['list'];
   if(isset($dlist["updateList"])){
      updateList($dlist["updateList"]);
   }
   $response = json_encode($dlist);
}

[paramvir]

js code for both data changes and comments can be called from same button.

First put the comments js code in a separate function i.e., saveComments.

Code: [Select]

function saveComments(){
      var grid = this;
         var arr = this.pageData().map(function(rd){
            return {
               id: rd.id, //id of the record.
               pq_cellattr: JSON.stringify( rd.pq_cellattr),
               pq_cellstyle: JSON.stringify( rd.pq_cellstyle )
            }
         });

         //post comments array to server.
         $.ajax({
            url: 'product_plan_save.php',
            data: {pq_meta: arr},
            success: function (){
               debugger;
               grid.history({ method: 'reset' });
            },
         })
      },
}

Then in success callback of saveChanges function, saveComments can be called.

Code: [Select]

saveComments.call( grid );


[paramvir]

Continued: https://paramquery.com/forum/index.php?topic=4310.0