Record communication data of the person

Question

I’m trying to record in my BD communication data of a person and I am not able to solve, the data of this communication are Email, Telefone Empresa, Celular Empresa and Ramal, each of these are Types registered in a call table cadTipoComunicacao and later I try to record this data in a table calls cadCommunicacao.

I have the information available:

    // TIPOS DE COMUNICAÇÃO
    $IdTipoRamal = 1;   
    $IdTipoTe = 3;
    $IdTipoCe = 4;
    $IdTipoEmail = 6;

    // INFORMAÇÃO
    $Ramal = 1820;  
    $TelEmpresa = '(44) 3733-8810';
    $CelEmpresa = '(44) 99898-8585';    
    $Email = '[email protected]';

And following structure for the agrupar the information:

    $Registros = array (
      array($IdTipoRamal,$Ramal),     
      array($IdTipoTe, $TelEmpresa),
      array($IdTipoCe, $CelEmpresa),
      array($IdTipoEmail, $Email)
    );

What am I doing to display the information:

for ($row = 0; $row < 4; $row++) {   
  echo "<ul>";
  for ($col = 0; $col < 2; $col++) {
    echo "<li>".$Registros[$row][$col]."</li>";
  }
  echo "</ul>";
}

The output of that code is this:

The insert code of the attempt I made is this:

// DADOS DA COMUNICAÇÃO
$Registros = array (
  array($IdTipoRamal,$Ramal),     
  array($IdTipoTe, $TelEmpresa),
  array($IdTipoCe, $CelEmpresa),
  array($IdTipoEmail, $Email)
);

// INSERIR  
for ($row = 0; $row < 4; $row++) {     
  // echo "<ul>";
  for ($col = 0; $col < 2; $col++) {
    // echo "<li>".$Registros[$row][$col]."</li>";

    $crud = $pdo->prepare("INSERT INTO cadComunicacao ( IdPessoa, IdTipo, Informacao ) VALUES (?, ?, ?)");
    $crud->bindParam(1, $IdPessoa , PDO::PARAM_INT);
    $crud->bindParam(2, $IdTipo , PDO::PARAM_INT);
    $crud->bindParam(3, $Informacao , PDO::PARAM_STR);
    $retorno = $crud->execute();           

  }
  // echo "</ul>";
} 

What I am not able to do is go through the loop and record Idtipo and Informacao, and in the image the Idtipo is the first value and the Information is the second.

I can’t record everything in the field Informacao, I need to go through the loop and record the IdPessoa, IdTipo and the `Information, which in the above case may be several lines for Idtype and Information.

The table where I am trying to record the information has this structure:

Answer 1

I think it made a big mess in the way you pull the data to insert into the table, can simplify using foreach, see how:

$IdPessoa = 1;
$IdTipoRamal = 1;   
$IdTipoTe = 3;
$IdTipoCe = 4;
$IdTipoEmail = 6;
$Ramal = 1820;  
$TelEmpresa = '(44) 3733-8810';
$CelEmpresa = '(44) 99898-8585';    
$Email = '[email protected]';
$Registros = array (
    array($IdTipoRamal,$Ramal),     
    array($IdTipoTe, $TelEmpresa),
    array($IdTipoCe, $CelEmpresa),
    array($IdTipoEmail, $Email)
);
foreach($Registros as &$value){
    $crud = $pdo->prepare("INSERT INTO cadComunicacao ( IdPessoa, IdTipo, Informacao ) VALUES (?, ?, ?)");
    $crud->bindParam(1, $IdPessoa , PDO::PARAM_INT);
    $crud->bindParam(2, $value[0] , PDO::PARAM_INT);
    $crud->bindParam(3, $value[1] , PDO::PARAM_STR);
    $retorno = $crud->execute(); 
}

If you want to try to simplify, make only one run instead of several, you can try to do so (Although me prefer the first way):

$sql = "INSERT INTO cadComunicacao ( IdPessoa, IdTipo, Informacao ) VALUES ";
foreach($Registros as &$value){
    $sql .= "(?, ?, ?),";
}
$sql = substr($sql, 0, -1);
$crud = $pdo->prepare($sql);
$i = 1;
foreach($Registros as &$value){
    $crud->bindParam($i++, $IdPessoa , PDO::PARAM_INT);
    $crud->bindParam($i++, $value[0] , PDO::PARAM_INT);
    $crud->bindParam($i++, $value[1] , PDO::PARAM_STR);
}
$retorno = $crud->execute(); 

TL;DR:

I recommend you do this procedure within a try... catch, if there is an error, it is easier to treat, more or less like this:

try {
    $crud = $pdo->prepare($sql);
    $i = 1;
    foreach($Registros as &$value){
        $crud->bindParam($i++, $IdPessoa , PDO::PARAM_INT);
        $crud->bindParam($i++, $value[0] , PDO::PARAM_INT);
        $crud->bindParam($i++, $value[1] , PDO::PARAM_STR);
    }
    $retorno = $crud->execute();                    
} catch (PDOException $e) {
    die($e);
}

---

One last but not least hint

Within the try catch I showed a way to display the errors on the screen, but this should not be done in a production environment.... take care because this exposes a little its structure, and also it is not anything elegant to show such a mistake to customers

What can be done is to create a function that stores these errors, whether in a file .txt, or in the database for example.

Like I usually do:

function create_log( $filename, $string ) {
    date_default_timezone_set( 'America/Sao_Paulo' );
    file_put_contents( $filename, date( 'r' )." ".$string.PHP_EOL, FILE_APPEND );
}

And within the try catch:

try {
  // Código      
} catch (PDOException $e) {
  http_response_code(500);
  // Removo todas informações que não devem ser salvas no log
  unset($_POST["senha"]);
  create_log( "logs/db_errors.log", "Informação adicional.PHP_EOL.json_encode($_POST).PHP_EOL."Exception: ".$e );
  // Com uma transaction ativa, comando desfaz tudo que foi feito no banco de dados
  // $db->rollBack();
  // Com uma transaction ativa, comando "salva" tudo que foi feito no banco de dados antes do erro que gerou a exception 
  // $db->commit();
  // Utilize a que melhor se adeque no seu caso
  die(); // Retorno vazio
}