How do I perform search with filters in PHP?

Question

I need to specify 6 different and distinct Search filters in different ways to carry out search in a mysql database using php. I'm confused on how to go about it.

I'm working a website and i need to carry out very large Search with 6 different search filters.

It seems to be complex to me, because this is my first time of writing this large amount of code with this complex logic.

This is the HTML Code:

<input type='text' name='searchterm' />

<select name='ind' class='custom-select'>
    <option value='0'> Select Industry</option>
    <option value='1'> Real Estate </option>
    <option value='2'> Hospitality / Hotel / Tourism / Travel & Aviation </option>
    <option value='3'> Financial Services / Banking </option>
</select>           

<select name='spec' class='custom-select'>
    <option value='0'> Select Specialization</option>
    <option value='1'> Accounting / Banking & Finance / Insurance </option>
    <option value='2'> Administration / Management / Executive </option>
    <option value='3'> Architecture / Construction  / Civil </option>
</select>           

<select name='loc' class='custom-select'>
    <option value='0'> Select Location</option>
    <option value='1'> Lagos </option>
</select>

The complete HTML Code is here: http://jsbin.com/otibel/1/

There are 6 different select boxes, and each is a Search Filter.

The User can select just one select box and search or select two at a time, or three search filter at a time, and so on.

I managed to do the Search Filter Just for 6, this code is for when the User just selects just one search filter.

function performSearchWithFilter(){
    if ( ($_GET['ind'] > 0) && (isset($_GET['ind'])) && ( $_GET['spec'] <= 0) && ( $_GET['loc'] <= 0 ) && ( $_GET['workexp'] <= 0 )  && ( $_GET['type'] <= 0 ) && ( $_GET['qualfctn'] <= 0 ) ){
        //it will carry out the search, when just the ind select box has been selected
    } else if ( ($_GET['ind'] <= 0) && ($_GET['spec'] > 0) && isset($_GET['spec']) && ( $_GET['loc'] <= 0 ) && ( $_GET['workexp'] <= 0 )  && ( $_GET['type'] <= 0 ) && ( $_GET['qualfctn'] <= 0 ) ) {
        //it will carry out the search, when just the spec select box has been selected
    }  else if ( ($_GET['ind'] <= 0) && ($_GET['spec'] <= 0) && ($_GET['loc'] > 0) && (isset($_GET['loc'])) && ( $_GET['workexp'] <= 0 )  && ( $_GET['type'] <= 0 ) && ( $_GET['qualfctn'] <= 0 ) ) {
        //it will carry out the search, when just the loc select box has been selected
    } else if ( ($_GET['ind'] <= 0) && ($_GET['spec'] <= 0) && ($_GET['loc'] <= 0) && ($_GET['workexp'] > 0) && isset($_GET['workexp'])  && ( $_GET['type'] <= 0 ) && ( $_GET['qualfctn'] <= 0 ) ) {
        //it will carry out the search, when just the workexp select box has been selected
    } else if ( ($_GET['ind'] <= 0) && ($_GET['spec'] <= 0) && ($_GET['loc'] <= 0) && ($_GET['workexp'] <= 0) && ($_GET['type'] > 0) && isset($_GET['type']) && ( $_GET['qualfctn'] <= 0 ) )  {
        //it will carry out the search, when just the type select box has been selected
    } else if ( ($_GET['ind'] <= 0) && ($_GET['spec'] <= 0) && ($_GET['loc'] <= 0) && ($_GET['workexp'] <= 0) && ($_GET['type'] <= 0) && ($_GET['qualfctn'] > 0) && isset($_GET['qualfctn']) ) {
        //it will carry out the search, when just the qualfctn select box has been selected
    }
}

I know I can write the Code for the Search like this, but it will have to be 25 Different else statement, and this look complex.

I used the Permutation and Combination Mathematics Formula to have the 6 Search Parameters arranged in an algorithm like this:

ind, spec, loc, workexp, type, qualfctn, ind & spec, ind & loc, ind & workexp, ind & type, ind & qualfctn, spec & loc, spec & workexp, spec & type, spec & qualfctn, loc & workexp, loc & type, loc & qualfctn, workexp & type, workexp & qualfctn, type & qualfctn, ind & spec & loc & workexp & type & qualfctn, ind & spec & loc & workexp & type, ind & spec & loc & workexp, ind & spec & loc.

This is the Search filter algorithm I made, that is if the User selects the search filter in different ways.

What Search Plugin, Framework or Librbary in PHP can do this for me, Search with multiple filters, and minimize the amount of code i write, so I won't have to start writing this Large chunk of code, or reinventing the wheel all over again, I'm really confused on what to do and how to do it.

Answer 1

A somewhat lengthy code to read all the variables but once they've been read, a simple query statement to run your search. I've made some assumptions as to the structure of the database table as explained in the comment and naturally you'll need to initialize the database connection.

// this code assumes that $link has been initialized as a mysqli object 
// with database connection open.   
// assuming database table name is "people"
// assuming database table column names match the names of GET variables
// if any of these assumptions are incorrect, you'll need to modify the 
// code to match the DB

// initialize WHERE conditions
$conditions = "1=1";

// test for ind
if(isset($_GET['ind']) && ($_GET['ind'] > 0)) {
    $conditions .= " AND ind='".$link->real_escape_string($_GET['ind'])."'";
}

// test for spec
if(isset($_GET['spec']) && ($_GET['spec'] > 0)) {
    $conditions .= " AND spec='".$link->real_escape_string($_GET['spec'])."'";
}

// test for loc
if(isset($_GET['loc']) && ($_GET['loc'] > 0)) {
    $conditions .= " AND loc='".$link->real_escape_string($_GET['loc'])."'";
}

// test for workexp
if(isset($_GET['workexp']) && ($_GET['workexp'] > 0)) {
    $conditions .= " AND workexp='".$link->real_escape_string($_GET['workexp'])."'";
}

// test for type
if(isset($_GET['type']) && ($_GET['type'] > 0)) {
    $conditions .= " AND type='".$link->real_escape_string($_GET['type'])."'";
}

// test for qualfctn
if(isset($_GET['qualfctn']) && ($_GET['qualfctn'] > 0)) {
    $conditions .= " AND qualfctn='".$link->real_escape_string($_GET['qualfctn'])."'";
}

// make sure we have at least one condition
if(!$first) {
    if(!$result = $link->query("
        SELECT *
        FROM people
        WHERE ".$conditions
    )) {
        // your error handling here
    }

    // read the results here

    $result->free();
}