'Allowed memory size of 67108864 bytes exhausted' error when looping through MySQL result set

Question

Ever since developing my first MySQL project about 7 years ago, I've been using the same set of simple functions for accessing the database (although, have recently put these into a Database class).

As the projects I develop have become more complex, there are many more records in the database and, as a result, greater likelihood of memory issues.

I'm getting the PHP error Allowed memory size of 67108864 bytes exhausted when looping through a MySQL result set and was wondering whether there was a better way to achieve the flexibility I have without the high memory usage.

My function looks like this:

function get_resultset($query) { 
    $resultset = array();

    if (!($result = mysql_unbuffered_query($query))) {
        $men = mysql_errno();
        $mem = mysql_error();
        echo ('<h4>' . $query . ' ' . $men . ' ' . $mem . '</h4>');
        exit;
    } else {
        $xx = 0 ;
        while ( $row = mysql_fetch_array ($result) )  {
            $resultset[$xx] = $row;
            $xx++ ;
        }
        mysql_free_result($result);
        return $resultset;
    }
}

I can then write a query and use the function to get all results, e.g.

$query = 'SELECT * FROM `members`';
$resultset = get_resultset($query);

I can then loop through the $resultset and display the results, e.g.

$total_results = count($resultset);

for($i=0;$i<$total_results;$i++) {
    $record = $resultset[$i];

    $firstname = $record['firstname'];
    $lastname = $record['lastname'];

    // etc, etc display in a table, or whatever
}

Is there a better way of looping through results while still having access to each record's properties for displaying the result list?

I've been searching around for people having similar issues and the answers given don't seem to suit my situation or are a little vague.

Answer 1

Your problem is that you're creating an array and filling it up with all the results in your result set, then returning this huge array from the function. I suppose that the reason for which this is not supported by any mysql_* function is that it's extremely inefficient.

You should not fill up the array with everything you get. You should step through the results, just like you do when filling up the array, but instead of filling anything, you should process the result and get to the next one, so that the memory for this one gets a chance to be freed.

If you use the mysql_* or mysqli_* functions, you should return the resource, then step through it right there where you're using it, the same way you're stepping through it to fill the array. If you use PDO, then you can return the PDOStatement and use PDOStatement::fetch() to step through it.