Decrease execution time in updating rows in PHP

Question

Any tips on how the below code can be optimised for faster execution?

I have a table in postgres called staging with the below columns;

|person|colour|sport|
|Tom |blue |Tennis|

and a multi dimensional associative array that contains updates for each entry of the staging table, like so:

$updateArray = array();
$updateArray['Tom']['color']='orange';
$updateArray['Tom']['sport']='golf';

I need to loop through the updateArray and update the entries of the staging data.

Here is what I have so far, where result2 is a query to select all the rows from the staging table. For each row returned I am looping through every entry of the updateArray, and then checking if it exists as one of the keys.

while ($row2 = pg_fetch_assoc($result2))
 {
 foreach ($array as $key => $value)
 {
 if ($key == $row2['person'])
 {
 foreach ($value as $column_name => $column_value)
 {
 $row2[$column_name] = $column_value;
 }
 }
 }
 fputcsv($fp, $row2);
 }

The above example is a simplified one, I need to update about 120,000 people and about 700 columns for each person multiple times so I am trying to get the above code more efficient.

Answer 1

You can replace the outer loop with a simple test:

if (array_key_exists($row2['person'], $array)) {
 $value = $array[$row2['person']];
}

Next, the inner loop can be replaced with a single merge:

$row2 = array_merge($row2, $value);

After some small refactorings, the code would look like this:

while ($row2 = pg_fetch_assoc($result2))
{
 if (array_key_exists($row2['person'], $array)) {
 fputcsv($fp, array_merge($row2, $array[$row2['person']]));
 }
}

• pippa dupree: Thank you very much for this, @nibra! One follow up question; if I wanted to use your above logic and extend it to work for numeric keys as well, would an efficient solution be to replace array_merge($row2, $array[$row2['person']])) with $row2 + $array[$row2['person_id']] where $row2['person_id'] results in a numeric key?
• nibra: Yes, that should work. However, you will not have real control over the indices, i.e., you can't rely on fx. index 3 always being the color.