How to save multiple data with different number of data at same time?

Question

I have cv table , user table and share table. Structure of share table shareto_id,cv_id,user_id

There can be any number of shareto_id and cv_id. Suppose i want to save (1) shareto_id and (2)cv_id. But it only saves one data in database. these are the code i have already tried.

$cv_id = explode(",",$cvids);
$shareto_id = explode(",",$sharetoids);
$cvlength = count($cv_id);
$sharetolength = count($shareto_id);
for($i=0; $i<$cvlength; $i++)
{
  for($j=0; $j<$sharetolength; $j++)
  {
     $data['cv_id'] = $cv_id[$i];
     $data['user_id'] = $request->user_id;
     $data['shareto_id']= $shareto_id[$j];
  }
}    
Share::create($data);

I think what you are asking is, Why when you create a share share is only the user_id being set? — Michael Mano, Nov 01 '19 at 06:51
@MichaelMano I want to save multiple data in database at the same time but only saves one. — A4family, Nov 01 '19 at 06:58

score 1 · Answer 1 · answered Nov 01 '19 at 07:06

$data is only a single array with 3 elements. Every iteration of that loop you are reassigning $data['cv_id'], $data['user_id'], $data['shareto_id'] so the previous values are gone.

You need to be keeping the previous values by creating new arrays of elements to add to a main collection:

$data = [];

for (...) {
    for (...) {
        $data[] = [
            'cv_id' => $cv_id[$i],
            'user_id' => $request->user_id,
            'shareto_id' => $shareto_id[$j],
        ];
    }
}

The create method on Eloquent Builder is for creating 1 new model instance and saving it not for many.

If you want to do a mass insert and partially bypass Eloquent you can use Query Builder's insert method.

Share::insert($data);

score 1 · Accepted Answer · answered Nov 01 '19 at 07:07

1

If I correctly understand your question, you should move create into loop:

for ($i = 0; $i < $cvlength; $i++) {
    for ($j = 0; $j < $sharetolength; $j++) {
        $data['cv_id']      = $cv_id[$i];
        $data['user_id']    = $request->user_id;
        $data['shareto_id'] = $shareto_id[$j];
        Share::create($data);
    }
}

Or use insert as described here

How to insert multiple rows from a single query using eloquent/fluent

answered Nov 01 '19 at 07:07

Roman Meyer

2,634
2
20
27

yes, but if you have a lot of data, best way to use @lagbox's answer, cause `insert` will query DB once, and `create` once per each piece of data – Roman Meyer Nov 01 '19 at 07:31

score 0 · Answer 3 · answered Nov 01 '19 at 07:32

Make sure loop format the data in an array as shown below

$data = array(
    array('cv_id'=> 2, 'user_id'=> 1, 'shareto_id'=> 4),
    array('cv_id'=> 5, 'user_id'=> 3, 'shareto_id'=> 9),
    //...
);

Model::insert($data);

So in your case, the code should look like this

$data = [];
for($i=0; $i<$cvlength; $i++)
{
  for($j=0; $j<$sharetolength; $j++)
  {
     $data[] = [
         'cv_id' => $cv_id[$i],
         'user_id' => $request->user_id,
         'shareto_id'=> $shareto_id[$j]
     ];
  }
}    
Share::insert($data);

score 0 · Answer 4 · answered Nov 01 '19 at 08:53

Don't insert new record using in loop, We can build raw insert query like

insert into "Table_name" ('cv_id','user_id','shareto_id')VALUES (cv_id_value_1, user_id_value_1, shareto_id_1) (cv_id_value_2, user_id_value_2, shareto_id_2)`

just one query to batch insert

How to save multiple data with different number of data at same time?

4 Answers4