How to insert multiple rows from array using CodeIgniter framework?

I'm passing a large dataset into a MySQL table via PHP using insert commands and I'm wondering if it's possible to insert approximately 1000 rows at a time via a query other than appending each value on the end of a mile-long string and then executing it. I am using the CodeIgniter framework so its functions are also available to me.

Do you mean like this?
https://www.codeigniter.com/user_guide/d...nsertbatch

Assembling one INSERT statement with multiple rows is much Faster in MySQL Examples than one INSERT statement per row.

That said, it sounds like you might be running into string-handling problems in PHP, which is really an algorithm problem, not a language one. Basically, when working with large strings, you want to minimize unnecessary copying. Primarily, this means you want to avoid concatenation. The fastest and most memory-efficient way to build a large string, such as for inserting hundreds of rows at one, is to take advantage of the implode() function and array assignment.

Code:

$sql = array();
foreach( $data as $row ) {
    $sql[] = '("'.mysql_real_escape_string($row['text']).'", '.$row['category_id'].')';
}
mysql_query('INSERT INTO table (text, category) VALUES '.implode(',', $sql));

The advantage of this approach is that you don't copy and re-copy the SQL statement you've so far assembled with each concatenation; instead, PHP does this once in the implode() statement. This is a big win.

If you have lots of columns to put together, and one or more are very long, you could also build an inner loop to do the same thing and use implode() to assign the values clause to the outer array.

I don't recommend using mysql_* functions, this has been deprecated since PHP 5.5 and removed in PHP 7.0.
The insertBatch() is maintained and available on multiple drivers supported by CodeIgniter4.

See: https://github.com/codeigniter4/CodeIgni....php#L1737

To insert multiple rows from an array using the CodeIgniter framework, you can utilize the insert_batch() function provided by the CI_DB class. This function efficiently handles bulk data insertion, enabling you to seamlessly add multiple records to your database. Here's a step-by-step guide on how to implement this method:

1. Prepare the Data Array:

Construct an array that stores the data for each row you intend to insert into the database. Each element of the array should represent a single row, with the keys corresponding to the column names in your table. For instance, if your table has columns named id, name, and email, your data array might look like this:

PHP
$data = [
['id' => 1, 'name' => 'John Doe', 'email' => '[email protected]'],
['id' => 2, 'name' => 'Jane Doe', 'email' => '[email protected]'],
// ... Additional rows
];
Use code with caution. Learn more
2. Load the Database Class:

To access the database functionality, you need to load the database class within your controller or model file. Use the following code to load the database instance:

PHP
$this->load->database();
Use code with caution.
3. Insert Multiple Rows Using insert_batch():

Once the data array and database instance are ready, you can utilize the insert_batch() function to insert multiple rows simultaneously. The function takes two arguments: the table name and the data array.

PHP
$this->db->insert_batch('users', $data);
Use code with caution.
This code will insert all the rows from the $data array into the users table. The insert_batch() function will return an integer indicating the number of rows successfully inserted.