Function to simplify records in Mysql with PDO - You are saving all identical values

Question

I am trying to create a function to simplify insertion of records in Mysql using PDO.

The function is working but recording identical values.

Follow the PHP script:

<?php

function inserir($tabela,$lista_campos){
    $host = 'localhost';
    $user = 'root';
    $password = '';
    $database = 'banco_de_dados';

    try {
        $conexao = new PDO("mysql:host=$host;dbname=$database;charset=utf8", $user, $password);

    } catch (PDOException $e) {
        exit("Erro conectar: " . $e->getMessage());
    }


    $campos=[];
    $campos_insert=[];
    $valores=[];

    // LOOP PERCORER DICIONARIO =======================================================================
    foreach($lista_campos as $campo => $valor){
        //echo "$campo - $valor";
        
        $campos_insert[]=":".$campo;
        $campos[]=$campo;
        $valores[]=":".$valor;
        
    }
    // CONVERTE ARRAY PARA STRING =========================================================================
    $campos_insert=implode(",",$campos_insert);
    print_r($campos_insert);

    echo "<br>";

    $campos=implode(",",$campos);
    print_r($campos);

    echo "<br>";

    $valores=implode(",",$valores);
    //print_r($valores);

    echo "<br>";

    $query = $conexao->prepare("INSERT INTO $tabela ($campos) VALUES ($campos_insert)");
    echo "conexao->prepare(INSERT INTO $tabela ($campos) VALUES ($campos_insert) )";
    echo "<br>";

    // LOOP PARA O bindParam ==============================================================================
    foreach($lista_campos as $campo => $valor){
        $query->bindParam($campo, $valor, PDO::PARAM_STR);
        echo "query->bindParam($campo, $valor, PDO::PARAM_STR)";
        echo "<br>";
       
    }

     
    try {
        $result = $query->execute();
        //var_dump($result);

        if ($result) {
            echo '<p >registrado</p>';
        } else {
            echo '<p >erro ao registrar</p>';
        }
        

    } catch (PDOException $e) {
        exit("Error: " . $e->getMessage() );
    }
        
}




$valor=array("identificador" => "2535",
        "nome_original" => "nome original do arquivo",
        "tamanho" => "2.4 MB",
        "extencao" => "pdf",
        "arquivo" => "gd6gd7384jd8.pdf",
        "data_envio" => "02/05/2021",
        "entidade" => "cidade");

inserir($tabela="documentos",$lista_campos=$valor);

?>

---

---

When calling the function, it saves the line, only saves in all columns only the value cidade.

I did a lot of research on the Internet, but I couldn’t find any objective information about creating functions for simplify data entry with PDO.

Answer 1

You need to pass the variable by reference and not by value.

    // LOOP PARA O bindParam 
 ==============================================================================
//Colocando o & na sua variável $valor vai funcionar 
foreach($lista_campos as $campo => &$valor){
    $query->bindParam($campo, $valor, PDO::PARAM_STR);
    echo "query->bindParam($campo, $valor, PDO::PARAM_STR)";
    echo "<br>";