How to insert multiple rows from array using CodeIgniter framework

Question

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

User · Answer

I had to INSERT more than 14000 rows into a table and found that line for line with Mysqli prepared statements took more than ten minutes, while argument unpacking with string parameters for the same Mysqli prepared statements did it in less than 10 seconds. My data was very repetitive as it was multiples of id's and one constant integer.

10 minutes code:

            $num = 1000;
            $ent = 4;
            $value = ['id' => 1,
                      'id' => 2,
                      'id' => 3,
                      'id' => 4,
                      'id' => 5,
                      'id' => 6,
                      'id' => 7,
                      'id' => 8,
                      'id' => 9,
                      'id' => 10,
                      'id' => 11,
                      'id' => 12,
                      'id' => 13,
                      'id' => 14];
            $cnt = 0;
            $query = "INSERT INTO table (col1, col2) VALUES (?,?)";
            $stmt = $this->db->prepare($query);
            $stmt->bind_param('ii', $arg_one,$arg_two);
                    foreach ($value as $k => $val) {
                         for ($i=0; $i < $num; $i++) { 
                            $arg_one = $k;
                            $arg_two = $ent;
                            if($stmt->execute()) {
                                $cnt++;
                            }
                        }
                    }

10 second code:

            $ent = 4;
            $num = 1000;
            $value = ['id' => 1,
                      'id' => 2,
                      'id' => 3,
                      'id' => 4,
                      'id' => 5,
                      'id' => 6,
                      'id' => 7,
                      'id' => 8,
                      'id' => 9,
                      'id' => 10,
                      'id' => 11,
                      'id' => 12,
                      'id' => 13,
                      'id' => 14];
             $newdat = [];
             foreach ($value as $k => $val) {
                 for ($i=0; $i < $num; $i++) {
                    $newdat[] = $val;
                    $newdat[] = $ent;
                 }
             }
            // create string of data types
            $cnt = count($newdat);
            $param = str_repeat('i',$cnt);
            // create string of question marks
            $rec = (count($newdat) == 0) ? 0 : $cnt / 2 - 1;
            $id_q = str_repeat('(?,?),', $rec) . '(?,?)';
            // insert
            $query = "INSERT INTO table (col1, col2) VALUES $id_q";
            $stmt = $db->prepare($query);
            $stmt->bind_param($param, ...$newdat);
            $stmt->execute();

User · Answer

I have created this simple function which you guys can use easily  You will need to pass the table-name   tbl   table-field   insertFieldsArr  against your inserting data   data array   arr    insert batch  table  array  field1   field2    dataArray        function insert batch  tbl  insertFieldsArr  arr    sql   array         foreach   arr as  row              strVals              cnt 0          foreach  insertFieldsArr as  key  gt  val               if is array  row                     strVals      mysql real escape string  row  cnt                                    else                   strVals      mysql real escape string  row                                    cnt                       strVals rtrim  strVals                sql          strVals                  fields implode      insertFieldsArr       mysql query  INSERT INTO     tbl        fields    VALUES   implode       sql

User · Answer

mysqli in PHP 5 is an object with some good functions that will allow you to speed up the insertion time for the answer above   mysqli- gt autocommit FALSE    mysqli- gt multi query  sqlCombined    mysqli- gt autocommit TRUE    Turning off autocommit when inserting many rows greatly speeds up insertion  so turn it off  then execute as mentioned above  or just make a string  sqlCombined  which is many insert statements separated by semi-colons and multi-query will handle them fine

User · Answer

