How Can CodeIgniter's Model Class Optimize Bulk Database Inserts?

Bulk Insert using CodeIgniter's Model Class

Inserting large amounts of data into a database can be a time-consuming process. Traditionally, one approach involves executing a series of individual insert commands, which can be inefficient and resource-intensive.

To optimize this process, it's possible to insert multiple rows at once using a single query. In CodeIgniter, this can be achieved using the Model class.

Constructing the Query

The key to inserting multiple rows with a single query lies in assembling the SQL statement correctly. Instead of appending each value to a long string, you can use the array assignment and implode functions.

$data = [
    ['text' => 'foo', 'category_id' => 1],
    ['text' => 'bar', 'category_id' => 2],
    // ...
];

$sql = [];

foreach ($data as $row) {
    $sql[] = '("' . mysql_real_escape_string($row['text']) . '", ' . $row['category_id'] . ')';
}

$sql = implode(',', $sql);

Executing the Query

Once the SQL statement is constructed, you can execute it using the Model class's query method:

$this->db->query('INSERT INTO table (text, category_id) VALUES ' . $sql);

Benefits of Bulk Insert

Using bulk insert techniques offers several advantages:

• Reduced Execution Time: Combining multiple inserts into a single query significantly reduces database overhead and execution time.
• Improved Memory Usage: Avoids creating a long concatenated string, which can consume excessive memory.
• Simplified Code: Consolidates multiple insert statements into a single, more manageable query.

The above is the detailed content of How Can CodeIgniter's Model Class Optimize Bulk Database Inserts?. For more information, please follow other related articles on the PHP Chinese website!