1
\$\begingroup\$

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.

\$\endgroup\$
1
\$\begingroup\$

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']]));
    }
}
\$\endgroup\$
  • \$\begingroup\$ 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? \$\endgroup\$ – pippa dupree Mar 17 '18 at 14:39
  • \$\begingroup\$ 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. \$\endgroup\$ – nibra Mar 18 '18 at 1:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.