0

I'm trying to add params to the IN clause of a sql query in PHP.

The query:

$sqlQuery = <<<SQL 
    SELECT * FROM mytable where codes IN (:codes)"
SQL;

The params:

$params['codes'] = "'test','last'";

But this doesn't work i.e. doesn't return any results

$total = DB::select($sqlQuery, $params);

But this query run directly in the database returns results

SELECT * FROM mytable where codes IN ('test','last')

I'm guessing it has to with the parameters not being handled the same way for an IN clause, but I haven't been able to find anything about this.

fractal5
  • 2,034
  • 4
  • 29
  • 50
  • Try to use a list for parameter :codes.If you join the sql string you can do it like you do. If you use prepare statement way. You must do it with bind variable.I declare that I don't know much about PHP with db ORM, just guess this from other language. – ElapsedSoul Aug 24 '22 at 01:12
  • Related: https://stackoverflow.com/q/60589381/2943403 – mickmackusa Aug 24 '22 at 05:11

3 Answers3

1

If you are using Laravel, and if raw sql is not a must, you can do this instead.

DB::table("mytable")
    ->whereIn('codes', $param['codes'])
    ->get();

This should give you the relevant collection of mytable rows.

If you have model set up correctly as an Eloquent model, you can also do:

MyModel::whereIn('codes', $param['codes'])->get();

Should yield the same result.

Reference: Laravel documentation, under the section "whereIn / whereNotIn / orWhereIn / orWhereNotIn" under Additional Where Clauses

user3532758
  • 2,221
  • 1
  • 12
  • 17
0

Try to spread the array and the params in the sql query. For example, the sql query should be like "SELECT * FROM mytable where codes IN (:param1,:param2)" and pass the params values using spread operator on the array $params['codes']. You can use foreach loop to make it dynamic

Ahmed Nabil
  • 1
0

You can't bind a list like that. Each bound variable needs it's own placeholder. I'm assuming you don't know ahead of time how many parameters you'll have. Something like this would work, though this could certainly be optimized.

$codes = ['test','last'];
$qry = sprintf('SELECT * FROM mytable where codes IN (%s)',
    implode(',',array_filll(0, count($codes), '?')
);

$total = DB::select($qry, $codes);
TheGentleman
  • 2,324
  • 13
  • 17
  • Wi`lll` this work? Maybe simply point [here](https://stackoverflow.com/q/17226762/2943403) instead of answering. – mickmackusa Aug 24 '22 at 05:15