I have some inputs, however I should only update the fields that have been filled:
For example :
Logo and Password are left blank. UPDATE should not occur for these fields.
And so on.
That is, would you have to create all possible queries combinations? Or is there a better way to handle it?
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 put, you can remove the null values from the list using the array_filter .
$data = array_filter($_POST);
This will return the array :
Array
(
[ambiente] => Novo ambiente
[senha] => Nova senha
)
To construct the SQL query, that is, put in $key=$value format, we can use the array_map in conjunction 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. So the array that we will have with this will be:
Array
(
[0] => 'ambiente'='Novo ambiente'
[1] => 'senha'='Nova senha'
)
We can merge the values using the implode function and build the final query:
$query = sprintf("UPDATE 'tabela' SET %s WHERE 'id'=%d", implode(",", $fields), $id);
If you see the result, it will be:
UPDATE 'tabela' SET 'ambiente'='Novo ambiente','senha'='Nova senha' WHERE 'id'=1
See working at Ideone .
Note that if, for example, only the logo field has been filled, the result will be:
UPDATE 'tabela' SET 'logotipo'='Novo logotipo' WHERE 'id'=1
See working at Ideone .
To use the PDO along with bindValue the logic is basically the same. You construct the query in 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 of values, simply scroll through the values:
$stmt = $pdo->prepare($query);
foreach (array_values($data) as $i => $value) {
$stmt->bindValue($i+1, $value);
}
Generating calls:
$stmt->bindParam(1, 'Novo ambiente');
$stmt->bindParam(2, 'Nova senha');