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: [code] function query($sql, $binds = FALSE, $return_object = TRUE) [/code]

to this: [code] function query($sql, $binds = FALSE, $return_object = TRUE, $buffered = TRUE) [/code]

Then about line 295 in that same file, change this line: [code] if (FALSE === ($this->result_id = $this->simple_query($sql))) [/code]

to this: [code] if (FALSE === ($this->result_id = $this->simple_query($sql,$buffered))) [/code]

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

About line 165, after the definition of the _execute($sql) method, add a new method, _execute_unbuffered($sql), looking like this: Change this code: [code] function _execute_unbuffered($sql) { $sql = $this->_prep_query($sql); return @mysql_unbuffered_query($sql, $this->conn_id); } [/code]

Then, to utilize an unbuffered query, rather than the standard buffered query, change this representation: [code] $query = $this->db->query($sql); [/code]

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

[code] $query = $this->db->query($sql,FALSE,TRUE,TRUE); [/code]