Laravel Query Builder: where condition based on user input

Question

Here is my output of print_r($_POST)

array([id] => '123', [name] => '', [place] => '', [color] => '')

Where name , place and color are optional fields submitted by user..... user may select only name, place or color, name + color, color + place, or all three name + color + place.

How can I put where condition for these options chosen by user? Let's say for example, In Laravel we select DB table using the following statement...

 $Select_db = Db::table('mytable')
                  ->where('name', Input::get('name'))
                  ->where('place', Input::get('place'))
                  ->where('color', Input::get('color'))
                  ->select()
                  ->get();

As you can see above condition works only if there is an input for all fields from user, based on user input I want add where condition, how do we fix this???

Note: In this particular scenario, I am aware I could use isset() for each condition. However, what if there are many optional inputs?

Check this link may be helpful to you http://stackoverflow.com/questions/19325312/laravel-eloquent-multiple-where — Sadikhasan, Jun 25 '14 at 05:27
@Sadikhasan they are adding `multiple where conditions` for the known input....but in my case its unknown input seems to be different from my question... — Friend, Jun 25 '14 at 05:32

score 4 · Accepted Answer · edited Jul 22 '16 at 14:18

Try this:-

$Select_db = DB::table('mytable');

if (Input::get('name') != "")
    $Select_db->where('name', Input::get('name'));

if (Input::get('place') != "")
    $Select_db->where('place', Input::get('place'));

if (Input::get('color') != "")
    $Select_db->where('color', Input::get('color'));

$result = $Select_db->get();

And if there are multiple columns to match, then try using this:-

$Select_db = DB::table('mytable'); 
foreach($_POST as $key => $val){
    if(Input::get($key) != ""){
        $Select_db->where($key, Input::get($key));
    }   
} 
$Select_db->get();

score 0 · Answer 2 · edited May 16 '16 at 14:58

0

what about

$Select_db = Db::table('mytable');
foreach($_POST as $key => $val) {
    $Select_db->where($key, Input::get($key));
}
$Select_db->query()->get();

maybe consider to copy your $_POST and remove undesired values before you enter foreach:

unset($postcopy['badvar'])

edited May 16 '16 at 14:58

veksen

6,863
5
20
33

answered Jun 25 '14 at 05:33

Stefan

11
1

score 0 · Answer 3 · answered May 11 '18 at 13:28

  public function filter(Request $request)
  {   
    $first_name = $request->input('first_name');
    $sur_name = $request->input('sur_name');
    $email_work = $request->input('email_work');
    $country = $request->input('country');
    $position = $request->input('position');
    $event_id = $request->input('event_id');
    $event_name = $request->input('event_name');
    $nature_of_business = $request->input('nature_of_business');
    $mobile_number = $request->input('mobile_number');
    $event_date = $request->input('event_date');

        $record = DB::table('exceldatas');
        if ($request->has('first_name')){
            $record->where('first_name', $first_name);
        }
        if ($request->has('sur_name')) {
           $record->where('sur_name', $sur_name);
        }
        if ($request->has('email_work')) {
            $record->where('email_work', $email_work);
        }
        if ($request->has('country')) {
            $record->where('country', $country);
        }
        if ($request->has('event_name')) {
            $record->where('event_name', $event_name);
        }
        if ($request->has('nature_of_business')) {
            $record->where('nature_of_business', $nature_of_business);
        }
        if ($request->has('mobile_number')) {
            $record->where('mobile_number', $mobile_number);
        }
        if ($request->has('event_date')) {
            $record->where('event_date', $event_date);
        }
        $record =$record->paginate(15);
        return view('showresult')->with('record', $record);

}

Laravel Query Builder: where condition based on user input

3 Answers3