How Can I Efficiently Insert Multiple Rows into a MySQL Database Using PHP and CodeIgniter?

Efficient Insertion of Multiple Rows Using PHP and CodeIgniter

When inserting large datasets into a MySQL table using PHP, it's essential to optimize the process for performance and efficiency. Instead of appending each row value into a single string and executing it, a more efficient approach is to insert multiple rows simultaneously using a single query.

CodeIgniter's Approach

CodeIgniter framework provides an array-based insert function, insert_batch(), that simplifies this process. It allows you to pass an array of data, each element representing a row to be inserted, into a single query execution. Here's an example:

$insert_data = array(
    array(
        'name' => 'John Doe',
        'email' => 'johndoe@example.com'
    ),
    array(
        'name' => 'Jane Doe',
        'email' => 'janedoe@example.com'
    )
);

$this->db->insert_batch('users', $insert_data);

This code will insert both rows into the users table in a single query, providing a significant performance improvement over iteratively inserting each row.

Avoiding String Handling Issues

When dealing with large datasets, it's crucial to avoid string concatenation or appending values directly into the SQL statement as it can lead to memory and performance issues. Instead, utilize functions like array_map() or implode() to efficiently assemble the SQL query.

For instance:

$data = array(
    array(
        'text' => 'Row 1',
        'category_id' => 1
    ),
    array(
        'text' => 'Row 2',
        'category_id' => 2
    )
);

$sql = array();

foreach ($data as $row) {
    $sql[] = '(' . $this->db->escape($row['text']) . ', ' . $row['category_id'] . ')';
}

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

This approach allows you to construct the SQL statement incrementally, avoiding unnecessary copying and string manipulations.

The above is the detailed content of How Can I Efficiently Insert Multiple Rows into a MySQL Database Using PHP and CodeIgniter?. For more information, please follow other related articles on the PHP Chinese website!