How to merge two arrays

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 manadatory 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.

I know I could do something like the following withut using arrays but I don't know how to use prepared statement in this case

if(!empty($user_last))
  $update_values[] = "user_last='".$user_last."'";
$update_values_imploded = implode(', ', $update_values);

if( !empty($update_values) ){
 $q = "UPDATE users SET $update_values_imploded WHERE user_id='$userid' ";
 $r = mysqli_query($conn,$q);

 if($r){
$_SESSION['success_msg'] = 'profile updated!';
header("location: ../client_profile.php");
exit();
}
}

The array method I've tryed 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.

`but it doesn't really work properly.` What does that mean? You need to show us the values of the arrays, what you expect, and what you get. — John Conde, Jan 19 '19 at 20:01
*"I don't know how to use prepared statement in this case"* Almost in the same way as you did with select — Pinke Helga, Jan 19 '19 at 20:18
^ and that is your real question. The alternative is not best practice. You might then even need to implement locking to avoid your record has changed in between read & write. — trincot, Jan 19 '19 at 20:20
*If* you ask on SO, you should ask the *proper* way since you cannot solve neither the one nor the other approach on your own. SO is meant to archive *good* questions and answers as a knowledge database. — Pinke Helga, Jan 19 '19 at 20:25
That's not meant as citism, just to encourage you to ask for the proper way. ;-) — Pinke Helga, Jan 19 '19 at 20:31
We needed to see how the `$update_values` are retrieved. There's only one value added. — Pinke Helga, Jan 19 '19 at 20:34
For passing arguments to a dynamic number of `?` in a prepared statement, use `bind_param` like `call_user_func_array(array($stmt, 'bind_param'), refValues($params));`. See https://stackoverflow.com/a/16120923/5459839 — trincot, Jan 19 '19 at 20:42
From PHP documentation of `array_merge`: " If an array key exists in both arrays, then the element from the **first** array will be used and the matching key's element from the second array will be ignored." - Do you want the fist array values or the second? — dWinder, Jan 19 '19 at 20:51
Hi @trincot thanks for your reply I need to test it but I believe it can be a solution — pippo, Jan 19 '19 at 20:53
Hi @DavidWinder thanks for your reply. I need vales from first array but the problem is if the first array has an empty value and the second array with values from databse hasn't then I need to use the value from second array — pippo, Jan 19 '19 at 20:55
if the first array has empty field you can just use `array_filter` to exclude them and then merge — dWinder, Jan 19 '19 at 20:58

dWinder · Answer 1 · 2019-01-19T22:20:34.820

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!

Actually you **are** using it according to the PHP documentation. Your comment to the question refers to **numeric** keys, ["If the input arrays have the same **string** keys, then the later value for that key will overwrite the previous one"](http://php.net/manual/en/function.array-merge.php) — Nick, Jan 19 '19 at 21:38

Pinke Helga · Answer 2 · 2019-01-19T22:03:09.133

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.

score 1 · Accepted Answer · answered Jan 20 '19 at 22:21

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

Hi yes I read about the coalesce but never understood how to use it :( your example works fine and I’m going to accept your answer as well. I read in many posts that is better not to involve mysql in logic operations but prefer php to do the hard work is it correct? It is just a curiosity what I’m asking. Many thanks for your time and your support, I’ve learned a lot thanks to you ;) — pippo, Jan 21 '19 at 07:00
There is no general answer whether logic should be in PHP or in SQL (it's more like a religious question). I would even tend to say that SQL logic is often used too less. Here it is used to avoid dynamic SQL at all which would be a potentiol risk of SQL injection. When using SQL, you should *always* filter by a whitelist or even better better lookup names from a map `['form_field_name' => 'db_col_name', ...]`. Then the remaining risk would be in code refactoring. Often you can reduce huge amount of data transfer on db connection by using SQL logic on big tables. — Pinke Helga, Jan 21 '19 at 09:45
In this particular case you could pre-check if non of the fields are set and therefore an execution of an update statement is not needed at all. I would consider to do so in an environment with high server loads on the db connection. — Pinke Helga, Jan 21 '19 at 10:06

score 0 · Answer 4 · answered Jan 19 '19 at 20:17

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']

How to merge two arrays

4 Answers4