How to insert multiple rows from array using CodeIgniter framework?

by

Assembling one INSERT statement with multiple rows is much faster in MySQL 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.

$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.

More Related Contents:

  1. how to select only one value sql
  2. How to create codeigniter dynamic menu from single table
  3. Comma separated numeric values – PHP CodeIgniter
  4. Using PHP read word in textarea line by line and insert to mySQL
  5. CodeIgniter – foreach loop
  6. PHP – Change value y,n in database [closed]
  7. strange character encoding of stored data , old script is showing them fine new one doesn’t
  8. PHP mysql insert date format
  9. CodeIgniter: Unable to connect to your database server using the provided settings Error Message
  10. Incorrect Integer (2147483647) is inserted into MySQL?
  11. Escaping single quote in PHP when inserting into MySQL [duplicate]
  12. PHP/MySQL Insert null values
  13. MySQL – ignore insert error: duplicate entry
  14. Find total number of results in mySQL query with offset+limit
  15. Object of class DateTime could not be converted to string
  16. How can I Use Prepared Statements in CodeIgniter
  17. Grouping WHERE clauses in Codeigniter
  18. What are the differences between backtick and single quote? Can I use IF statement in a query as above?
  19. How to create Codeigniter language files from database?
  20. Trying to get property of non-object – CodeIgniter
  21. php run once and insert twice in mysql database
  22. Codeigniter Transactions
  23. Inserting NOW() into Database with CodeIgniter’s Active Record
  24. How to select rows where column value IS NOT NULL using CodeIgniter’s ActiveRecord?
  25. PHP mySQL – Insert new record into table with auto-increment on primary key
  26. Replacing \r\n with PHP
  27. Escaping SQL queries in Codeigniter
  28. php+mysql Fatal error: Call to undefined function mysql_connect()
  29. Special characters in PHP / MySQL
  30. Convert Time String to Decimal Hours PHP [closed]

Leave a Comment