Inserting Multiple Records Into a Table

Category:Contributions::Libraries::Database

[h3]Overview[/h3] This subclass of CodeIgniter's Model class was originally posted by [url=http://codefury.net]Kenny Katzgrau at CodeFury.net[/url].

Inserting multiple records into a database table at once is something that is not currently handled by CodeIgniter's database methods.

In the event that many records need to be inserted into a table, a significant performance increase can be gained by executing a large query with multiple rows being inserted instead inserting rows in individual queries.

This article will document an extension of the CodeIgniter model with methods that allow for efficient insertion of data upwards of 1,000 rows. The provided code handles escaping of SQL, and the appropriate formatting.

Although this isn't a terribly difficult task, the focus of this contribution is to provide a tested, optimized solution to a common problem that CodeIgniter developers have.

[h3]Performance[/h3] This solution has successfully prepared a 10,000 row insertion (complete with escaping of 40,000 strings) in under 1 second on a Core 2 Duo WAMP setup with the default PHP memory limits. It relies heavily on speedier built-in PHP functions such as array_walk_recursive to do heavy lifting.

[h3]Example Usage[/h3] An example call would look like:

[code] /* Prepare some fake data (10000 rows, 40,000 values total) */ $rows = array_fill(0, 10000, array("That's", "All", "Folks!", 0));

/* Execute the base class method that is now available, insert_rows() */ $this->insert_rows('table_name', array('ColA', 'ColB', 'ColC', 'ColD'), $rows); [/code]

Which would produce and execute:

[code] INSERT INTO table_name (ColA, ColB, ColC, ColD) VALUES ('That's', 'All', 'Folks!', 0), ('That's', 'All', 'Folks!', 0), ... 997 More Times ... ('That's', 'All', 'Folks!', 0) [/code]

[h3]Implementing[/h3] To add this functionality to your models, you will be extending the model class, which you can read more about here: [url=http://codeigniter.com/user_guide/general/core_classes.html]Extending The Core[/url]

This article will assume you haven't already extended the CI model. If you have, feel free to copy and paste what you need.

Create a new class in application/libraries. Name it MY_Model.php . Insert the following code.

[b]Note:[/b] There is an assumption that you have not changed the configuration value 'subclass_prefix'. If you have, use your custom subclass prefix instead of 'MY_'.

[code]

class MY_Model extends Model {

function Extended_Model()
{
    parent::Model();
}

/**
 * A method to facilitate easy bulk inserts into a given table.
 * @param string $table_name
 * @param array $column_names A basic array containint the column names
 *  of the data we'll be inserting
 * @param array $rows A two dimensional array of rows to insert into the
 *  database.
 * @param bool $escape Whether or not to use CodeIgniter's escape method
 *  on data that will be inserted. Default = true.
 * @author Kenny Katzgrau <katzgrau@gmail.com>
 */
function insert_rows($table_name, $column_names, $rows, $escape = true)
{
    /* Build a list of column names */
    $columns    = array_walk($column_names, array($this, 'prepare_column_name') );
    $columns    = implode(',', $column_names);

    /* Escape each value of the array for insertion into the SQL string */
    if( $escape ) array_walk_recursive( $rows, array( $this, 'escape_value' ) );
    
    /* Collapse each rows of values into a single string */
    for($i = 0; $i < count($rows); $i++)
    {
        $rows[$i] = implode( ',', $rows[$i] );
    }

    /* Collapse all the rows into something that looks like
     *  (r1_val_1, r1_val_2, ..., r1_val_n),
     *  (r2_val_1, r2_val_2, ..., r2_val_n),
     *  ...
     *  (rx_val_1, rx_val_2, ..., rx_val_n)
     * Stored in $values
     */
    $values = "(" . implode( '),(', $rows ) . ")";

    $sql = "INSERT INTO $table_name ( $columns ) VALUES $values";

    return $this->db->simple_query($sql);
}

function escape_value(& $value)
{
    if( is_string($value) )
    {
        $value = "'" . mysql_real_escape_string($value) . "'";
    }
}

function prepare_column_name(& $name)
{
    $name = "`$name`";
}

} [/code]

In the model in which you would like use this new functionality, replace the typical extension of the Model class, which looks like:

[code] class Messages extends Model { ... [/code]

with:

[code] class Messages extends MY_Model { ... [/code]

Once you do this, you can now use the method insert_rows(). Documentation of the function is provided in the PHPDoc block.

[h3]Last Notes[/h3] It is still suggested that if you have to insert a number of rows nearing upwards of 1,000, that you put thought into whether you want to page through the data being inserted (and insert 1,000 at a time). Otherwise, you may run the risk of exceeding MySQL's (or you DB of choice's) maximum packet size.

-- Stub --