Laravel Query: Php implode array string values

Question

Currently I have this variable

$arrays = implode(", ", $request->pmChck);

and If I try to return this variable. I will be getting this kind of output

2019-100,2018-100

As you can see the values are separated by commas

Now in my laravel query, I'm trying to get all the records of the employee except for these two company_id above.

$pm_selected = DB::connection('mysql')->select("SELECT * FROM view_employee_info WHERE company_id NOT IN('".$arrays."')");

The query is not working, it shows all the data and also the data with the company_id of 2019-100 and 2018-100

It should listing all the data except for these two company_id 2019-100 and 2018-100

Is there anything wrong with my format or syntax?

When you wrap a comma-separated string in quotes, you are only passing one value. `NOT IN ()` is processing a single entry, but you mean to feed it two. You need to quote wrap each entry in the list. I'll find a duplicate to close with. — mickmackusa, Oct 28 '19 at 07:32

score 4 · Answer 1 · edited Oct 28 '19 at 07:47

4

Assuming $request->pmChck is an array of companies you want to exclude, the query will be :

DB::table(..)->select(..)->whereNotIn('company_id', $request->pmChck)->get();

edited Oct 28 '19 at 07:47

ka_lin

answered Oct 28 '19 at 07:36

Prasanth M P

score 3 · Accepted Answer · answered Oct 28 '19 at 07:33

3

Your imploded array looks like 2019-100, 2018-100 and so is query:

SELECT * FROM view_employee_info WHERE company_id NOT IN('2019-100, 2018-100')

try imploding like this:

implode("', '", $arr)

And query will be ok:

SELECT * FROM view_employee_info WHERE company_id NOT IN('2019-100', '2018-100')

answered Oct 28 '19 at 07:33

I tied this `implode("', '", $arr)` based on your answer and it works fine. Thank you! – King RG Oct 28 '19 at 07:42
Glad to help you ^_^ – Malkhazi Dartsmelidze Oct 28 '19 at 07:44

score 1 · Answer 3 · answered Oct 28 '19 at 07:32

The problem in your query are the single quotes in the IN part.

If you change from

$pm_selected = DB::connection('mysql')->select("SELECT * FROM view_employee_info WHERE company_id NOT IN('".$arrays."')");

To

$pm_selected = DB::connection('mysql')->select("SELECT * FROM view_employee_info WHERE company_id NOT IN(".$arrays.")");

it should work as you expect it. It currently does not work, because your query would be like the following:

SELECT * FROM table WHERE company_id IN ('123, 234')

which would treat your input values as a single value, 123, 234

3 Answers3