Update database table row with submission data and set new values to NULL if empty

Question

I've got a form and I use it to update a user profile. The problem is that I don't want to make all the fields mandatory so if the user leaves a blank field then the value already stored in the database gets updated with a blank or null value. Therefore I've decided to query the database and get an array of values already stored, then I've got an array of values from the user form. I need to merge the two arrays in a way that I can update the database field if a new value has been inserted in the form and keep the old database value if the form field is blank.

The array method I've tried is the following, but it doesn't really work properly.

$merged_array = array_unique(array_merge($database_rows, $form_data));

Do you have any other ideas? Many thanks

This is the full code with a from array simulated

$km_user_id = 2;

// this array comes from the form
$from_array = array(
    'km_user_first_name' => 'Antonio', 
    'km_user_last_name' => 'Acri',
    'km_user_address' => 'via pola',
    'km_user_city' => 'roma',
    'km_user_city_prov' => '',
    'km_user_postcode' => '',
    'km_user_email' => '',
    'km_user_website' => 'url',
    'km_user_telephone' =>  '123456',
    'km_user_mobile' => '',
    'km_user_fiscalcode' => '',
    'km_user_document' => '',
    'km_user_document_number' => '',
    'km_user_document_exp' =>  '',
    'km_user_birth_place' => '',
    'km_user_birth_date' => ''    
);


// select vakues from database

$query= "SELECT km_user_first_name, km_user_last_name, km_user_address, km_user_city, km_user_city_prov, km_user_postcode, km_user_email, km_user_website, km_user_telephone, km_user_mobile, km_user_fiscalcode, km_user_document, km_user_document_number, km_user_document_exp, km_user_birth_place, km_user_birth_date FROM km_users WHERE km_user_id= ?";
$stmt = mysqli_prepare($db_user_conn, $query);
mysqli_stmt_bind_param($stmt, 'i', $km_user_id);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
print_r($row);   
?>
<br>
<br>
<?php
print_r($from_array);
?>
<br>
<br>
<?php

$result = array_merge($from_array, $row); 
print_r($result);

You can see the value [km_user_address] => via roma 11 supposed to be [km_user_address] => via pola in the merged array.

Answer 1

