DB Transactions query looping alternative

Hi,

Version: 5.4

Method: Query Builder after storing the .csv as an array $headers for columns, $data_rows for the data

Aim: I want to add users phone numbers via a .csv file to my database.

Problem: Is there a better method other than looping through each row?

The entries can be up to 5000 users which results in 5000 insert queries. To ensure all are added at a go I have setup the insert process to run within a transaction.

Below is a simplified version of the code:

DB::transaction(function() use ($request, $headers, $data_rows) {

foreach ($data_rows as $row) { // loop through each row

    DB::table('user_phone_numbers')->insert(
                    [
                        'user_id' => $row[$user_index_from_csv_here], 
                        'number' => $row[$user_number_from_csv_here], 
                        'created_at' => date("Y-m-d H:i:s"), // now
                        'updated_at' => date("Y-m-d H:i:s"), // now
                    ]
                );

} // end foreach

});

lostdreamer_nl

7 years ago

Best Answer

Level 53

You can do bulk inserts with insert() as well:

$batches = array_chunk($data_rows, 200);  // batches of 200 each

DB::transaction(function() use ($request, $headers, $batches ) {
    foreach($batches as $batch) {
        foreach($batch as $i => $row) {
            $batch [ $i ] = [
                'user_id' => $row[$user_index_from_csv_here], 
                'number' => $row[$user_number_from_csv_here], 
                'created_at' => date("Y-m-d H:i:s"), // now
                'updated_at' => date("Y-m-d H:i:s"), // now
            ];
        }

        DB::table('user_phone_numbers')->insert($batch);
    }
});

pinakmithaiwala

7 years ago

Level 1

using Eloquent

$data = array( array('user_id'=>'1', 'name'=> '1'), array('user_id'=>'2', 'name'=> '2), //... );

Model::insert($data);

ochirach

7 years ago

Level 1

Great. Had a detailed look at bulk inserts and it seems to be the better option

Please or to participate in this conversation.