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']]));
    }
}