Insert multiple data into database in Yii 2

Question

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.

you can use batchInsert() to insert multiple records at a time. For more details: http://www.yiiframework.com/doc-2.0/yii-db-command.html#batchInsert%28%29-detail — Chinmay Waghmare, Mar 26 '15 at 06:17

score 12 · Answer 1 · edited Jan 24 '19 at 06:41

Create an 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]
   ];
}

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 may be helpful.

This works fine. You can get tableName using YourModelName::getTableSchema()->name — Ludo, Apr 03 '22 at 00:51

Jørgen · Answer 2 · 2015-03-26T08:37:53.633

3

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

edited Mar 26 '15 at 08:37

answered Mar 26 '15 at 06:45

Jørgen

3,467
6
33
49

Not when you use Yii's db command. http://www.yiiframework.com/doc-2.0/yii-db-command.html - Using an object in a loop would be very inefficient and a terrible solution, especially for potentially large amounts of data. Your really going to load an object 1,000 times in a loop to import 1,000 items? Here is a side-by-side breakdown showing the differences and uses: http://www.bsourcecode.com/yiiframework2/insert-query/ - To the op, look at the batch insert as @Kailas answered. For really large imports, split the data into batches of say 500/loop. – Wade Apr 30 '15 at 05:46
Question: One of the API's I am using (which is awful) returns all records (about 3,000 in total) - i want to do a batch upload but only of new records and altered records, how would i do that without a foreach loop? – DrBorrow Aug 23 '16 at 14:29

score 2 · Answer 3 · edited Mar 26 '15 at 08:40

2

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.

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

edited Mar 26 '15 at 08:40

topher

14,790
7
54
70

answered Mar 26 '15 at 06:55

kammy

352
2
10

isn't it going to shortened our store life? cz if i have 250000 data then i need to write 250000 times to my storage, rather than just write one time with bigger size to write? or is it will gave the same result to our storage?? – Ryan Arief May 26 '16 at 18:26

score 1 · Answer 4 · answered Feb 02 '18 at 17:42

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.

score 0 · Answer 5 · answered Mar 29 '18 at 19:14

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');

score 0 · Answer 6 · edited Nov 13 '20 at 10:39

0

foreach ($data as $key=>$value) {
   $model = new ModelNeme(); //optional
   $model->route = $_POST['name'][$key];
   $model->save();
}
return $this->redirect('index');

edited Nov 13 '20 at 10:39

BDL

21,052
22
49
55

answered Nov 13 '20 at 04:12

code-droid

190
12

Insert multiple data into database in Yii 2

6 Answers6

Linked

Related