I have created a multiform search in laravel & it's not working properly

I have a table name candidates & there is a row named salary in which it has value like shown the the below image from 1 lac(s) to 39 lac(s)

The meaning it's not working properly is if i search min salary = 0 & max salary = 4 it's showing data between 0 to 4 but it's also showing data like 10 18 22

controller code

    public function advance(Request $request)
    {
    $data = \DB::table('candidates');
    if( $request->name){
        $data = $data->where('name', 'LIKE', "%" . $request->name . "%");
    }
     if( $request->location){
        $data = $data->where('location', 'LIKE', "%" . $request->location . "%");
    }
     if( $request->key_skills){
        $data = $data->where('key_skills', 'LIKE', "%" . $request->key_skills . "%");
    }
    if( $request->gender){
        $data = $data->where('gender', 'LIKE', "%" . $request->gender . "%");
    }
     if( $request->pref_loc){
        $data = $data->where('pref_loc', 'LIKE', "%" . $request->pref_loc . "%");
    }
    if( $request->phoneno){
        $data = $data->where('phoneno', 'LIKE', "%" . $request->phoneno . "%");
    }
     if( $request->email){
        $data = $data->where('email', 'LIKE', "%" . $request->email . "%");
    }

    $min_ctc = $request->min_ctc;
    $max_ctc = $request->max_ctc;
    
    if ($min_ctc || $max_ctc) {
         $data = $data->where('salary', '>=', $min_ctc);
         $data = $data->where('salary','<=',$max_ctc);
     }
    
    $data = $data->paginate(10);
    $data2 = $data->total();
    return view('search', compact('data2'))->with('data',$data);
}

Thanks in advance

CodePudding user response：

You are trying to search on string not on integer. In order to get the required result you have to type cast the string to integer or float and then perform the operation.

Here is the script that might help you.

    if ($min_ctc || $max_ctc) {
         $data = $data->whereRaw("CONVERT(SUBSTRING_INDEX(salary,' ', 1),UNSIGNED INTEGER) >= {$min_ctc}");
         $data = $data->whereRaw("CONVERT(SUBSTRING_INDEX(salary,' ', 1),UNSIGNED INTEGER) <= {$max_ctc}");
     }