How to save data from a changed grid?

Question

My system has a grid with order items, coming from the dice.

Well, I need to change the requested items, add one more item or even remove. You get the information in json and I’ll put it on the grid. I can interact in the JsonStore, which is an array of items, I remove and add normally, but this locally.

Well, after editing the budget items, I need to ennviate to the database the editions made.

Right now I need help.

There is a modelling pattern in sql to perform this procedure?

What’s the best way? I delete everything in the database related to budget and enter again? I update the items?

If you need more clarity on the question please let me know.

Answer 1

The solution I’m going to propose is not based on your language (php) but on your problem.

From your printscreen, I understood that 1 Request has N Itempedidodeproduct (which contains the product code, the quantity ...) which contains 1 reference for the Product. We are talking here of 1 -> N -> 1

Every time you send the data to the server, you must send and recover the id of the Request, and from it, recover all the Itempedidodeproduct.

Update of the Itempedidodeproduct

You must use their id that will come from a Hidden field to make the comparison with what is already registered in the bank.

Removal of an Itempedidodeproduct

Compare the Itempedidodeproduct that came from the screen with those from the database, when a certain record does not come from the screen, it is because it was removed, so just make a delete.

Addition of an Itempedidodeproduct

If an Itempedidodeproduct comes from the screen without the id, it means it’s a new record, then you just need to insert it.

Changes to the Request have to be atomic (should be performed in the same transaction) because I believe that for example, you will have to validate if a certain amount of products exist in stock.

Answer 2

Using an auxiliary variable in your favor

Creates a list that defines the behavior that should be done in JSON itself, in each record.

Any previously sent record would have, for example, 0, saying it is a pre-existing unchanged data.

Inserted a new record? Assigns another value, for example, 1. Then the server knows to run one INSERT INTO

Deleted a record? Oops, but the guy might have deleted something that’s not even on the server yet... so basically

se(action = 0)entao
  definir action deste registro 2, para executar DELETE do lado do servidor
senao
  só apaga do JSON, não envia pro servidor

Same thing goes for an issue. If the person edits a record that has not yet been entered into the server you will keep the 1, but will update the data within the JSON. If the record existed previously there you define a 3, so that it is executed a UPDATE at the bank.

This will keep you from doing 1 SELECT For each record, the client-side effort increases almost nothing, greatly decreases the server-side problem. (You will only have to add a switch to the server part if it is already ready)

If you want to further reduce the server-side problem, you can try filtering JSON and sending everything that is not 0, so that the server is only in charge of changing the database, and not have to keep checking which records were or were not affected.

Answer 3

So, I’ve already made a budget system, and to change individually, you have to make a form for each line, interesting also make an individual Ubmit button for each one too, both to delete and to change. Then you can do what you want with the data of the post, you can try a little harder and do with AJAX, the application is much better, without Reload, if you don’t already use. Ai, from the POST, can change in the bank. Answering your questions, to change in the bank only with UPDATE same, simpler and fast method. You do not need to delete all information, just change the line you want, as I said before.

Answer 4

With the UPDATE, is faster because the data is being rewritten on existing lines. With the DELETE you would have to delete and then insert again and it takes more time, lines of code and performance. Also you can use the instruction of MULTI_QUERY of PHP (http://php.net/manual/en/mysqli.quickstart.multiple-statement.php) with the UPDATE to update all data with a request and further improve performance.

Answer 5

You will need to have little knowledge in jquery/js.

1° Creates 2 array variables.

var deleteID = new Array();
var updateID = new Array();

2° You will create an onchange event in all grid fields, which when changed will insert into the following key array, field = value,

3 ° When you make a change to the product the onchange will send to updateID array with the product ID and the field that is supposedly changed example Quantity:20.

4 ° To delete you will create a delete X button in each row of the table and insert a button with onclick='deletarProduct(iddoproduct)' action or can checkbox; within the function you add so

function deletarProduto(iddoproduto) {
    deleteID.push(iddoproduto);
}

At the end you will have Save/Update button in this action you send the data of the 2 Arrays to the backend. Then the logic will be more or less like this.

<?php
  $acao_deletar = $_POST['deletar'];
  $acao_update  = $_POST['update'];

  // Se existir alguma ação de deletar 
  if(!isset($acao_deletar) {
     $deletar_ids = implode(",",$acao_deletar);
     mysql_query("DELETE FROM tb_produto WHERE ID IN (".$deletar_ids.")");
  }

  // Se existir alguma ação de update 
  if(!isset($acao_update) {
     foreach($acao_update as $update) {
        mysql_query("UPDATE FROM tb_produto SET $update[campo] = $update[valor] WHERE ID = $update[id]");
     }
  }


?>

this code above is just example to open up your mind I hope to have helped.