Improve query binding using array for where in statement

How do I bind the array to where in statement. Here is how I write it

$data = [3, 45, 65, 43];
$ids = implode(',', $data);
$sql = 'SELECT * FROM `table_name` WHERE iid in (:ids) AND iv_status IN ("N","Y","A")';
$result = array_map(function ($value) {
      return (array) $value;
   }, DB::select($sql, [
         'ids' => $ids,
   ]));

Any idea on how to improve this?

piljac1

6 years ago

Level 28

Not sure what you're trying to accomplish exactly, but you should use query builders instead of raw SQL

Example (based on your code block) :

DB::table('table_name')
    ->whereIn('iid', $data)
    ->whereIn('iv_status', ["N", "Y", "A"])
    ->get();

You can get rid of your $ids variable. No need to use implode with query builders. As for your array_map, I don't understand what you're trying to accomplish with it, so I'll let you update this piece of code to suit your needs.

miehilmie

6 years ago

Level 10

I just migrated from old php project and it has very lengthy sql statement. And I don't have time to construct all the model and restructure to the query builder like yours above. I just want to know how to bind it using DB::select

4unkur

6 years ago

Level 3

You can use selectRaw. Example from docs:

$orders = DB::table('orders')
                ->selectRaw('price * ? as price_with_tax', [1.0825])
                ->get();

tamiltyping

6 years ago

Level 1

Thanks and its really helpful

Please or to participate in this conversation.