Insert array values into database in laravel

<form method="post" action="/submit_quiz" id="quiz_form" >
{{ csrf_field() }}
            <b> Questions</b>
    <div style="max-height:60%;overflow-y:auto;">

@foreach($answers as $answer)

<input type="hidden" name="name" value="{{Auth::user()->name}}">
<input type="hidden" name="category" value="{{$answer->category}}">
<input type="hidden" name="year" value="{{$answer->year}}">

<input type="hidden" name="question_id" value="{{$answer->id}}">
            <P>{{$answer->id}}.<BR>
                <select name="answer[]">
                    <option></option>
                    <option value="{{$answer->option_A}}">{{$answer->option_A}}</option>
                    <option value="{{$answer->option_B}}">{{$answer->option_B}}</option>
                    <option value="{{$answer->option_C}}">{{$answer->option_C}}</option>
                    <option value="{{$answer->option_D}}">{{$answer->option_D}}</option>
                    <option value="{{$answer->option_E}}">{{$answer->option_E}}</option>
                </select>
</p>
<br>

@endforeach
<button class="btn btn-danger"  onClick="javascript:cancelmove();" style="margin-top:2px;" >Submit Quiz</button>


    
<br>
<br>
<br>

</form>






public function submit_quiz(Request $request)
{


    $answer = new Answers;

    $data = $request->all();
     for ($i = 1; $i < count($request->answer); $i++) {
        $answers[] = [
            'username' => Auth::user()->name,
            'category' => $request->category,
            'year' => $request->year,
            'correct_answer' => $request->answer[$i],
            'question_id' => $request->question_id,
        ];
    }
    dd($answers);

Replace your code with below:

public function submit_quiz(Request $request)
{


$answer = new Answers;

$data = $request->all();
 foreach ( $request->get('answer') as $answer) {
    $answers[] = [
        'username' => Auth::user()->name,
        'category' => $request->category,
        'year' => $request->year,
        'correct_answer' => $answer,
        'question_id' => $request->question_id,
    ];
}
dd($answers);

@indpurvesh it works but when i try to submit to database i get this error

ErrorException in MySqlConnection.php line 81:
Array to string conversion

i used Answers::insert($answers); to submit to the database

this is what the dd($answers) returns

array:3 [▼
  0 => array:5 [▼
    "username" => "jimmy sanwo"
    "category" => "WAEC"
    "year" => "1988"
    "correct_answer" => "2.02"
    "question_id" => "3"
  ]
  1 => array:5 [▼
    "username" => "jimmy sanwo"
    "category" => "WAEC"
    "year" => "1988"
    "correct_answer" => "5.4 × 10^-2"
    "question_id" => "3"
  ]
  2 => array:5 [▼
    "username" => "jimmy sanwo"
    "category" => "WAEC"
    "year" => "1988"
    "correct_answer" => "2/3"
    "question_id" => "3"
  ]
]

foreach(...){

$answers = [. . ];

Answer::create($answers);

}

@amk it submits NULL to database

"question_id" => "3" for each answer instead of the actually question id as it in database

try this @jsanwo64

foreach($request->answer as $answers){

Answers::create([
 'username' => Auth::user()->name,
 'category' => $request->category,
 'year' => $request->year,
 'correct_answer' => $answers,
 'question_id' => $request->question_id,
]);
}

@amk it returns just one correct answer if i do a dd

i used Answers::insert($answers); to submit to the database

Try Answers::insert([$anwsers]);

@cronix it does not work instead i get

Undefined variable: anwsers

Ok, I misspelled it, and so did you lol.

Answers::insert([$answers]);

@jlrdw he did (scroll up)

You need to build array for each insert.

It actually needs to be an array of arrays, which is why I said to try Answers::insert([$answers]); to wrap the arrays in another array.

I have looked at this part 20 times now:

@foreach($answers as $answer)

<input type="hidden" name="name" value="{{Auth::user()->name}}">
<input type="hidden" name="category" value="{{$answer->category}}">
<input type="hidden" name="year" value="{{$answer->year}}">

<input type="hidden" name="question_id" value="{{$answer->id}}">
            <P>{{$answer->id}}.<BR>
                <select name="answer[]">
                    <option></option>
                    <option value="{{$answer->option_A}}">{{$answer->option_A}}</option>
                    <option value="{{$answer->option_B}}">{{$answer->option_B}}</option>
                    <option value="{{$answer->option_C}}">{{$answer->option_C}}</option>
                    <option value="{{$answer->option_D}}">{{$answer->option_D}}</option>
                    <option value="{{$answer->option_E}}">{{$answer->option_E}}</option>
                </select>
</p>
<br>

@endforeach

Okay this is inside a form, so you have only one long input array, you need to separate four elements at a time and iterate over and build 3 separate insert arrays.

I admit this one is confusing.

@Cronix am I seeing something wrong?

@indpurvesh answer does that but

$answer = new Answers;

and the insert needs to fall inside the controllers foreach. Missed that at first.

@cronix for

Answers::insert([$answers]);

i get the error

SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'field list' (SQL: insert into `user_answers` (`0`, `1`, `2`) values (jimmy sanwo, jimmy sanwo, jimmy sanwo))

with this

public function submit_quiz(Request $request)
{


    $answer = new Answers;

$data = $request->all();
 foreach ( $request->get('answer') as $answer) {
    $answers[] = [
        'username' => Auth::user()->name,
        'category' => $request->category,
        'year' => $request->year,
        'correct_answer' => $answer,
        'question_id' => $request->question_id,
    ];
}
 Answers::insert($answers);

the data get submitted to the database but one error i get the created_at and updated at column returns null also the question id returns the exact figure 3 for each answers submitted instead of 1,2,3 i get 3,3,3

@cronix I was going to make your suggestion at first, but then I noticed;

$answers[] = [

So it is already building an array of arrays

@jsanwo64 These lines do nothing

$answer = new Answers;

$data = $request->all();

Also, Insert is raw. As you are not iterating over eloquent models there is no opportunity to set the created_at / updated_at. You need to put these in the array.

personally, I would save one answer per loop

public function submit_quiz(Request $request)
{

 foreach ( $request->answer as $answer) {
    Answers::create([
        'username' => Auth::user()->name,
        'category' => $request->category,
        'year' => $request->year,
        'correct_answer' => $answer,
        'question_id' => $request->question_id,
    ]);
}

additionally, models should be singular by convention (Answer not Answers)

@Snapey i tried this

public function submit_quiz(Request $request)
{

 foreach ( $request->answer as $answer) {
    Answers::create([
        'username' => Auth::user()->name,
        'category' => $request->category,
        'year' => $request->year,
        'correct_answer' => $answer,
        'question_id' => $request->question_id,
    ]);
}

i get null for the following columns username, year and question_id

Are they in the model's $fillable array?

Also, add some validation. At the moment a user can submit answers for any question and for any category

@Snapey i have added them into the model $fillable array ,i will do the validation of the form once am able to test that the inputs submits successfully to database, after adding them to the model $fillable i get this error

Array to string conversion (SQL: insert into `user_answers` (`username`, `category`, `year`, `correct_answer`, `question_id`) values (jimmy sanwo, WAEC, 1988, 2020, 1))

so Answers model is using the table user_answers ??

What does the migration look like?

@Snapey yes it does, below is my my Migration

public function up()
    {
  Schema::create('user_answers', function (Blueprint $table) {
            $table->increments('id');
            $table->string('username');
            $table->string('category');
            $table->string('year');
            $table->string('question_id');
            $table->string('correct_answer');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('user_answers');
    }

I cant see anything obviously wrong.

Is that the full error message?

I would have expected question_id to be an unsigned integer so that it can be used in a relationship, and username in be instead a foreign key to the users table, but neither of these concerns should give that error

@Snapey

Whoops, looks like something went wrong.
2/2
QueryException in Connection.php line 770:
Array to string conversion (SQL: insert into `user_answers` (`username`, `category`, `year`, `correct_answer`, `question_id`) values (jimmy sanwo, WAEC, 1988, 2020, 1))
in Connection.php line 770
at Connection->runQueryCallback('insert into `user_answers` (`username`, `category`, `year`, `correct_answer`, `question_id`) values (?, ?, ?, ?, ?)', array('jimmy sanwo', 'WAEC', '1988', '2020', array('1', '2', '3')), object(Closure)) in Connection.php line 726
at Connection->run('insert into `user_answers` (`username`, `category`, `year`, `correct_answer`, `question_id`) values (?, ?, ?, ?, ?)', array('jimmy sanwo', 'WAEC', '1988', '2020', array('1', '2', '3')), object(Closure)) in Connection.php line 481
at Connection->statement('insert into `user_answers` (`username`, `category`, `year`, `correct_answer`, `question_id`) values (?, ?, ?, ?, ?)', array('jimmy sanwo', 'WAEC', '1988', '2020', array('1', '2', '3'))) in Connection.php line 435
at Connection->insert('insert into `user_answers` (`username`, `category`, `year`, `correct_answer`, `question_id`) values (?, ?, ?, ?, ?)', array('jimmy sanwo', 'WAEC', '1988', '2020', array('1', '2', '3'))) in Builder.php line 2138
at Builder->insert(array('username' => 'jimmy sanwo', 'category' => 'WAEC', 'year' => '1988', 'correct_answer' => '2020', 'question_id' => array('1', '2', '3'))) in Builder.php line 1470
at Builder->__call('insert', array(array('username' => 'jimmy sanwo', 'category' => 'WAEC', 'year' => '1988', 'correct_answer' => '2020', 'question_id' => array('1', '2', '3')))) in Model.php line 3561
at Builder->insert(array('username' => 'jimmy sanwo', 'category' => 'WAEC', 'year' => '1988', 'correct_answer' => '2020', 'question_id' => array('1', '2', '3'))) in Model.php line 3561
at Model->__call('insert', array(array('username' => 'jimmy sanwo', 'category' => 'WAEC', 'year' => '1988', 'correct_answer' => '2020', 'question_id' => array('1', '2', '3')))) in Model.php line 3573
at Answers->insert(array('username' => 'jimmy sanwo', 'category' => 'WAEC', 'year' => '1988', 'correct_answer' => '2020', 'question_id' => array('1', '2', '3'))) in Model.php line 3573
at Model::__callStatic('insert', array(array('username' => 'jimmy sanwo', 'category' => 'WAEC', 'year' => '1988', 'correct_answer' => '2020', 'question_id' => array('1', '2', '3')))) in QuizController.php line 78
at Answers::insert(array('username' => 'jimmy sanwo', 'category' => 'WAEC', 'year' => '1988', 'correct_answer' => '2020', 'question_id' => array('1', '2', '3'))) in QuizController.php line 78
at QuizController->submit_quiz(object(Request))
at call_user_func_array(array(object(QuizController), 'submit_quiz'), array(object(Request))) in Controller.php line 55
at Controller->callAction('submit_quiz', array(object(Request))) in ControllerDispatcher.php line 44
at ControllerDispatcher->dispatch(object(Route), object(QuizController), 'submit_quiz') in Route.php line 190
at Route->runController() in Route.php line 144
at Route->run(object(Request)) in Router.php line 653
at Router->Illuminate\Routing\{closure}(object(Request)) in Pipeline.php line 53
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in SubstituteBindings.php line 41
at SubstituteBindings->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in VerifyCsrfToken.php line 65
at VerifyCsrfToken->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in ShareErrorsFromSession.php line 49
at ShareErrorsFromSession->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in StartSession.php line 64
at StartSession->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in AddQueuedCookiesToResponse.php line 37
at AddQueuedCookiesToResponse->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in EncryptCookies.php line 59
at EncryptCookies->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in Pipeline.php line 104
at Pipeline->then(object(Closure)) in Router.php line 655
at Router->runRouteWithinStack(object(Route), object(Request)) in Router.php line 629
at Router->dispatchToRoute(object(Request)) in Router.php line 607
at Router->dispatch(object(Request)) in Kernel.php line 268
at Kernel->Illuminate\Foundation\Http\{closure}(object(Request)) in Pipeline.php line 53
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in CheckForMaintenanceMode.php line 46
at CheckForMaintenanceMode->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in Pipeline.php line 104
at Pipeline->then(object(Closure)) in Kernel.php line 150
at Kernel->sendRequestThroughRouter(object(Request)) in Kernel.php line 117
at Kernel->handle(object(Request)) in index.php line 54
at require_once('C:\xampp\htdocs\testp\public\index.php') in server.php line 21
1/2
ErrorException in MySqlConnection.php line 81:
Array to string conversion
in MySqlConnection.php line 81
at HandleExceptions->handleError('8', 'Array to string conversion', 'C:\xampp\htdocs\testp\vendor\laravel\framework\src\Illuminate\Database\MySqlConnection.php', '81', array('statement' => object(PDOStatement), 'bindings' => array('jimmy sanwo', 'WAEC', '1988', '2020', array('1', '2', '3')), 'key' => '4', 'value' => array('1', '2', '3')))
at PDOStatement->bindValue('5', array('1', '2', '3'), '2') in MySqlConnection.php line 81
at MySqlConnection->bindValues(object(PDOStatement), array('jimmy sanwo', 'WAEC', '1988', '2020', array('1', '2', '3'))) in Connection.php line 478
at Connection->Illuminate\Database\{closure}(object(MySqlConnection), 'insert into `user_answers` (`username`, `category`, `year`, `correct_answer`, `question_id`) values (?, ?, ?, ?, ?)', array('jimmy sanwo', 'WAEC', '1988', '2020', array('1', '2', '3'))) in Connection.php line 763
at Connection->runQueryCallback('insert into `user_answers` (`username`, `category`, `year`, `correct_answer`, `question_id`) values (?, ?, ?, ?, ?)', array('jimmy sanwo', 'WAEC', '1988', '2020', array('1', '2', '3')), object(Closure)) in Connection.php line 726
at Connection->run('insert into `user_answers` (`username`, `category`, `year`, `correct_answer`, `question_id`) values (?, ?, ?, ?, ?)', array('jimmy sanwo', 'WAEC', '1988', '2020', array('1', '2', '3')), object(Closure)) in Connection.php line 481
at Connection->statement('insert into `user_answers` (`username`, `category`, `year`, `correct_answer`, `question_id`) values (?, ?, ?, ?, ?)', array('jimmy sanwo', 'WAEC', '1988', '2020', array('1', '2', '3'))) in Connection.php line 435
at Connection->insert('insert into `user_answers` (`username`, `category`, `year`, `correct_answer`, `question_id`) values (?, ?, ?, ?, ?)', array('jimmy sanwo', 'WAEC', '1988', '2020', array('1', '2', '3'))) in Builder.php line 2138
at Builder->insert(array('username' => 'jimmy sanwo', 'category' => 'WAEC', 'year' => '1988', 'correct_answer' => '2020', 'question_id' => array('1', '2', '3'))) in Builder.php line 1470
at Builder->__call('insert', array(array('username' => 'jimmy sanwo', 'category' => 'WAEC', 'year' => '1988', 'correct_answer' => '2020', 'question_id' => array('1', '2', '3')))) in Model.php line 3561
at Builder->insert(array('username' => 'jimmy sanwo', 'category' => 'WAEC', 'year' => '1988', 'correct_answer' => '2020', 'question_id' => array('1', '2', '3'))) in Model.php line 3561
at Model->__call('insert', array(array('username' => 'jimmy sanwo', 'category' => 'WAEC', 'year' => '1988', 'correct_answer' => '2020', 'question_id' => array('1', '2', '3')))) in Model.php line 3573
at Answers->insert(array('username' => 'jimmy sanwo', 'category' => 'WAEC', 'year' => '1988', 'correct_answer' => '2020', 'question_id' => array('1', '2', '3'))) in Model.php line 3573
at Model::__callStatic('insert', array(array('username' => 'jimmy sanwo', 'category' => 'WAEC', 'year' => '1988', 'correct_answer' => '2020', 'question_id' => array('1', '2', '3')))) in QuizController.php line 78
at Answers::insert(array('username' => 'jimmy sanwo', 'category' => 'WAEC', 'year' => '1988', 'correct_answer' => '2020', 'question_id' => array('1', '2', '3'))) in QuizController.php line 78
at QuizController->submit_quiz(object(Request))
at call_user_func_array(array(object(QuizController), 'submit_quiz'), array(object(Request))) in Controller.php line 55
at Controller->callAction('submit_quiz', array(object(Request))) in ControllerDispatcher.php line 44
at ControllerDispatcher->dispatch(object(Route), object(QuizController), 'submit_quiz') in Route.php line 190
at Route->runController() in Route.php line 144
at Route->run(object(Request)) in Router.php line 653
at Router->Illuminate\Routing\{closure}(object(Request)) in Pipeline.php line 53
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in SubstituteBindings.php line 41
at SubstituteBindings->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in VerifyCsrfToken.php line 65
at VerifyCsrfToken->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in ShareErrorsFromSession.php line 49
at ShareErrorsFromSession->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in StartSession.php line 64
at StartSession->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in AddQueuedCookiesToResponse.php line 37
at AddQueuedCookiesToResponse->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in EncryptCookies.php line 59
at EncryptCookies->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in Pipeline.php line 104
at Pipeline->then(object(Closure)) in Router.php line 655
at Router->runRouteWithinStack(object(Route), object(Request)) in Router.php line 629
at Router->dispatchToRoute(object(Request)) in Router.php line 607
at Router->dispatch(object(Request)) in Kernel.php line 268
at Kernel->Illuminate\Foundation\Http\{closure}(object(Request)) in Pipeline.php line 53
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in CheckForMaintenanceMode.php line 46
at CheckForMaintenanceMode->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in Pipeline.php line 104
at Pipeline->then(object(Closure)) in Kernel.php line 150
at Kernel->sendRequestThroughRouter(object(Request)) in Kernel.php line 117
at Kernel->handle(object(Request)) in index.php line 54
at require_once('C:\xampp\htdocs\testp\public\index.php') in server.php line 21

@Snapey i did a serialize on question_id

 foreach ( $request->answer as $answer) {
    Answers::create([
        'username' => Auth::user()->name,
        'category' => $request->category,
        'year' => $request->year,
        'correct_answer' => $answer,
        'question_id' => serialize($request->question_id),
    ]);

i get this in the database

a:3:{i:1;s:1:"1";i:2;s:1:"2";i:3;s:1:"3";}

yep, question_id is the problem

its an array when it should not be. you need to go back to the form and work out why question_id is an array

@Snapey trust me i have been staring at the form for some time now i can't seem to fathom where d array error is coming from now it only returns question_id as 3,3,3 instead of 1,2,3 and when i view the form in the webpage i get it returned as 1,2,3 but when i submit to database i get 3,3,3 i'm lost . here is what my form looks like

<form method="post" action="/submit_quiz" id="quiz_form" >
    
{{ csrf_field() }}
            <b> Questions</b>
    <div style="max-height:60%;overflow-y:auto;">

@foreach($answers as $key => $answer)

<input type="hidden" name="name" value="{{Auth::user()->name}}">
<input type="hidden" name="category" value="{{$answer->category}}">
<input type="hidden" name="year" value="{{$answer->year}}">

<input type="hidden" name="question_id" value="{{$question->id}}">
            <P>{{$answer->id}}.<BR>
                <select name="answer[]">
                    <option></option>
                    <option value="{{$answer->option_A}}">{{$answer->option_A}}</option>
                    <option value="{{$answer->option_B}}">{{$answer->option_B}}</option>
                    <option value="{{$answer->option_C}}">{{$answer->option_C}}</option>
                    <option value="{{$answer->option_D}}">{{$answer->option_D}}</option>
                    <option value="{{$answer->option_E}}">{{$answer->option_E}}</option>
                </select>
</p>
<br>
@endforeach
<button class="btn btn-danger"  onClick="javascript:cancelmove();" style="margin-top:2px;" >Submit Quiz</button>


    
<br>
<br>
<br>

</form>

I already told you that in the post you are getting

Just quick example:

[a,b,c,d,e,f,g,h,i,j,k,l]

You need to break that up to

[a,b,c,d]
////perform an insert
[e,f,g,h]
////perform an insert
[i,j,k,l]
////perform an insert

But you can loop through all if setup correctly. This is not hard. DD first and get position of csrf token, may have to adjust position.

In other words you are getting:

[csrf_token,a,b,c,d,e,f,g,h,i,j,k,l]

Yes I know it's really more like

[csrf_token,key => value, etc etc]

practice just looping over first and just play with it till you get it right.

You should know if question_id should contain more than one value. Is each answer not for just one question?

 <select name="answer[]">

Learn how to loop over this stuff, just play with it til you got it.

A loop in a loop, one for normal request but you are passing an answer array as well, loop it. Get an array count, loop that many times over each answer array, just working out basic math.