Filtering data from MYSQL database based on parameters passed with POST request I am working on a PHP MVC project for my portfolio. Its an web app where you can store contacts (email and phone number). I am having trouble with filtering contacts based on filter form that has 3 options https://ibb.co/8dJbSWd

I wrote code that works fine but I am sure there is better way of doing this. Here is the code that I wrote for solving this problem.

public function filterContacts($user_id, $group, $email, $phone){

    $query = 'SELECT * FROM contacts WHERE user_id = :user_id ';

    //Nothing is selected
    if($group == '0' &amp;&amp; $email == '' &amp;&amp; $phone == ''){

        $this-&gt;db-&gt;query($query . ' ORDER BY name');
        $this-&gt;db-&gt;bind(':user_id', $user_id);
        return $this-&gt;db-&gt;resultSet();

        //Everything is selected
    } else if ($group !== '0' &amp;&amp; $email !== '' &amp;&amp; $phone !== ''){

        $query .= 'AND contact_group = :group AND email != "" AND phone_number != ""';

        $this-&gt;db-&gt;query($query . 'ORDER BY name');
        $this-&gt;db-&gt;bind(':user_id', $user_id);
        $this-&gt;db-&gt;bind(':group', $group);

        return $this-&gt;db-&gt;resultSet();

        //Just group
    } else if ($group !== '0' &amp;&amp; $email == '' &amp;&amp; $phone == ''){

        $query .= 'AND contact_group = :group ';

        $this-&gt;db-&gt;query($query . 'ORDER BY name');
        $this-&gt;db-&gt;bind(':user_id', $user_id);
        $this-&gt;db-&gt;bind(':group', $group);

        return $this-&gt;db-&gt;resultSet();

        //Just email
    } else if ($group == '0' &amp;&amp; $email !== '' &amp;&amp; $phone == ''){

        $query .= 'AND email != "" ';

        $this-&gt;db-&gt;query($query . 'ORDER BY name');
        $this-&gt;db-&gt;bind(':user_id', $user_id);


        return $this-&gt;db-&gt;resultSet();

        //Just phone
    }  else if ($group == '0' &amp;&amp; $email == '' &amp;&amp; $phone !== ''){

        $query .= 'AND phone_number != "" ';

        $this-&gt;db-&gt;query($query . 'ORDER BY name');
        $this-&gt;db-&gt;bind(':user_id', $user_id);

        return $this-&gt;db-&gt;resultSet();

        //Group and email
    } else if($group !== '0' &amp;&amp; $email !== '' &amp;&amp; $phone == ''){

        $query .= 'AND contact_group = :group AND email != ""';

        $this-&gt;db-&gt;query($query . 'ORDER BY name');
        $this-&gt;db-&gt;bind(':user_id', $user_id);
        $this-&gt;db-&gt;bind(':group', $group);

        return $this-&gt;db-&gt;resultSet();

        //Group and phone number
    } else if($group !== '0' &amp;&amp; $email == '' &amp;&amp; $phone !== ''){
        $query .= 'AND contact_group = :group AND phone_number != ""';

        $this-&gt;db-&gt;query($query . 'ORDER BY name');
        $this-&gt;db-&gt;bind(':user_id', $user_id);
        $this-&gt;db-&gt;bind(':group', $group);

        return $this-&gt;db-&gt;resultSet();

        //Email and phone number
    } else if($group == '0' &amp;&amp; $email !== '' &amp;&amp; $phone !== ''){

        $query .= 'AND phone_number != "" AND email != ""';

        $this-&gt;db-&gt;query($query . 'ORDER BY name');
        $this-&gt;db-&gt;bind(':user_id', $user_id);

        return $this-&gt;db-&gt;resultSet();
    }

}


As you can see I used a lot of if statements to make different queries in Contact model which is possible because i have only 3 filtering options but I cannot imagine doing this with 10 options.

So I was wondering is there a better way of solving this?

Here is link to my github repository if you want to see rest of the code https://github.com/Smiley-dev/Phonebook-demo
The entire long conditional is definitely worth to be decomposed and consolidated.

The main mechanics of optimization is based on 3 aspects:


determining a common logic/behavior that needs to be unified. In your case such a common fragment is:

$this-&gt;db-&gt;query($query . 'ORDER BY name');
$this-&gt;db-&gt;bind(':user_id', $user_id);
return $this-&gt;db-&gt;resultSet();

considering only populated fields
collecting bindings for particular fields




The final optimized function becomes as below:

public function filterContacts($user_id, $group, $email, $phone){

    $query = 'SELECT * FROM contacts WHERE user_id = :user_id';
    $bindings = [':user_id':=&gt; $user_id];

    if ($group !== '0') {
        $query .= ' AND contact_group = :group';
        $bindings[':group'] = $group;
    }    
    if ($email !== '') {
        $query .= ' AND email != ""';
    }
    if ($phone !== '') {
        $query .= ' AND phone_number != ""';
    }

    $this-&gt;db-&gt;query($query . ' ORDER BY name');
    foreach ($bindings as $k =&gt; $v) {
        $this-&gt;db-&gt;bind($k, $v);
    }

    return $this-&gt;db-&gt;resultSet();
}




In case if your DB clent $this-&gt;db allows to pass an array of bindings you can just  replace the loop with $this-&gt;db-&gt;bind($bindings); (check your DB client interface)