CODEWITHSUNDEEP
phplaravelsearchlaravel-5query-builder
Schwesi
Schwesi

Reputation: 4904

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.

Upvotes: 0

Views: 2116

Answers (3)

Jono20201
Jono20201

Reputation: 3205

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();

Upvotes: 2

Avishek
Avishek

Reputation: 824

Simply try this code and if you got some syntax error then try to change the ' symbols here and there..as I can't test it before post..

$term = "%".$request->input('term')."%";
$count = DB::table('members as m')
         ->where('m.member_first_name', 'LIKE', $term)
         ->orWhere('m.member_last_name', 'LIKE', $term)
         ->orWhere('m.member_business_address', 'LIKE', $term)
         ->orWhereRaw('(concat_ws(m.member_first_name,m.member_last_name) LIKE ? )',[$term])
         ->orWhereRaw('(concat_ws(m.member_last_name,m.member_first_name) LIKE ? )',[$term])
         ->orWhereRaw('(concat_ws(m.member_name_affix,m.member_last_name,m.member_first_name) LIKE ? )',[$term])
         ->orWhereRaw('(concat_ws(m.member_first_name,m.member_name_affix,m.member_last_name) LIKE ? )',[$term])
         ->count();

var_dump($count);

you can use whereRaw() don't need to put DB::raw() inside where().

Upvotes: 0

George Sharvadze
George Sharvadze

Reputation: 570

You're using and / or together, which of course will fail. Try the following:

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

Upvotes: 0

Related Questions