Unbuffered Queries in CI

This page demonstrates how to change a few driver files in CI's system directory to use mysql_unbuffered_query rather than the standard mysql_query.

mysql_unbuffered_query is useful for large result sets, such as when exporting a large amount of data to a CSV or XML file.

These modifications require making changes to your Codeigniter MySQL database driver files. Do not attempt unless you make a backup of these files first!

These modifications are only to CI's core database funcionality, it doesn't add mysql_unbuffered_query to CI's Active Record class.

In the Code Igniter system/database/DB_driver.php file on or about line 245, change this line:

function query($sql, $binds = FALSE, $return_object = TRUE)

to this:

function query($sql, $binds = FALSE, $return_object = TRUE, $buffered = TRUE)

Then about line 295 in that same file, change this line:

if (FALSE === ($this->result_id = $this->simple_query($sql)))

to this:

if (FALSE === ($this->result_id = $this->simple_query($sql,$buffered)))

Then about line 443, in the simple_query($sql) method title, change...

function simple_query($sql)

to:

function simple_query($sql,$buffered=TRUE)

Then update the return of that same simple_query() method from:

return $this->_execute($sql);

to:

return $buffered ? $this->_execute($sql) : $this->_execute_unbuffered($sql);

Next, in the Code Igniter system/database/drivers/mysql/mysql_driver.php file, make the following change:

About line 165, after the definition of the _execute($sql) method, add a new method, _execute_unbuffered($sql), looking like this: Add this code after the definition of the _execute($sql) method:

    function _execute_unbuffered($sql)
    {
        $sql = $this->_prep_query($sql);
        return @mysql_unbuffered_query($sql, $this->conn_id);
    }

Then, to utilize an unbuffered query, rather than the standard buffered query, change this representation:

$query = $this->db->query($sql);

To this, which will perform the query, not use binds (second parameter), returns an object (third parameter), and sets the query to be unbuffered (new fourth parameter). The second parameter defaults to FALSE, and the third parameter defaults to TRUE.

$query = $this->db->query($sql,FALSE,TRUE,TRUE);