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

2

Hi, I am trying to create a page to change the cadastral data with mysql database, but it should only update the data that is filled and do not change the other data if they already exist in the database.

I've tried a thousand ways and I can not, could you help me?

Follow 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();
}

?>
    
asked by anonymous 25.10.2017 / 15:08

4 answers

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). Must use as array:

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

However, when it's time to interpolate your variables in the query, you're 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 quotation marks, is using single quotation marks and the $campos variable is added 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 addressed in your code. What if no fields are changed?

This situation can be easily solved 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 think this is the reason for your last mistake.

    
25.10.2017 / 15:32
2

You need to do the assignment of the array correctly, now you override the string every if. It was also necessary to add the simple quotes correctly in the time of implode() ide was to use prepared statements and eliminate this problem and that of sql injections.

Instead of:

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

Do (do not 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:

Dynamic bind PDO

    
25.10.2017 / 15:25
1

I was able 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();
    }

    ?>

Thanks to everyone.

    
29.10.2017 / 01:09
-1

Your doubt and something common in the world of API's, I'll tell you how I'm not going to make the code (this is up to you)! Look for all the information in the database, and update the information that was passed, the ones that were not passed receive the old values (and a form, there are others ...).

    
26.10.2017 / 13:03