Create a firm foundation for your script by defining an array of whitelisted/editable columns, you might also associate some santization functions for each column (but I won't go too far down the rabbit hole of validation and sanitization).

Iterate the whitelist, use $_POST values if they exist and build a prepared statement and an array of values for each placeholder.

Then simply execute your prepared statement.

Code: (PHPize Demo)

// use mb_trim() when PHP version allows
$editableUserColumns = [
    'km_user_first_name' => 'trim',
    'km_user_last_name' => 'trim',
    'km_user_address' => 'trim',
    'km_user_city' => 'trim',
    'km_user_city_prov' => 'trim',
    'km_user_postcode' => 'trim',
    'km_user_email' => 'trim',
    'km_user_website' => 'trim',
    'km_user_telephone' => 'trim',
    'km_user_mobile' => 'trim',
    'km_user_fiscalcode' => 'trim',
    'km_user_document' => 'trim',
    'km_user_document_number' => 'trim',
    'km_user_document_exp' => 'trim',
    'km_user_birth_place' => 'trim',
    'km_user_birth_date' => 'trim'
];

if (empty($_POST['km_user_id'])) {
    exit('Invalid/Missing data');
}

$sets = [];
$vals = [];
foreach ($editableUserColumns as $col => $func) {
    $sets[] = "$col = NULLIF(?, '')";
    $vals[] = $func($_POST[$col] ?? '');
}
$sql = sprintf(
    'UPDATE users SET %s WHERE km_user_id = ?',
    implode(', ', $sets)
);
$vals[] = $_POST['km_user_id'];
$mysqli->execute_query($sql, $vals);
echo 'Affected rows: ' . $mysqli->affected_rows;

Answer 2

I want to provide a second answer with a total different approach.

The probably best and most secure way you can do it is just to execute a simple static prepared statement and let the handling of empty parameters up to SQL:

$sql_update = <<<_SQL_

  UPDATE 
    `users`
  SET
    `km_user_first_name`      = COALESCE(NULLIF(?, ''), `km_user_first_name`      ),
    `km_user_last_name`       = COALESCE(NULLIF(?, ''), `km_user_last_name`       ),
    `km_user_address`         = COALESCE(NULLIF(?, ''), `km_user_address`         ),
    `km_user_city`            = COALESCE(NULLIF(?, ''), `km_user_city`            ),
    `km_user_city_prov`       = COALESCE(NULLIF(?, ''), `km_user_city_prov`       ),
    `km_user_postcode`        = COALESCE(NULLIF(?, ''), `km_user_postcode`        ),
    `km_user_email`           = COALESCE(NULLIF(?, ''), `km_user_email`           ),
    `km_user_website`         = COALESCE(NULLIF(?, ''), `km_user_website`         ),
    `km_user_telephone`       = COALESCE(NULLIF(?, ''), `km_user_telephone`       ),
    `km_user_mobile`          = COALESCE(NULLIF(?, ''), `km_user_mobile`          ),
    `km_user_fiscalcode`      = COALESCE(NULLIF(?, ''), `km_user_fiscalcode`      ),
    `km_user_document`        = COALESCE(NULLIF(?, ''), `km_user_document`        ),
    `km_user_document_number` = COALESCE(NULLIF(?, ''), `km_user_document_number` ),
    `km_user_document_exp`    = COALESCE(NULLIF(?, ''), `km_user_document_exp`    ),
    `km_user_birth_place`     = COALESCE(NULLIF(?, ''), `km_user_birth_place`     ),
    `km_user_birth_date`      = COALESCE(NULLIF(?, ''), `km_user_birth_date`      )
  WHERE
    `user_id` = ?
  ;

_SQL_;

$stmt = $db_user_conn->prepare($sql_update);

mysqli_stmt_bind_param
(
  $stmt,  'ssssssssssssssssi',

  $form_data['km_user_first_name'],
  $form_data['km_user_last_name'],
  $form_data['km_user_address'],
  $form_data['km_user_city'],
  $form_data['km_user_city_prov'],
  $form_data['km_user_postcode'],
  $form_data['km_user_email'],
  $form_data['km_user_website'],
  $form_data['km_user_telephone'],
  $form_data['km_user_mobile'],
  $form_data['km_user_fiscalcode'],
  $form_data['km_user_document'],
  $form_data['km_user_document_number'],
  $form_data['km_user_document_exp'],
  $form_data['km_user_birth_place'],
  $form_data['km_user_birth_date'],
  $km_user_id
);

mysqli_stmt_execute($stmt);

Answer 3

I hope I realize your question (fill free to comment if not).

If you want to have the form array as the main values and only if its empty take the value from the second array you can use combination on array_filter and array_merge.

Consider the following example:

$form = array("km_user_first_name" => "Alice", "km_user_address" => "");
$row = array("km_user_first_name" => "Boby", "km_user_address" => "via pola");
$res = array_merge($row, array_filter($form));

This will output:

Array
(
    [km_user_first_name] => Alice
    [km_user_address] => via pola
)

I know I use the form array second as said in PHP documentation (thanks @Nick):

If the input arrays have the same string keys, then the later value for that key will overwrite the previous one

Hope that helps!

Answer 4

You should rely on prepared statements.

Only build the column names using a whitelist as dynamic SQL. Filter out empty strings ''. Insert anonymous prepared statement parameters ? and generate a type specifiers string. Then pass the values as bound parameters.

This one should work (could not test it yet).

<?php
declare (strict_types=1);

$km_user_id = 2;

// this array comes from the form
$form_array =
[
  'km_user_first_name'      => 'Antonio',
  'km_user_last_name'       => 'Acri',
  'km_user_address'         => 'via pola',
  'km_user_city'            => 'roma',
  'km_user_city_prov'       => '',
  'km_user_postcode'        => '',
  'km_user_email'           => '',
  'km_user_website'         => 'url',
  'km_user_telephone'       => '123456',
  'km_user_mobile'          => '',
  'km_user_fiscalcode'      => '',
  'km_user_document'        => '',
  'km_user_document_number' => '',
  'km_user_document_exp'    => '',
  'km_user_birth_place'     => '',
  'km_user_birth_date'      => '',
];

$white_list =
[
  'DEBUGkm_user_first_name'      => true, // DEBUG TEST filter
  'km_user_last_name'       => true,
  'km_user_address'         => true,
  'km_user_city'            => true,
  'km_user_city_prov'       => true,
  'km_user_postcode'        => true,
  'km_user_email'           => true,
  'km_user_website'         => true,
  'km_user_telephone'       => true,
  'km_user_mobile'          => true,
  'km_user_fiscalcode'      => true,
  'km_user_document'        => true,
  'km_user_document_number' => true,
  'km_user_document_exp'    => true,
  'km_user_birth_place'     => true,
  'km_user_birth_date'      => true,
];


// filter by whitelist and remove  ''  but NOT  '0'
$non_empty = array_intersect_key(array_filter($form_array, function($v){return $v !== '';}), $white_list);

if(!empty($non_empty))
{
  $cols   = '`' . implode('` = ?, `', array_keys($non_empty)) . ' = ?';
  $query  = "UPDATE `users` SET $cols WHERE `user_id` = ?";
  $values = array_values($non_empty);
  array_push($values, $km_user_id);

  $stmt = mysqli_prepare($db_user_conn, $query);
  mysqli_stmt_bind_param($stmt, str_repeat('s', count($non_empty)).'i', ...$values);
  mysqli_stmt_execute($stmt);
  // TODO: error handling
}

km_user_first_name should not be updated since it is not in the whitelist. Remove the prefix DEBUG when tested.

Answer 5

Why creating another array? You can realy edit the array you already have. for example : You get the indexed array from database Fill the form with the values so the user can see old informations . When user edits lets say his name myArray ['name'] = $_POST ['name']