Insert Multiple Rows Into A Database with CodeIgniter

When using a spiffy web application framework like CodeIgniter for PHP, I tend to start depending on the framework for a lot of functions.

For example, if I want to get the segments of the URI, I first think, “CodeIgniter must have that!” If I want a function to convert input from a <textarea> into correctly formatted html, I think, “CodeIgniter must have that!”

And when CodeIgniter doesn’t have a function that would be really useful, I tend to think, “Now what?”

One commonly found problem is that CodeIgniter doesn’t have a specific function for inserting multiple rows into a database at once. It’s obvious that if you need to insert over 10 rows — you should really be doing it in as few queries as possible. There have been some blog posts asking about this on the CI forums. It’s not a tough problem to solve by any stretch — but I wanted to write an extension of the Model class with a function to do this as quickly and efficiently as possible.

Right now I’m working on a webapp where I need to allow one user to send a message to potentially thousands of other users. That means I’ll need thousands of database inserts. Doing this with individual queries could take minutes. Inserting them in bulk would take under half of a second.

The code that I wrote has been tested and benchmarked to be as fast as I could possibly make it. I used PHP’s built in array_walk functions (which are written in C) over my own. A few other optimizations are present as well.

I posted this code over on the CodeIgniter wiki too.

The first thing you need to do is extend the model class (if it isn’t already extended), with the following code:

class MY_Model extends Model {
 
    function MY_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 containing 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 escape data
     *  that will be inserted. Default = true.
     * @author Kenny Katzgrau 
     */
    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 */
        $length = count($rows);
        for($i = 0; $i < $length; $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`";
    }
}

That file, My_Model.php, should be placed in your system/application/libraries folder. Next, you need to modify the model which needs to insert multiple rows. In my case, I want my Message model to be able to insert a lot of messages, so I’ll add ‘extends MY_Model’ to its class definition.

Note: The MY_ prefix is the default prefix used to extend a class in CodeIgniter. If you have modified this in your config.php, use your prefix as appropriate.

This is my new model, which contains an example usage of the method (but real usage would likely be a little more involved).

class Messages extends MY_Model 
{   
  /* Code .. */   
  function InsertTestData()   
  {     
    /* Prepare some fake data (10000 rows, 40,000 values total) */
    $rows = array_fill(0, 10000, array(34239, 102438, "Test Message!", '2009-12-12'));
    $columns = array('to_user_id', 'from_user_id', 'message', 'created');
    $this->insert_rows('messages', $columns, $rows); 
  }
}

The insert_rows method will automatically escape your data for the query, which tends to take up a healthy portion of this script’s running time. After all, it has to check 40,000 values, and escape 10,000!

If you need to insert something like 10,000 or 20,000 rows, it is recommended that you page through the values you are inserting. Otherwise, you run the risk of exceeding memory limits, or MySQL’s maximum packet size.

This entry was posted in Uncategorized. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.
  • Lilwiki

    Thanks Kenny that was very helpful.

  • http://syabac.student.ipb.ac.id syabac

    Thanks for sharing,
    but i think escape_value and prepare_column methods
    should be dependent. so, they should

    db->escape($value);
    }

    function prepare_column_name(&$name)
    {
    $name = $this->db->protect_identifiers($name);
    }

    ?>

  • http://syabac.student.ipb.ac.id syabac

    oops,
    here is the right code:
    function escape_value(&$value){
    $value = $this->db->escape($value);
    }

    function prepare_column_name(&$name){
    $name = $this->db->protect_identifiers($name);
    }

  • Yaje

    Need an update for this for CI version 2.0