Insert multiple data into database in Yii 2

问题:

I have problem with my code when i'm trying to save multiple data into database at the same time, this is my code to save into database:

foreach ($data as $value) {
   $model->route = $value[0][1];
   $model->begin_point = $value[0][2];
   $model->begin_point = $value[0][3];
   $model->save();
}
return $this->redirect('index');

every i'm trying to save, i'm only get the last data array can save into database. could someone help me? or if someone could provide a tutorial, that would be a real help.

回答1:

1. Create a array by looping your multiple values.

   $data- has multiple values
   $bulkInsertArray = array();
   foreach($data as $value){
      $bulkInsertArray[]=[
          'columnName1'=>$value[0][1],
          'columnName2'=>$value[0][2],
          'columnName3'=>$value[0][3]
      ];
   }
   

2. Check $bulkInsertArray in not empty

   if(count($bulkInsertArray)>0){
       $columnNameArray=['columnName1','columnName2','columnName3'];
       // below line insert all your record and return number of rows inserted
       $insertCount = Yii::$app->db->createCommand()
                      ->batchInsert(
                            $tableName, $columnNameArray, $bulkInsertArray
                        )
                      ->execute();
   }
   

Hope these code is help full.

回答2:

You have to create a New object of the model each time. Or Else youre Just overwriting.

回答3:

1. You can use Yii command builder to achieve this.

$command = Yii::app()->db->createCommand();

$command->insert('table_name',array('column_1'=>$value_1),
'column_2'=>$value_2));

and so on.

2. Write this code in loop and it will insert all records one after another.

回答4:

You can use Yes It Is Batch Insert to insert multiple rows. It is faster than any of the ways stated here :

$connection->createCommand()->batchInsert('table_name', ['table_column_1', 'table_column_2'], [
    ['column_1_data_a', 'column_2_data_a'],
    ['column_1_data_b', 'column_2_data_b'],
    ['column_1_data_c', 'column_2_data_c'],
])->execute();

Check the link for this.

回答5:

I think batch insert is the best solution for this problem.

but there is one problem with your pic of code that is this code will create only one data row(first row) in data table and update to that same model

solution for your code is

 foreach ($data as $value) {
    $model = new Model(); // creating new instance of model 
    $model->route = $value[0][1];
    $model->begin_point = $value[0][2];
    $model->begin_point = $value[0][3];
    $model->save();
  }
  return $this->redirect('index');