Multiple UPDATE from a dynamic field

Question

I’m trying to catch the ids that come by user-selected checkbox (array), and through these ids, update the table, with the following code:

$ocs_imp = $_POST['check_imprime']; 
$contar = count($ocs_imp);
$id = implode(',',$ocs_imp); 

   $inicio = 0;
   $ultimo = $contar-1;
   $arr = range($inicio, $ultimo);

$sqli = "";
     foreach ( $arr as $k => $v ) {
$sqli .= "UPDATE oc_cadastro SET imprime_oc='s', imprime_oc_data='".$pega_data."' WHERE id = '".$ocs_imp[$v]."')))";
                                  } 

                            $mysqli->query($sqli);  

However not working, no error, simply does not update the records, also tried with WHERE id IN ($id), unsuccessful.

Answer 1

The problem in the code is concatenating the Sqls into the $sqli variable and running all querys together.

Each query should be executed alone this way I suggest:

foreach ( $arr as $k => $v ) {
    $sqli = "UPDATE oc_cadastro SET imprime_oc='s', imprime_oc_data='".$pega_data."' WHERE id = '".$ocs_imp[$v]."'";
    $mysqli->query($sqli); 
 } 

I hope you helped him.

Answer 2

Have some relatives about in your update:

"UPDATE oc_cadastro SET 
    imprime_oc='s',
    imprime_oc_data='".$pega_data."'
WHERE id = '".$ocs_imp[$v]."')))";
                             ^^^
--------------------------------

Had previously suggested the use of multi_query() which executes several sql statements separated by semicolon(;) but this method does not seem suitable for INSERT/DELETE/UPDATE because it checks only if the first instruction is correct and returns true even if the others have errors or is it performs all instructions until find the first wrong and to and returns true as if everything is right, with that records are inserted/updated/removed by half.

To process multiple INSERTS at once use multiple values in clause VALUES ex:

INSERT INTO tabela (c1, c2, c3) VALUES ('v1', 'v2', 'v3'), ('v12', 'v22', 'v32')

In the case of DELETE just pass the ids in the client IN ex:

DELETE FROM tabela WHERE id IN (1,2,3,4,5)

In the case of UPDATE to make sure that all records are modified turn off the auto commit, this means that the source code and not the database is responsible for effecting the rollback transaction for failure and commit for success. Run the query using query() inside the foreach.

Options - sprintf

$mysqli->autocommit(false);
$sqli = "";
$erro = false;
foreach ($arr as $k => $v) {
   $sqli = sprintf("UPDATE oc_cadastro SET
                        imprime_oc = '%s',
                        imprime_oc_data = '%s'
                    WHERE id = %d;", 's', $pega_data, $ocs_imp[$v]);

   if(!$mysqli->query($sql)){
      $erro = true;
      echo 'Atualização cancelada: <br>'. $mysqli->errno .' - '. $mysqli->error;
      break;
  }
}

if($erro == true)
   $mysqli->rollback();
else
   $mysqli->commit();

A remark:

In the excerpt from the question $k(contains the contents of the array) is not used at all, so you can simplify the code a little and remove it because $item shall have the same value as $v.

foreach($arr as $item){
   " ... WHERE id = %d;", 's', $pega_data, $ocs_imp[$item]);

Answer 3

Whereas the return of your $_POST['check_imprime'] is already an array:

$ocs_imp = $_POST['check_imprime'];
$sql = "";

foreach ($ocs_imp as $id => $val) {
   $sql .= "UPDATE .. WHERE id = {$val};";
}

$mysqli->query($sql);