I have created a class that performs multi-line that is used as follows    pdo- gt beginTransaction     pmi   new PDOMultiLineInserter  pdo   foo   array  a   b   c   e    10    pmi- gt insertRow  data            pmi- gt insertRow  data    pmi- gt purgeRemainingInserts     pdo- gt commit      where the class is defined as follows   class PDOMultiLineInserter       private   purgeAtCount      private   bigInsertQuery    singleInsertQuery      private   currentlyInsertingRows    array        private   currentlyInsertingCount   0      private   numberOfFields      private   error      private   insertCount   0                  Create a PDOMultiLine Insert object                 param PDO  pdo              The PDO connection         param type  tableName       The table name         param type  fieldsAsArray   An array of the fields being inserted         param type  bigInsertCount  How many rows to collect before performing an insert              function   construct PDO  pdo   tableName   fieldsAsArray   bigInsertCount   100             this- gt  numberOfFields   count  fieldsAsArray            insertIntoPortion    REPLACE INTO   tableName      implode         fieldsAsArray      VALUES            questionMarks          str repeat        this- gt  numberOfFields - 1                 this- gt  purgeAtCount    bigInsertCount           this- gt  bigInsertQuery       pdo- gt prepare  insertIntoPortion  questionMarks str repeat       questionMarks   bigInsertCount - 1             this- gt  singleInsertQuery    pdo- gt prepare  insertIntoPortion  questionMarks              function insertRow  rowData                todo Compare speed             this- gt  currentlyInsertingRows   array merge  this- gt  currentlyInsertingRows   rowData           foreach  rowData as  v  array push  this- gt  currentlyInsertingRows   v                      if     this- gt  currentlyInsertingCount     this- gt  purgeAtCount                if   this- gt  bigInsertQuery- gt execute  this- gt  currentlyInsertingRows      FALSE                     this- gt  error    Failed to perform a multi-insert  after   this- gt  insertCount  inserts   the following errors occurred   implode   lt br  gt     this- gt  bigInsertQuery- gt errorInfo                     return false                             this- gt  insertCount                  this- gt  currentlyInsertingCount   0               this- gt  currentlyInsertingRows   array                      return true             function purgeRemainingInserts             while   this- gt  currentlyInsertingCount  gt  0                 singleInsertData   array                    todo Compare speed - http   www evardsson com blog 2010 02 05 comparing-php-array shift-to-array pop                 for   i   0   i  lt   this- gt  numberOfFields   i     singleInsertData     array pop  this- gt  currentlyInsertingRows   array reverse  singleInsertData               for   i   0   i  lt   this- gt  numberOfFields   i        array unshift  singleInsertData  array pop  this- gt  currentlyInsertingRows                 if   this- gt  singleInsertQuery- gt execute  singleInsertData      FALSE                     this- gt  error    Failed to perform a small-insert  whilst purging the remaining rows  the following errors occurred   implode   lt br  gt     this- gt  singleInsertQuery- gt errorInfo                     return false                             this- gt  currentlyInsertingCount--                       public function getError             return  this- gt  error

User · Answer

Although it is too late to answer this question  Here are my answer on the same  If you are using CodeIgniter then you can use inbuilt methods defined in query builder class   this- gt db- gt insert batch   Generates an insert string based on the data you supply  and runs the query  You can either pass an array or an object to the function  Here is an example using an array   data   array      array               title    gt   My title                name    gt   My Name                date    gt   My date             array               title    gt   Another title                name    gt   Another Name                date    gt   Another date             this- gt db- gt insert batch  mytable    data      Produces  INSERT INTO mytable  title  name  date  VALUES   My title    My name    My date       Another title    Another name    Another date    The first parameter will contain the table name  the second is an associative array of values  You can find more details about query builder here

User · Answer

Use insert batch in codeigniter to insert multiple row of data    this- gt db- gt insert batch  tabname   data array       data array holds the value to be inserted

User · Answer

You could always use mysql s LOAD DATA   LOAD DATA LOCAL INFILE   full path to file foo csv  INTO TABLE  footable  FIELDS TERMINATED BY     LINES TERMINATED BY   r n     to do bulk inserts rather than using a bunch of INSERT statements

User · Answer

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

User · Answer

You could prepare the query for inserting one row using the mysqli stmt class  and then iterate over the array of data   Something like    stmt     db- gt stmt init     stmt- gt prepare  INSERT INTO mytbl  fld1  fld2  fld3  fld4  VALUES                foreach  myarray as  row         stmt- gt bind param  idsb    row  fld1     row  fld2     row  fld3     row  fld4          stmt- gt execute       stmt- gt close      Where  idsb  are the types of the data you re binding  int  double  string  blob

User · Answer

Multiple insert  batch insert is now supported by CodeIgniter   data   array     array         title    gt   My title           name    gt   My Name           date    gt   My date           array         title    gt   Another title           name    gt   Another Name           date    gt   Another date            this- gt db- gt insert batch  mytable    data       Produces  INSERT INTO mytable  title  name  date  VALUES   My title    My name    My date      Another title    Another name    Another date

User · Answer

You can do it with several ways in codeigniter e g      First By loop   foreach  myarray as  row        data   array  first   gt  row- gt first  second   gt  row- gt sec       this- gt db- gt insert  table name   data          Second -- By insert batch    data   array         array             first    gt   myarray 0   first                second    gt   myarray 0   sec                      array             first    gt   myarray 1   first                second    gt   myarray 1   sec                            this- gt db- gt insert batch  table name    data        Third way -- By multiple value pass    sql   array     foreach   myarray as  row          sql          mysql real escape string  row  first           row  sec          mysql query  INSERT INTO table  first  second  VALUES   implode       sql

User · Answer

Well  you don t want to execute 1000 query calls  but doing this is fine    stmt  array   array of statements      query   INSERT INTO yourtable  col1 col2 col3  VALUES    foreach   stmt AS  k   gt   v        query         v          NOTE  you ll have to change to suit   if    k     sizeof  stmt -1    query            r  mysql query  query     Depending on your data source  populating the array might be as easy as opening a file and dumping the contents into an array via file

User · Answer

query  array     foreach   your data as  row          query          mysql real escape string  row  text           row  category id          mysql query  INSERT INTO table  text  category  VALUES   implode       query

[php] How to insert multiple rows from array using CodeIgniter framework?

Examples related to php

Examples related to mysql

Examples related to codeigniter

Examples related to insert

Examples related to bulkinsert