How best to update inputs alternately

Question

I have some inputs, however I should only update the fields that have been filled:

For example:

1. Logo and Digital Certificate are blank. UPDATE should not occur to these fields.

2. Logo and Password are empty. UPDATE should not occur for these fields.

3. And so on and so forth.

I mean, you’d have to create all the combinations of darlings possible? Or is there a better way to treat this?

Answer 1

First, let’s consider that when your form is submitted, the following values come to PHP:

$_POST = [
    "ambiente" => "Novo ambiente",
    "logotipo" => "",
    "certificado" => "",
    "senha" => "Nova senha"
];

Simply, you can remove the null values from the list using the function array_filter.

$data = array_filter($_POST);

This will return the array:

Array
(
    [ambiente] => Novo ambiente
    [senha] => Nova senha
)

To build the SQL query, that is, put it in the format $key=$value, we can use the function array_map together with the function array_keys:

$fields = array_map(function ($value, $key) {
    return sprintf("`%s`='%s'", $key, $value);
}, $data, array_keys($data));

This will pass the value/key pair to the anonymous function, which returns the desired format. Thus, the array that we will have with this will be:

Array
(
    [0] => `ambiente`='Novo ambiente'
    [1] => `senha`='Nova senha'
)

We can join the values using the function implode and already build the final consultation:

$query = sprintf("UPDATE `tabela` SET %s WHERE `id`=%d", implode(",", $fields), $id);

If you display the result, it will be:

UPDATE `tabela` SET `ambiente`='Novo ambiente',`senha`='Nova senha' WHERE `id`=1

See working on Ideone.

Note that if, for example, only the logo field has been filled in, the result will be:

UPDATE `tabela` SET `logotipo`='Novo logotipo' WHERE `id`=1

See working on Ideone.

---

To use the PDO together with the bindValue the logic is basically the same. You build the query the same way, but instead of the value, you insert the question mark:

$_POST = [
    "ambiente" => "Novo ambiente",
    "logotipo" => "",
    "certificado" => "",
    "senha" => "Nova senha"
];

$id = 1;

$data = array_filter($_POST);

$fields = array_map(function ($key) {
    return sprintf("`%s`=?", $key);
}, array_keys($data));

$query = sprintf("UPDATE `tabela` SET %s WHERE `id`=%d", implode(",", $fields), $id);

echo $query;

This will generate a query like:

UPDATE `tabela` SET `ambiente`=?,`senha`=? WHERE `id`=1 

To execute the bind values, just go through the values:

$stmt = $pdo->prepare($query);

foreach (array_values($data) as $i => $value) {
    $stmt->bindValue($i+1, $value);
}

Thus generating the calls:

$stmt->bindParam(1, 'Novo ambiente');
$stmt->bindParam(2, 'Nova senha');