Populating an array from a database

Question

I have a query that populates an array from the database. In some cases, this query returns a great amount of data, (let's say for purpose of an example, 100.000 records). Each row of the database has at least 6 or 7 columns.

$results = [
  ['id' => 1, 'name' => 'name', 'status' => true, 'date' => '10-01-2012'],
  ['id' => 2, 'name' => 'name 2', 'status' => false 'date' => '10-01-2013'],
  ...
]

I need to perform a substitution of some of the data inside the $results array, based on another one that give me some information about how i would change the values in the rows.

$model = [
    'status' => ['function' => 'formatStatus', params => ['status']],
    'date' => ['function' => 'formatDate', params => ['date']]
]

Now that I have all the data, what do I do with it? I have the following routine:

foreach ($results as &$itemResult) {
        $oldValues = $itemResult;
        foreach ($itemResult as $attribute => &$value) {
            if (isset($model[$attribute]['function'])) {
                $function = $model[$attribute]['function'];
                $params = $model[$attribute]['params'];
                $paramsSize = count($params);
                for ($i = 0; $i < $paramsSize; $i++) {
                    $newParams[] = $oldValues[$params[$i]];
                }
                $itemResult[$attribute] = call_user_func_array([$this, $function], $newParams);
                $newParams = null;
            }
        }
    }

So, for each attribute for each row of my data array, I run check for the existence of a function and params information. When the attribute in question needs to be replaced, I call the function via call_user_func_array and replace the value with the function return value.

Also notice that I am replacing the current array, not creating another, by passing the reference &$itemResult inside the loop, so in the end, I have the same array from the beginning but with all columns that needed to be replaced with its new values.

The thing is, for little arrays, this method is quite good. But for big ones, it becomes a pain. I do believe that an array this size consumes so much RAM that makes the whole algorithm slow.

Could you provide me with some alternative to the problem? Should I use another data structure instead of the PHP array?

Answer 1

As noticed by mmm a solution might be (if applicable) to slice results. Anyway, here is my suggestion for the process to be as thrifty as possible.

// First create a specific model version appropriate to current results structure
$curModel = [];
$resultSample = reset($results);
foreach ($model as $attribute => $ope) {
  // keep only useful attributes
  if (array_key_exists($attribute, $resultSample) {
    // flip params list to have param as key
    $curModel[$attribute] = [
      'function' => $ope['function'],
      'params' => array_flip($params),
    ];
  }
}

// Then work:
// . without having to check if some attribute applies
// . with the ability to use array_intersect_key() to provide params
foreach ($results as &$itemResult) {
  foreach ($curModel as $attribute => $ope) {
    $function = $ope['function'];
    $itemResult[$attribute] =
      $function(array_intersect_key($itemResult, $ope['params']));
  }
}

It's based on the attempt to move as much work as possible, from the loop where it's repetitively executed, to a preparatory work executed once:

• build a version of $model strictly suitable for the current results structure (so we don't have to check it further)
• invert the nested iterations: rather than iterate on $model attributes and look if applicable, now we know that the $model contains only applicable attributes, so for each one we can directly work with the current record contents

At the same time, I chose to simplify the transformer function invocation: passing a unique array argument avoids using call_user_func_array(). Note that it needs the adaptation of the transformer functions, but it should be quite easy.

A few more little things:

• I stopped using a temporary copy of $itemResult, which is useless
• I used as much PHP specific functions as possible (instead of a sequence of statements) to reduce processor consumption: array_flip(), array_intersect_key()