How to inserting multiple records into mysql using query builder laravel

Question

How to inserting multiple records into mysql using query builder laravel ? i try use insert->() to insert records to mysql table, but it just inserting 1 value.i've try to read some article about inserting multiple records, but its not just like i want

my controller

public function postBuy(Request $request)
{
$data = array();
$data['products_id']= $request->get('products_id');
$data['products']   = $request->get('products');
$data['price']      = $request->get('price');
$data['user_id']    = $request->get('user_id');
$data['user_name']  = $request->get('user_name');
$data['user_email'] = $request->get('user_email');

$query_insert = DB::table('cashier')->insert($data);

return redirect('index');
}

my forms

   <form method="POST" action='{{ url("buy") }}' enctype="multipart/form-data">  
      <input type="hidden" name="_token" value="{{ csrf_token() }}">
      <table class="table table-stripped">
        <thead>
          <tr>
            <th>Product</th>
            <th>Price</th>
          </tr>
        </thead>
        <tbody>
        @foreach($item as $items)
        <input type="hidden" name="products_id" class="form-control" required="required" value="{{$items->id}}" readonly="readonly">
          <tr>
            <td>
              <div class="form-group">
                <input type="text" name="products" class="form-control" required="required" value="{{$items->name}}" readonly="readonly">
              </div>                      
            </td>
            <td>
              <div class="form-group">
                <input type="text" name="price" class="form-control" required="required" value="{{$items->price}}" readonly="readonly">
              </div>
            </td>
          </tr>
        @endforeach
          <tr>
            <td>Total :</td>
            <td><?php echo number_format($total) ;?></td>
          </tr>
        </tbody>
      </table>
      <div class="form-group">
        <input type="hidden" name="user_id" class="form-control" required="required" value="{{Session::get('id')}}">
      </div>
      <div class="form-group">
        <input type="hidden" name="user_name" class="form-control" required="required" value="{{Session::get('name')}}">
      </div>
      <div class="form-group">
        <input type="hidden" name="user_email" class="form-control" required="required" value="{{Session::get('email')}}">
      </div>
      <br>
      <button type="submit" class="btn btn-success"><span class="glyphicon glyphicon-ok"></span> Buy</button>
      <button type="button" class="btn btn-danger" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span>Cancel</button>
    </form>

my records in view which will be inserting in mysql table
i want insert that 2 records into my cashier table, but it just inserting 1 records

You can use array and foreach function

Jigs1212
– Jigs1212

2018-03-26 10:11:05 +00:00
Commented Mar 26, 2018 at 10:11 — Jigs1212
– Jigs1212, Commented Mar 26, 2018 at 10:11
@Jigs1212 can you explain more specific ?

Just L
– Just L

2018-03-27 02:09:34 +00:00
Commented Mar 27, 2018 at 2:09 — Just L
– Just L, Commented Mar 27, 2018 at 2:09

Hiren Gohel · Accepted Answer · 2018-03-27 08:12:17Z

It is really easy to do a bulk insert in Laravel using Eloquent or the query builder.

You can use the following approach:

public function postBuy(Request $request)
{
   $data = array(
      array('products_id'=> $request->get('products_id'), 'products'=> $request->get('products'), 'price'=> $request->get('price'), 'user_id'=> $request->get('user_id'), 'user_name'=> $request->get('user_name'), 'user_email'=> $request->get('user_email')),
      array('products_id'=> $request->get('products_id'), 'products'=> $request->get('products'), 'price'=> $request->get('price'), 'user_id'=> $request->get('user_id'), 'user_name'=> $request->get('user_name'), 'user_email'=> $request->get('user_email'),
      //...
   );

    $query_insert = DB::table('cashier')->insert($data);
    return redirect('index');

    //For info
    Model::insert($data); // Eloquent approach
    DB::table('cashier')->insert($data); // Query Builder approach as you are using
}

EDIT :

In your case, you need to take [] in your every input field in your form! Like:

name="products_id[]", name="products[]", name="price[]"

EDIT 2 :

You can add multiple records using foreach like:

foreach ($request as $key=>$value)
{
    $data[] = [
        'products_id' => $value['products_id'],
        'products' => $value['products'],
        'price' => $value['price'],
        'user_id' => $value['user_id'],
        'user_name' => $value['user_name'],
        'user_email' =>  $value['user_email']
    ];
}

DB::table('cashier')->insert($data); // Query Builder approach as you are using

Hope this helps you!

Sérgio Reis · Accepted Answer · 2018-03-26 11:06:18Z

0

You're sending your inputs with the same name, so laravel doesn't recieve them as arrays.

For example

@foreach($item as $items)
    <input type="hidden" name="products_id[]" class="form-control" required="required" value="{{$items->id}}" readonly="readonly">
    <tr>
      <td>
        <div class="form-group">
          <input type="text" name="products[]" class="form-control" required="required" value="{{$items->name}}" readonly="readonly">
        </div>                      
      </td>
      <td>
        <div class="form-group">
          <input type="text" name="price[]" class="form-control" required="required" value="{{$items->price}}" readonly="readonly">
        </div>
      </td>
   </tr>
@endforeach

In the controller:

$data = array();
$data['products_id']= $request->get('products_id');
$data['products']   = $request->get('products');
$data['price']      = $request->get('price');
$insertData = collect($data);
$insertData->prepend($request->get('user_id'), 'user_id');
$insertData->prepend($request->get('user_name'), 'user_name');
$insertData->prepend($request->get('user_email'), 'user_email');

$query_insert = DB::table('cashier')->insert($insertData);

answered Mar 26, 2018 at 11:06

Sérgio Reis

2,5232 gold badges22 silver badges32 bronze badges

2 Comments

Sérgio Reis Over a year ago

Note the added [] in the form

Just L Over a year ago

get an error, 'Argument 1 passed to Illuminate\Database\Query\Builder::insert() must be of the type array, object given' , in controller this line '$query_insert = DB::table('cashier')->insert($insertData);'

Collectives™ on Stack Overflow

How to inserting multiple records into mysql using query builder laravel

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related