WHERE IN array binding in DB::raw laravel 5.4

Question

I'm trying to bind an array in a raw WHERE IN query in the Laravel DB

example:

$arr = [1,2,3];
DB::select(DB::raw("select * from test1 WHERE id IN ? "), [$arr]);

for some reason the array is not being changed to the following query:

select * from test1 WHERE id IN (1,2,3)

does someone know if I can do this somehow?

Answer 1

try it in laravel:

$arr = [1,2,3];
$result = DB::table('test1')->whereIn('id', $arr)->get();
dd($result);

And use this one for your raw query:

$arr = [1,2,3];
$arr = join(",",$arr);
$result =  DB::select(DB::raw("SELECT * FROM test1 WHERE id IN (".$arr.")"));
dd($result);

For preventing sql injection you use something like which i have mentioned below.

 $arr = [1,2];
 $arr = join(",",$arr);
 $result =  DB::select(DB::raw("SELECT * FROM test1 WHERE id IN (?,?)"),$arr);
 dd($result);

it will be work for you.

Answer 2

$arr = [1,2,3];
$placeholders = implode(",", array_fill(0, count($arr), '?'));

DB::select("select * from test1 WHERE id IN ($placeholders)", $arr);

This example:

1. supports any length of an array
2. does not contain a cycle
3. passes arguments safely

In this example, I fill a new array with such a number of question marks equal to the array length. Then I glue the new array, separated by commas, and get "?,?,?, ...". Then I insert this substring between the parentheses operator "IN". And I pass the array of items itself as the second parameter of the select function. As a result, each element of the array of elements has its own placeholder in the "IN" operator.

Answer 3

or

DB::table("test1")->whereIn('id', $arr)->get();

https://laravel.com/docs/5.4/queries#where-clauses

Answer 4

or Eloquent :

$q= TestModel::where('id',$arr)->get();

Answer 5

Try:

$array = [2,5,9,7,5];
$arrPos = [];
$arrBind = [];
foreach ($array as $pos => $id) {
    $arrPos[] = ":id_{$pos}";
    $arrBind["id_{$pos}"] = $id;
}

$sql = 
"  SELECT * FROM test1 WHERE id IN (".implode(', ', $arrPos).") ";
$rs = DB::select($sql, $arrBind);

Answer 6

I slightly optimized @nilecrocodile code so that we don't need to construct array and create string from and it. Instead, we'll create placeholders string this way:

$placeholders = substr(str_repeat(', ?', count($arr)), 2);

DB::select("select * from test1 WHERE id IN ($placeholders)", $arr);