Convert sql select where in to Laravel query builder

Question

my array from $stemp [hasil] => 20,8,24

my sql query SELECT * FROM 'objek' WHERE objek.id IN ('20','8','24')

I'm try DB::select("SELECT * FROM 'objek' WHERE objek.id IN ('$stemp')");

It's not work, and get error Array to string conversion.

Answer 1

The clean solution

Use the Laravel Query Builder methods to make your query:

DB::table('objek')->select('*')->whereIn('id', [20, 8, 4])->get()

Note that the ->select('*') isn't required since the query builder selects * by default. So you can remove it:

DB::table('objek')->whereIn('id', [20, 8, 4])->get()

The bad (but working) solution

Since $stemp is a array, you need to convert it to an string before concatenating it on the query:

$stemp = [20, 8, 24];

// Put your ID's between Quotes (not necessary actually, but 
// I'm just trying to replicate the desired query)
$stemp = array_map(function($id) { return "'{$id}'"; }, $stemp); // ['20','8','24']

// Glue the pieces using a comma
$stemp = implode(',', $stemp); // string(13) "'20','8','24'"

// Now you can concatenate it to your query
// Note that you shouldn't use the single quotes between $stemp
// since your quotes are already on each number.
DB::select("SELECT * FROM 'objek' WHERE objek.id IN ($stemp)");

Why this is a bad solution?

Doing the query by this way can open a SQL Injection vulnerability, so you must do either:

• Trust on your data source, eg: Your $stemp come from another queries that you trust the content
• Sanitize your data to remove malicious code

Why the first solution is better?

• Your code turns more human readable
• Laravel Query Builder automatically sanitize your input

Answer 2

You should do it like this.

DB::table('objek')
    ->select('*')
    ->whereIn('id', $stemp)
    ->get();

You can find more info on Laravel Where here in laravel.com/docs/5.3/queries#where-clauses

But I'd prefer using Eloquent rather than using DB::table().

you can create a model like Objek and extend it with Illuminate\Database\Eloquent\Model

use Illuminate\Database\Eloquent\Model;

class Objek extends Model
{
    protected $table = 'Objek';

}

Then you can do,

Objek::select('*')
    ->whereIn('id', $stemp)
    ->get();

Answer 3

Depends on what way you want to do

If Model is present

Model::whereIn('columnName', $array)->get()

To query table

DB::table('users')->whereIn('columnName', $array)->get();

If you want more have a look at https://laravel.com/docs/5.3/queries#raw-expressions

Hope this helps.

Answer 4

I think you should try this:

DB::table('objek')->whereIn('objek.id',array('20','8','24'))->get();

Hope this work for you!