Duplicate Bulk Records - Run a query for each displayed record

Question

I have a table where only accounts with your type = 'fixed are displayed'

What I would like to do is run a query and duplicate all the table Records at once. At the moment I only managed to duplicate the accounts one by one and not all the displayed results, which already helps but does not solve my problem hehehe.

I am running the following query:

$tSqlDup = $tPdo->prepare("INSERT INTO Contas (Data_Vencimento, Nome, Cedente, Centro_Custo, Finalidade, Valor)SELECT Data_Vencimento, Nome, Cedente, Centro_Custo, Finalidade, Valor FROM tab_contas WHERE ID = '{$tGet}'");

$tSqlDup ->execute();

The idea of this is to remove the user’s work to relaunch accounts that will have exactly equal data, maybe differentiating maturity and value... and when clicking the button all values of the fixed type be duplicated...

I’ll leave the whole HTML structure below:

<table class="table">
    
 <thead>
        
    <tr style="background-color: #428bca;">
                        
        <th>Vencimento</th>

        <th>Nome da Conta</th>

        <th>Cedente</th>

        <th>Finalidade</th>

        <th>CC</th>

        <th>Valor da Ultima Conta</th>

        <th>Duplicar</th>

    </tr>

</thead>
      
<tbody>

<?php
 
require_once("includes/BaseDeDados.php");
require_once("includes/LogSistema.php");
           
    $tQuantidade = 100;
    $tPagina     = (isset ($_GET['pg'])) ? (int)$_GET['pg'] :1;
    $tIniciar    = ($tQuantidade * $tPagina) - $tQuantidade;

    $tSql = $tPdo->prepare("SELECT * FROM Contas WHERE Tipo='F' ORDER BY ID DESC LIMIT $tIniciar, $tQuantidade");
$tSql ->execute();
      
      while($tMC  = $tSql->fetch(PDO::FETCH_ASSOC))
      {   

      //Faz o explode da Data para transformar data do formato US para o formato BR
      $data = $tMC['Data_Vencimento'];
      $data_nova = explode("-",$data);
      // Calcula Mês atual + 1
      $mes = strtotime("+1 month");

      echo "
        <tr>
            
          <td>$data_nova[2]/".date('m', $mes)."/$data_nova[0]</td>

          <td><a href='?controle=***ARQUIVO***&CodC=".codificarUrl($tMC['ID'])."' rel='tooltip' title='Editar Esta Conta'>".utf8_encode($tMC['Nome'])."</td>

          <td>{$tMC['Cedente']}</td>

          <td>{$tMC['Finalidade']}</td>

          <td>{$tMC['Centro_Custo']}</td>

          <td>R$ {$tMC['Valor']}</td>
          
<!--- ////////////////////////////
ESTE E O BOTAO QUE RODA A CONSULTA PARA DUPLICAR A CONTA COM O ID ESCOLHIDO, POREM GOSTARIA DE AO INVES TER DE FAZER ISTO DE CONTA EM CONTA, REALIZAR EM TODAS DE UMA VEZ...
/////////////////////////////////-->
          <td><a href='?controle=***ARQUIVO***&Acao=Duplicar&CodC=".codificarUrl($tMC["ID"])."' role='button' rel='tooltip' title='Duplicar este Arquivo'></a></td>
          
        </tr>";

      }
        
  if (isset($_GET['Acao']) && isset($_GET['CodC']))
    {

# É AQUI AONDE É RODADA A CONSULTA QUE FAZ A DUPLICAÇÃO DA CONTA

      $tGet    = decodificarUrl($_GET['CodC']);
      $tSqlDup = $tPdo->prepare("INSERT INTO Contas (Data_Vencimento, Nome, Cedente, Centro_Custo, Finalidade, Valor) SELECT Data_Vencimento, Nome, Cedente, Centro_Custo, Finalidade, Valor FROM tab_contas WHERE ID = '{$tGet}'");
     
      $tSqlDup ->execute();       
    
    }

?>

<tbody>
    
</table>

PS: In the SS there are the buttons to the right of the table, and the button above the table, the one above the table does not work, would be the button that would duplicate everything at once, while the buttons to the right of each Account are the buttons that duplicate one at a time...

Answer 1

You can continue using your query that does an Insert at a time, just by placing it inside a loop that goes through all items.

$query_itens = $tPdo->prepare("SELECT ID FROM tab_contas WHERE SUA_CONDICAO_AQUI")->execute();
$itens = $query_itens->fetchAll();

$tSqlDup = $tPdo->prepare("INSERT INTO Contas (Data_Vencimento, Nome, Cedente, Centro_Custo, Finalidade, Valor)SELECT Data_Vencimento, Nome, Cedente, Centro_Custo, Finalidade, Valor FROM tab_contas WHERE ID = ?");

foreach($itens as $item) {
    $tSqlDup->execute([$item->id]);
}

Just change the condition of the first query to match your case.