Using Form Data to Query Table

Hi,

how does your database look like? In general you can use something like the following in your Controller: $results = NameOfYourModel::all()->where('question_id', 'id_youSearchFor')->get(); return view('nameOfYourView',[ 'results' => $results ]);

Table looks like id, question_id, user_id, answer and timestamps. What you have suggested brings back just one row based on the question_id but I need answers to match also. The search array submitted could have up to 30 fields to it would need to somehow loop through to create the query. Something like:

foreach ($filter_array as $key => $value) {
    $queries[] = DB::table('filter_answers')->where([
             ['question_id', $key],
             ['answer','=',$value]
        ])->get();
}

Looping through like this doesn't really do the job though because it test each individual row but would return multiple results each time through.

Possibly something like this

$results = AnswerTable::select('user_id')->where(function($query) {
    $query->whereQuestionId(1)->whereAnswer('Yes');
})->orWhere(function($query) {
    $query->whereQuestionId(2)->whereAnswer('Yes');
})->orWhere(function($query) {
    $query->whereQuestionId(15)->whereAnswer('Bachelors Degree');
})->orWhere(function($query) {
    $query->whereQuestionId(19)->whereAnswer('Air Force');
})->orWhere(function($query) {
    $query->whereQuestionId(30)->whereAnswer('Yes');
})->get();

Or if your criteria array is dynamic then

$criteria = [
  1 => "Yes"
  2 => "Yes"
  15 => "Bachelors Degree"
  19 => "Air Force"
  30 => "Yes"
];

$builder = AnswerTable::select('user_id');
$counter = 0;
foreach($criteria as $question => $answer) {
    if($counter == 0) {
        $builder = $builder->where(function($query) use ($question, $answer) {
            $query->whereQuestionId($question)->whereAnswer($answer);
        })
    } else {
        $builder = $builder->orWhere(function($query) use ($question, $answer) {
            $query->whereQuestionId($question)->whereAnswer($answer);
        })
    }
    $counter++;
}
$results = $builder->get();

This is untested code and some initial thoughts by just looking at your question.

@roshan.gautam This looks like it may work but catches on 'user' as unexpected string

Sorry, thats a typo it should be use. Fixed @ewistrand

@roshan.gautam Tried it out and gets me half way there by returning the user_ids, but I'm still facing the issue that it returns for each question. What I am really having trouble with is coming up with the logic to make sure that a user has matched all in the criteria array.

I think it should be something like:

// Loop Through Criteria
       // Find all who match the first in criteria array
       // Loop through those users
           // If user doesn't have next question_id or answer incorrect
               // Remove users
          // endif
     // end user loop
// end criteria loop

Hope that makes sense.

@roshan.gautam Thank for all your help! Took me a while but I got it working. If you see a way to refactor this to be a little cleaner i'm game. This is what my controller now looks like:

    public function results(Request $request)
    {
        $criteria = $request->all();
        unset( $criteria['_token'] );
        foreach ( $criteria as $key => $value ) {
            if ( $value == "na" ) {
                unset($criteria[$key]);
            }
        }

        $builder = FilterAnswers::select( 'user_id' );
        $counter = 0;
        foreach ( $criteria as $question => $answer ) :
            if($counter === 0) {
                $builder = $builder->where( function ( $query ) use ( $question, $answer ) {
                    $query->whereQuestionId( $question )->whereAnswer( $answer );
                } );
            }
            break;
        endforeach;
        $results = $builder->get();

        $user_ids = array();
        foreach( $results as $result ) {
            $user_ids[] = $result->user_id;
        }

        if(count($user_ids) > 1) {
            foreach( $user_ids as $key => $value ){
                foreach($criteria as $question => $answer) {
                    $test = FilterAnswers::where([
                        ['user_id', $value],
                        ['question_id', $question],
                        ['answer','=',$answer]
                    ])->get();
                    if($test->isEmpty()) {
                        unset($user_ids[$key]);
                    }
                }
            }
        }

        $users = User::find($user_ids);
        dd($users);

    }