CODEWITHSUNDEEP
phpmysql
arvil
arvil

Reputation: 920

Elegantly Handling large MySQL rows to be processed by PHP

So, i'd been exposed to more and more real life application where: SELECT * FROM table is too heavy already, resulting to Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 47 bytes) in.

What I am currently doing is splitting these mySQL rows into batches, setting a variable $perPage, then passing a $_GET['page'] variable to the page, something link: process.php?page=1

Which I think is "good". But sometimes, we want full automation. So I set $nextPage = (int) $_GET['page'] + 1, then redirect the page to then iteration after processing these mySQL rows header("Location: process.php?p="$nextPage)

Now, this would give you some issues:

  1. Its harder to debug. If a warning/notice issued in page 3, you won't be able to see these output not unless you log them or looked at your php logs.
  2. Browsers won't allow you to redirect too much. So, we either use command-line curl or code another php script that will cURL process.php with follow-redirect enabled.

This is how I currently handle this, but sometimes thinking that I need to code more just to make it work is a bit frustrating. Does anyone know how to handle these more elegantly?

Upvotes: 3

Views: 2718

Answers (2)

meda
meda

Reputation: 45490

Just process your rows one by one instead of collecting them all in array.

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    //process then release, do not store
}

If your processing requires an array, then there is a trick. PHP Generators would disguise a while loop as an array.

A generator allows you to write code that uses foreach to iterate over a set of data without needing to build an array in memory, which may cause you to exceed a memory limit, or require a considerable amount of processing time to generate. Instead, you can write a generator function, which is the same as a normal function, except that instead of returning once, a generator can yield as many times as it needs to in order to provide the values to be iterated over.

Using yield(from C) will allow you to achieve that.

Here is an example on how to implement it:

function getRecords()
{
    $sql = 'SELECT field1, field2 FROM table';
    $stmt = $this->conn->prepare($sql);
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        yield $row;
    }
}

foreach ($this->getRecords() as $record) {
    //process then release, do not store
}

However, it wouldn't make much sense, as PDOStatement is already Traversable (frankly, uses the same trick as generators) and you can have this function like this with same outcome

function getRecords()
{
    $sql = 'SELECT field1, field2 FROM table';
    $stmt = $this->conn->prepare($sql);
    $stmt->execute();
    return $stmt;
}

Notice:

  1. I select specific fields, since memory is a concern you should be careful with everything, SELECT * can unnecessarily make you run out of memory.
  2. Something like fetchAll() would not work, because it will get all the rows at once.
  3. Do not store the data in memory when iterating in the foreach this defeats the purpose of generators.

Upvotes: 7

kenarsuleyman
kenarsuleyman

Reputation: 1020

In a loop; get a piece of data from database do whatever you want, free memory with unset() function and then get next piece of data.

Upvotes: 0

Related Questions