How to store Multidimensional array in Database - using laravel 5.8

Question

I want to save students course information into database courses table. Student can add multiple course, class time and section at a time using a form. Here is details of courses table:

**id, 
nsu_id
course,
section,
class_start,
class_end**

Form (View)

            <form method="POST" action="/store">
            @csrf
            {{-- clone start from here --}}  
                <div class="parent">
                    <div id="form-set">
                        <div class="form-group row">
                            <label for="course" class="col-md-4 col-form-label text-md-right">{{ __('Course & Section') }}</label>

                            <div class="col-md-3">
                                <select type="text" class="form-control" name="course[]">
                                    <option>Course</option>
                                    <option>cse 115</option>
                                    <option>cse 215</option>
                                    <option>cse 311</option>
                                    <option>cse 411</option>
                                </select>
                            </div>
                            <div class="col-md-3">
                                <select type="number" class="form-control" name="section[]">
                                    <option>Section</option>
                                    <option>1</option>
                                    <option>2</option>
                                    <option>3</option>
                                    <option>4</option>
                                </select>
                            </div>
                        </div>

                        <div class="form-group row">
                            <label for="Class Timing" class="col-md-4 col-form-label text-md-right">{{ __('Class Timing') }}</label>

                            <div class="col-md-3">
                                <select type="text" class="form-control" name="class_start[]">
                                    <option>Start</option>
                                    <option>08:00 AM</option>
                                    <option>09:40 AM</option>
                                    <option>11:20 AM</option>
                                    <option>01:00 PM</option>
                                    <option>02:40 PM</option>
                                    <option>04:20 PM</option>
                                </select>
                            </div>
                            <div class="col-md-3">
                                <select type="text" class="form-control" name="class_end[]" >
                                    <option>End</option>
                                    <option>9:30</option>
                                    <option>11:10</option>
                                    <option>12:50</option>
                                    <option>2:30</option>
                                    <option>4:10</option>
                                    <option>5:50</option>
                                </select>
                            </div>
                        </div>

                    </div>
                </div>
                <div class="form-group row justify-content-center"  style="margin-top: -11%">
                    <div class="col-md-10" style="visibility: hidden"></div>
                    <div class="col-md-2">
                        <button class="btn btn-xs btn-success" id="add">Add</button>
                    </div>
                </div>
                <script type="text/javascript">
                    $(document).ready(function () {
                        $('#add').click(function(e) {
                            e.preventDefault();
                            $("#form-set").clone().appendTo(".parent");
                        });
                    });
                </script>
           {{-- Clone end here --}}

                <div class="form-group row">
                    <label for="nsu_id" class="col-md-4 col-form-label text-md-right">{{ __('NSU ID') }}</label>

                    <div class="col-md-6">
                        <input type="number" required class="form-control" name="nsu_id"  placeholder="Exp. 1520836042">
                    </div>
                </div>

                <div class="form-group row mb-0">
                    <div class="col-md-6 offset-md-4">
                        <button type="submit" class="btn btn-primary">
                            {{ __('Register') }}
                        </button>
                    </div>
                </div>
            </form>

Model

class Course extends Model
{
  protected $table='courses';
  protected $fillable=[
    'nsu_id','course','section','class_start','class_end'
  ];

}

Migration

public function up()
{
    Schema::create('courses', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('nsu_id');
        $table->string('course');
        $table->integer('section');
        $table->string('class_start');
        $table->string('class_end');
        $table->timestamps();
    });
}

This is what I have done to controller and i am getting Array to string conversion exception.

    public function store(Request $request)
    {
     $data[]=$request->all();
     //dd($data);
     $courseData=[];
     foreach($data as $data){
         $pointData[]=[
             'nsu_id'=>$data['nsu_id'],
             'course'=>$data['course'],
             'section'=>$data['section'],
             'class_start'=>$data['class_start'],
             'class_end'=>$data['class_end'],
         ];
     }
     DB::table('courses')->insert($courseData);

     return redirect()->to('/home');
    }

