Laravel 5.5 querybuilder insert to table from array collection

Question

How can I insert the collection into the database. I loop the values in my controller and when I do insert, It has some error in it Array to string conversion (SQL: insert intosms(user_id,msg,mobile_number,isSend) values (1, SMS, {"phone":"+63 (971) 659-8143"}, 1)). Can someone tell me what should I do about this? Thanks in advance

Controller Code

   if($request->smsn == 'on'){
            $events->smsnotify = 1;

            $numbers = \DB::table('users')
                        ->select('phone')
                        ->where('school_id', '=', $sid)
                        ->where('role', '=', $request->group_id)
                        ->get();

            $numbersArray = [];
            foreach($numbers as $number){
                $numbersArray[] = json_encode($number);
            }

                        // dd($numbersArray);
            $sms = DB::table('sms')->insert([
                'user_id' => $sid,
                'msg' => $request->name,
                'mobile_number' => $numbersArray,
                'isSend' => 1
            ]);

        }

when i dd($numbersArray);

array:9 [▼
  0 => "{"phone":"+63 (971) 659-8143"}"
  1 => "{"phone":"(0997) 212-7919"}"
  2 => "{"phone":"(0900) 117-9012"}"
  3 => "{"phone":"(0905) 470-0661"}"
  4 => "{"phone":"+63 (977) 194-0623"}"
  5 => "{"phone":"(0927) 448-8047"}"
  6 => "{"phone":"+63 (923) 889-3009"}"
  7 => "{"phone":"+63 (971) 215-1217"}"
  8 => "{"phone":"+63 (998) 452-5708"}"
]

I just want to insert the numbers with a comma separator in a single row of my table. How could I achieve that?

i want to add all the numbers sir in a single column in the sms table sir. — draw134
– draw134, Commented Nov 18, 2019 at 6:06
yes sir. Its like this user_Id = 1, msg=random, mobile_no= 001,002,003, isSend=1 — draw134
– draw134, Commented Nov 18, 2019 at 6:08
use implode : 'mobile_number' => implode(',',$numbersArray), — Yasin Patel
– Yasin Patel, Commented Nov 18, 2019 at 6:08

Prashant Deshmukh..... · Accepted Answer · 2019-11-18 06:36:04Z

2

Try this way

 $numbers = \DB::table('users')
                    ->where('school_id', '=', $sid)
                    ->where('role', '=', $request->group_id)
                    ->pluck('phone')
                    ->toArray();

and you can directly implode() resulting array.

 $numbersArray = implode(',',$numbers);

You can skip foreach loop. $numbersArray will give comma separated string to directly save in column.

answered Nov 18, 2019 at 6:36

Prashant Deshmukh.....

2,2921 gold badge11 silver badges11 bronze badges

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

Comments

lagbox · Accepted Answer · 2019-11-18 06:37:25Z

2

$numbers = \DB::table('users')
    ->where('school_id', $sid)
    ->where('role', $request->group_id)
    ->pluck('phone')
    ->implode(',');


$sms = DB::table('sms')->insert([
    'user_id' => $sid,
    'msg' => $request->name,
    'mobile_number' => $numbers,
    'isSend' => 1
]);

You can pluck just the phone column from the table and then call implode on the Collection to implode the values by ,.

Laravel 5.8 Docs - Query Builder - Retrieving Results - Retrieving A List Of Column Values pluck

Laravel 5.8 Docs - Collections - Available Methods - implode

answered Nov 18, 2019 at 6:37

lagbox

50.7k8 gold badges76 silver badges85 bronze badges

13 Comments

draw134 Over a year ago

it returns an error sir Array to string conversion (SQL: insert into sms` (user_id, school_id, msg, mobile_number, isSend) values (2, 1, New Event asda, +63 (928) 206-5706, 1))`

lagbox Over a year ago

then your query had no results

lagbox Over a year ago

implode is returning a string, so the error isn't coming from there

draw134 Over a year ago

how do i select multiple columns from implode sir? I want to get also the name?

lagbox Over a year ago

I have a documentation link for that method ... you are more than welcome to read it ... good luck with your project :)

|

Kenneth Sunday · Accepted Answer · 2019-11-18 06:30:14Z

1

You adding a json format to your 3rd column.

before you insert . json_encode it first then later once you retrieved the data . use json_decode and you are free to your object notation coding eg. $row->key

In addition if you insert json value to a column. you do JSON_VALUE query as well

https://learn.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver15

https://learn.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql?view=sql-server-ver15

answered Nov 18, 2019 at 6:30

Kenneth Sunday

8957 silver badges14 bronze badges

1 Comment

Kenneth Sunday Over a year ago

json_encode your created json array again to make it array of json (converted to string) for saving purposes.

Collectives™ on Stack Overflow

Laravel 5.5 querybuilder insert to table from array collection

3 Answers 3

Comments

13 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

13 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related