Cannot use mysql function IN in laravel db statement

Question

Im having a problem when using the IN function of MySQL in a Laravel 5.2 DB Statement. Here is the example:

$str_array = implode(",",$array_reservations);
$sql_result = DB::select("
            select r.id,r.people,r.date,r.status,p.alias,u.name,p.profile
            from  reservations r inner join places p on p.id = r.place_id 
            inner join  users u on u.id = r.user_id
            where r.id in(?)
            order by r.date desc
            ", [$str_array]);

But in the result I just get the result of the first reservation of the array.

The length of the array will be always variable.

Like @P.Gearman pointed out; refer: stackoverflow.com/questions/26465243/… — PseudoAj
– PseudoAj, Commented May 23, 2016 at 16:58
You can't use a single binding placeholder for an entire IN list, you have to use individual placeholders for each element, and pass $array_reservations as an array, not as a comma-separated string — Mark Baker
– Mark Baker, Commented May 23, 2016 at 17:02

Felippe Duarte · Accepted Answer · 2016-05-23 17:08:20Z

2

In this case you need to use DB::raw. Also, you need to create as many "?" you need, for each id you want to bind. That's the way PDO works:

$str_array = implode(",",$array_reservations);
$bindings = trim(str_repeat('?,', count($array_reservations)), ',');

DB::select($sql, $inputids);
$sql_result = DB::select(DB::raw("
        select r.id,r.people,r.date,r.status,p.alias,u.name,p.profile
        from  reservations r inner join places p on p.id = r.place_id 
        inner join  users u on u.id = r.user_id
        where r.id in($bindings)
        order by r.date desc
        "), $str_array);

But, IMO, you should use Query Builder:

DB::table('reservations')
    ->join('places','places.id','=','reservarions.place_id')
    ->join('users','users.id','=','reservarions.user__id')
    ->whereIn('reservations.id',$str_array)
    ->orderBy('reservations.date', 'DESC');

edited May 23, 2016 at 17:08

answered May 23, 2016 at 16:59

Felippe Duarte

15.2k2 gold badges27 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yasin Patel · Accepted Answer · 2016-05-23 17:14:19Z

1

Try this :

  $sql_result = DB::table('reservations AS r')
        ->join('places AS p', 'p.id', '=', 'r.place_id')
        ->join('users AS u', 'u.id', '=', 'r.user_id')
        ->select('r.id','r.people','r.date','r.status','p.alias','u.name','p.profile')
        ->whereIn('r.id',$str_array)                    // write you conditions in array
        ->orderBy('r.date', 'desc')
        ->get();

answered May 23, 2016 at 17:14

Yasin Patel

5,7818 gold badges37 silver badges57 bronze badges

Collectives™ on Stack Overflow

Cannot use mysql function IN in laravel db statement

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related