When i am trying get the value using helper function dd($data);

    array:1 [▼
      0 => array:6 [▼
        "_token" => "2AspZiawULQy4fMikkBTRzjvUxPVnZPPonJrZAUy"
        "course" => array:2 [▼
          0 => "cse 115"
          1 => "cse 215"
        ]
        "section" => array:2 [▼
          0 => "1"
          1 => "2"
        ]
        "class_start" => array:2 [▼
          0 => "08:00 AM"
          1 => "09:40 AM"
        ]
        "class_end" => array:2 [▼
          0 => "9:30"
          1 => "11:10"
        ]
        "nsu_id" => "1520836042"
      ]
    ]

Error: Array to string conversion (SQL: insert into courses (class_end, class_start, course, nsu_id, section) values (9:30, 08:00 AM, cse 115, 1520836042, 1))

Any kind of help will be highly appreciated

Answer 1

After taking a lot of time, i am successfully solve my own problem in a different way. Thanks @mchljams for all his kind assistance and cooperation.

            $nsu_id=$request->nsu_id;
            if (count($request->course)>0) {
                foreach($request->course as $item => $value)
                    $data[$value]=array(
                        'nsu_id'=>$nsu_id,
                        'course'=>$request->course[$item],
                        'section'=>$request->section[$item],
                        'class_start'=>$request->class_start[$item],
                        'class_end'=>$request->class_end[$item],
                    );
                    Course::insert($data);
            }

Answer 2

I would recommend looking into Laravel's Eloquent ORM. From the Eloquent documentation:

"Each database table has a corresponding "Model" which is used to interact with >that table. Models allow you to query for data in your tables, as well as >insert new records into the table."

$course = new Course();
$course->nsu_id = $request->nsu_id
$course->course = $request->course
$course->section = $request->->section
$course->class_start = $request->class_start
$course->class_end = $request->class_end
$course->save();

or if you configure to allow mass assignment

Course::create($request);

Also, you should have a look at Validation on that incoming request to ensure your users are not making mistakes, or some bad actor is not trying to compromize your application.

EDIT 1

Per the comments, you don't want to try to pursue the Eloquent solution. $data['course'], is an array. Since your database, as defined in your schema expects a string, that will be a problem. So here are some ideas on how to convert your array data to strings.

Comma Separated via implode()

foreach($data as $data){
         $pointData[]=[
             'nsu_id'=>$data['nsu_id'],
             'course'=>implode(',',$data['course']),
             'section'=>implode(',',$data['section']),
             'class_start'=>implode(',',$data['class_start']),
             'class_end'=>implode(',',$data['class_end']),
         ];
     }

JSON String via json_encode()

foreach($data as $data){
         $pointData[]=[
             'nsu_id'=>$data['nsu_id'],
             'course'=json_encode($data['course']),
             'section'=>json_encode($data['section']),
             'class_start'=>json_encode($data['class_start']),
             'class_end'=>json_encode($data['class_end']),
         ];
     }

Edit 2

In the comments you asked how to do mupltiple inserts

Inserting Multiple Rows into the Database

Have a look at Laravel's DB Insert Documentation, but here is the example from that page:

DB::table('users')->insert([
    ['email' => 'taylor@example.com', 'votes' => 0],
    ['email' => 'dayle@example.com', 'votes' => 0]
]);

Answer 3

First of all, the foreach in controller is pretty strange. why? :

foreach $data as $data

AND, leave $courseData empty and give it to insert.

Fixed version:

public function store(Request $request)
{
    $data[]=$request->all();
    $courseData=[];
    foreach($data as $d){
       $courseData[]= [
          'nsu_id'=> $d['nsu_id'],
          'course'=> $d['course'],
          'section'=> $d['section'],
          'class_start'=> $d['class_start'],
          'class_end'=> $d['class_end'],
        ]
    }
    DB::table('courses')->insert($courseData);

    return redirect()->to('/home');
}

The error you have is about giving the insert method an empty array as input instead of a string.

After fixing this problem you will realize that your code still doesn't work because your post values are also an array, you must think again about the input names of your form and reconstructing your data for easier and more usable logical form.