kringeltorte kringeltorte - 4 months ago 19
PHP Question

Laravel - Where Clause not working

I am trying to build a search form with laravel, but I cannot get the where clause to work.



$term = $request->input('term');

$count = DB::table('members as m')
->where(DB::raw('m.member_first_name'), 'LIKE', "%$term%")
->orWhere(DB::raw('m.member_last_name'), 'LIKE', "%$term%")
->orWhere(DB::raw('m.member_business_address'), 'LIKE', "%$term%")
->orWhere(DB::raw("concat('m.member_first_name',' ','m.member_last_name')"), 'LIKE', "%$term%")
->orWhere(DB::raw("concat('m.member_last_name',' ','m.member_first_name')"), 'LIKE', "%$term%")
->orWhere(DB::raw("concat('m.member_name_affix',' ','m.member_last_name',' ','m.member_first_name')"), 'LIKE', "%$term%")
->orWhere(DB::raw("concat('m.member_first_name',' ','m.member_name_affix',' ','m.member_last_name')"), 'LIKE', "%$term%")
->count();

var_dump($count);





var_dump($count) always returns all the database entries, no matter what the search term is.



This is my first Laravel project and I would be very thankful for any kind of help.

Answer

Perhaps try encasing the query in an overall where:

$count = DB::table('members as m')
         ->where(function ($q) {
            $q->where(DB::raw('m.member_first_name'), 'LIKE', "%$term%")
            ->orWhere(DB::raw('m.member_last_name'), 'LIKE', "%$term%")
            ->orWhere(DB::raw('m.member_business_address'), 'LIKE', "%$term%")
            ->orWhere(DB::raw("concat('m.member_first_name',' ','m.member_last_name')"), 'LIKE', "%$term%")
            ->orWhere(DB::raw("concat('m.member_last_name',' ','m.member_first_name')"), 'LIKE', "%$term%")
            ->orWhere(DB::raw("concat('m.member_name_affix',' ','m.member_last_name',' ','m.member_first_name')"), 'LIKE', "%$term%")
            ->orWhere(DB::raw("concat('m.member_first_name',' ','m.member_name_affix',' ','m.member_last_name')"), 'LIKE', "%$term%")
         })
         ->count();

If this fails, can you post the output of:

DB::table('members as m')
     ->where(function ($q) {
         ... scope here ....
     })->toSql();