insert multiple rows via a php array into mysql

Question!

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



Answers

You can do it with several ways in codeigniter e.g.

First By loop

foreach($myarray as $row)
{
   $data = array("first"=>$row->first,"second"=>$row->sec);
   $this->db->insert('table_name',$data);
}

Second -- By insert batch

$data = array(
       array(
          'first' => $myarray[0]['first'] ,
          'second' => $myarray[0]['sec'],
        ),
       array(
          'first' => $myarray[1]['first'] ,
          'second' => $myarray[1]['sec'],
        ),
    );

    $this->db->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));


Okay so I found this post I know this quite old but it does give me some ideas. but I do have some problem implementing this. Here's a snippet of what i did

//did some querying here
while($rv=mysql_fetch_array($rs))
{
   // did some stuff here
   if($classid <> '37005')
   {
       $arr = array("id"=>$id,
                    "date"=>$reportingDate,
                    "name"=>$custname,
                    "branch"=>$branch,
                    "account"=>$account,
                    "amount"=>$amount,
                    "balance"=>$balance);
       //as to requirements are to be inserted in the db under a new table in this format:
//id ----------branch -------account ------date ---- ------field --- value
//01 ----------B1 -----------001210 -------2016-02-29----name ------$custname
//01 ----------B1 -----------001210 -------2016-02-29----amount ----25000.00
//01 ----------B1 -----------001210 -------2016-02-29----balance ---16000.00
//and so on until all the data from array $arr are inserted
//i assigned it into another array
    foreach($arr as $k=>$v)  
    {
        $query[] = '("'.$id.'", "'.$branch.'","'.$account.'","'.$reportingDate.'","'.$k.'","'.$v.'")';
    }                         
   }
}
begin();
     mysql_query('Insert into tbl (id,branch,account_no,date,field,value) VALUES  '.implode(',', $query)) or die (mysql_error());
      var_dump($query);
     commit();

//no error but only the last set is inserted in the db. All of the previous data inserted into the ARRAY are gone but on the console all the data is there only in the db

I tried this because I need to perform batch insert. there are around 5000 rows with 54 columns from one report page that needs to be inserted into a different table in which the columns becomes the rows. Above is just a sample so that you guys can have an overview of what I am trying to accomplish. My problem now is that only the last row of the report is inserted which means instead of inserting 270,000 rows into the new table only 54 is inserted :( can't get this working properly.



Use insert batch in codeigniter to insert multiple row of data.

$this->db->insert_batch('tabname',$data_array); // $data_array holds the value to be inserted
By : aish


This video can help you solving your question :)
By: admin