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

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);