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 Answer 1

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\$
2
  • \$\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\$ Mar 17, 2018 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, 2018 at 1:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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