What is the best way to update inputs alternately

0

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

For example :

  • Logo and Digital Certificate are blank. UPDATE should not for these fields.
  • 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?

        
    asked by anonymous 08.08.2017 / 22:52

    1 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 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');
    
        
    08.08.2017 / 23:40