Update the data in the database without changing the others that already exist - PDO

Question

Hi, I’m trying to create a page to change the registration data with mysql database, but it should only update the data that are filled and not change the other data if it already exists in the database.

I’ve tried a thousand ways and I can’t, they could help me?

Follows the code:

<?php

try{    
//Abre a conexão; 
    include "conexao.php";  

//Pega os dados do formulário; 

    $campos = array();
    $id = $_POST['id'];
    if(!empty($nome)) $campos[]="nome='$nome'";
    if(!empty($apelido)) $campos[]="apelido='$apelido'";
    if(!empty($telefone)) $campos[]="telefone='$telefone'";
    if(!empty($celular)) $campos[]="celular='$celular'";
    if(!empty($email)) $campos[]="email='$email'";
    if(!empty($endereco)) $campos[]="endereco='$endereco'";
    if(!empty($num_end)) $campos[]="num_end='$num_end'";


//Prepara a query; 
    $stmt = $conn -> prepare("UPDATE `clientes` SET ".implode(',', $campos)." WHERE ID = :id");

//Executa a query;
    $stmt->execute([':id' => $id]);
    echo "Dados atualizados com sucesso! :)";   

} 
//Exibe a mensagem caso dê erro;
    catch(PDOException $e) {
    echo $stmt;
    echo 'Error: ' . $e->getMessage();
}

?>

Answer 1

You need to assign the array correctly, now you overwrite the string every if. Also missing to add simple quotes correctly at the time of implode()the ideal was to use Prepared statements and eliminate this problem and sql injections.

In place of:

if(!empty($nome)) $campos = "nome='$nome'";

Do it (don’t forget the other fields):

if(!empty($nome)) $campos[] = "nome='$nome'";

//ou se preferir sprintf()
//$set = sprintf("'%s'",implode("','",  $campos));

$set = "'". implode("','",  $campos) ."'";
$stmt = $conn -> prepare("UPDATE `clientes` SET ". $set ." WHERE ID = $id");

Related:

How to make dynamic bind PDO

Answer 2

You have two problems with your code, the first is that with each new field, you are replacing the previous one (as reported by @rray). You should use as an array:

if(!empty($nome)) $campos[] = "nome='$nome'";

However, when it comes to performing the input of your variables in the query, you are doing it wrong.

That:

$stmt = $conn -> prepare("UPDATE `clientes` SET '.implode(',','.$campos').' WHERE ID = '.$id'");

Should be:

$stmt = $conn -> prepare("UPDATE `clientes` SET ".implode(',', $campos)." WHERE ID = '{$id}'");

Basically, where you should close and open string with double quotes, is using single quotes and is added the variable $campos, inside the implode function, as if it were a normal string and not a variable.

More information on:

PHP: Strings

However, this will leave your code vulnerable to SQL Injection. You should use Prepared statements instead:

$stmt = $conn -> prepare("UPDATE `clientes` SET ".implode(',', $campos)." WHERE ID = :id");
$stmt->execute([':id' => $id]);

More information on

PHP: SQL Injection

UPDATE

There is a situation that is not being dealt with in your code. And if no field is changed?

This situation can be easily resolved by checking for items in the array:

if (count($campos) > 0)
{
    /Prepara a query; 
    $stmt = $conn -> prepare("UPDATE `clientes` SET ".implode(',', $campos)." WHERE ID = :id");

    //Executa a query;
    $stmt->execute([':id' => $id]);
    echo "Dados atualizados com sucesso! :)";  
}
else
{
    echo "Nenhum campo foi modificado";  
}

I believe this is the reason for your last mistake.

Answer 3

I managed to leave the code like this:

    <?php

    try{    

    //Abre a conexão; 
    include "conexao.php";  

    //Pega os dados do formulário; 
    $id = $_POST['id'];

    $campos = array();

    if(!empty($_POST['nome'])){
    $nome = $_POST['nome'];
    $campos[]= "nome='$nome'";
    }

    if(!empty($_POST['apelido'])){
    $apelido = $_POST['apelido'];
    $campos[]="apelido='$apelido'";
    }

    if(!empty($_POST['telefone'])){
    $telefone = $_POST['telefone'];
    $campos[]="telefone='$telefone'";
    }

    if(!empty($_POST['celular'])){
    $celular = $_POST['celular'];
    $campos[]="celular='$celular'";
    }

    if(!empty($_POST['email'])){
    $email = $_POST['email'];
    $campos[]="email='$email'";
    }

    if(!empty($_POST['$endereco'])){
    $endereco = $_POST['endereco'];
    $campos[]="endereco='$endereco'";
    }

    if(!empty($_POST['num_end'])){  
    $num_end = $_POST['num_end'];   
    $campos[]="num_end='$num_end'";
    }


    //Prepara a query; 
    $stmt = $conn -> prepare("UPDATE `clientes` SET ".implode(',', $campos)." WHERE ID = :id");

    //Executa a query;
    $stmt->execute(array(':id' => $id));
    echo "Dados atualizados com sucesso! :)";   

    } 
    //Exibe a mensagem caso dê erro;
    catch(PDOException $e) {
    echo 'Error: ' . $e->getMessage();
    }

    ?>

Thank you all.

Answer 4

Your doubt and something common in the world of API’s, I’ll tell you how else I won’t make the code (this is on you)! Search all the information in the database, and update the information that was passed, the information that was not passed receive the old values (and one way, there are other...).