laravel 5.3 querybuilder db insert using array iteration as column

Question

I'm new to Laravel and an amateur at best with PHP, but trying to learn. This is my first question as I've usually been able to find an answer searching.

This is probably something simple I'm overlooking. I am submitting a form that can have up to 2 rows from a view to a controller. I am creating an array from the data like so in my controller:

    foreach($candidates_member_id as $key => $value)
    {
        $arrData[] = array( 
            'member_user_id'        => $member_user_id[$key],
            'candidates_member_id'  => $candidates_member_id[$key], 
            'candidates_district'   => $candidates_district[$key], 
            'candidate_name'        => $candidate_name[$key], 
            'created_at'            => $created_at[$key],
            'updated_at'            => $updated_at[$key]                        
        );
    }

If I print_r($arrData) on the associated view, I get results as such:

Array
(
    [0] => Array
        (
            [member_user_id] => 1
            [candidates_member_id] => 12345
            [candidates_district] => 6
            [candidate_name] => Doe, John
            [created_at] => 2016-09-03 15:07:14
            [updated_at] => 2016-09-03 15:07:14
        )

    [1] => Array
        (
            [member_user_id] => 1
            [candidates_member_id] => 54321
            [candidates_district] => 6
            [candidate_name] => Doe, Jane
            [created_at] => 2016-09-03 15:07:15
            [updated_at] => 2016-09-03 15:07:15
        ) 
)

However, when I add DB insert into the above foreach loop:

DB::table('primary_votes')->insert(
            ['member_user_id' => $member_user_id,
             'candidates_member_id' => $candidates_member_id,
             'candidates_district' => $candidates_district,
             'created_at' => $created_at,
             'updated_at' => $updated_at]
        );

I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'field list' (SQL: insert into primary_votes (0, 1) values (1, 1), (12345, 54321), (6, 6), (2016-09-03 15:07:14, 2016-09-03 15:07:15), (2016-09-03 15:07:14, 2016-09-03 15:07:15)) in Connection.php line 761 at Connection->runQueryCallback('insert into primary_votes (0, 1) values (?, ?), (?, ?), (?, ?), (?, ?), (?, ?)', array('1', '1', '12345', '54321', '6', '6', '2016-09-03 15:07:14', '2016-09-03 15:07:15', '2016-09-03 15:07:14', '2016-09-03 15:07:15'), object(Closure)) in Connection.php line 717 at Connection->run('insert into primary_votes (0, 1) values (?, ?), (?, ?), (?, ?), (?, ?), (?, ?)', array('1', '1', '12345', '54321', '6', '6', '2016-09-03 15:07:14', '2016-09-03 15:07:15', '2016-09-03 15:07:14', '2016-09-03 15:07:15'), object(Closure)) in Connection.php line 481 at Connection->statement('insert into primary_votes (0, 1) values (?, ?), (?, ?), (?, ?), (?, ?), (?, ?)', array('1', '1', '12345', '54321', '6', '6', '2016-09-03 15:07:14', '2016-09-03 15:07:15', '2016-09-03 15:07:14', '2016-09-03 15:07:15')) in Connection.php line 435 at Connection->insert('insert into primary_votes (0, 1) values (?, ?), (?, ?), (?, ?), (?, ?), (?, ?)', array('1', '1', '12345', '54321', '6', '6', '2016-09-03 15:07:14', '2016-09-03 15:07:15', '2016-09-03 15:07:14', '2016-09-03 15:07:15')) in Builder.php line 2117 at Builder->insert(array('member_user_id' => array('1', '1'), 'candidates_member_id' => array('12345', '54321'), 'candidates_district' => array('6', '6'), 'created_at' => array('2016-09-03 15:07:14', '2016-09-03 15:07:15'), 'updated_at' => array('2016-09-03 15:07:14', '2016-09-03 15:07:15'))) in VoteSubmitController.php line 60

So, for some reason I can't figure out, it is trying to use the array "row" key as a column name. Why is it interpreting the individual row key as a column in the DB? Thanks in advance.

Andrej · Accepted Answer · 2016-09-03 17:47:35Z

4

Your code should look like:

foreach($candidates_member_id as $key => $value)
{
    $arrData = array( 
        'member_user_id'        => $member_user_id[$key],
        'candidates_member_id'  => $candidates_member_id[$key], 
        'candidates_district'   => $candidates_district[$key], 
        'candidate_name'        => $candidate_name[$key], 
        'created_at'            => $created_at[$key],
        'updated_at'            => $updated_at[$key]                        
    );
    DB::table('primary_votes')->insert($arrData);
}

The reason for mysql error is that $member_user_id, $candidates_member_id are arrays and not scalar values. You cannot insert array in mysql.

answered Sep 3, 2016 at 17:47

Andrej

7,5041 gold badge22 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Crazy_Redneck Over a year ago

Thank you very much! Getting closer. The insert is happening now, but the first iteration is inserted twice. I'll try to debug that when I get a chance. I have another insert as part of that function, plus an update and I'll create an array for each of those.

Collectives™ on Stack Overflow

laravel 5.3 querybuilder db insert using array iteration as column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related