How to create an optimized sql query for multiple dropdown filtering in php?

Am new to php and working on a project. At the beginning there was 2 filters for which I didn’t worry much in writing the sql query with 4 conditions. But now I’ve 8 different dropdowns and I need to write an optimized sql for filter functionality. As per conditions to be written counted, I need to write 256 conditions which is the worst idea at all.

Here the problem is, there is no mandatory filed to choose for filtering. This giving me the more problem in applying different approaches.

Is there any other alternative to achieve this issue? what would be the best idea for optimized query.

Example Code

if($_REQUEST['action']=='action_report'){
   $v1 = $_POST['v1'];
   $v2 = $_POST['v2'];

  if(!empty($v1) && !empty ($v2)){
     $sql = "SELECT * FROM TABLE WHERE v1=$v1 AND v2=$v2 AND action='action_report'";
  }elseif(!empty($v1) && empty($v2)){
     $sql = "SELECT * FROM TABLE WHERE v1=$v1 AND action='action_report'";
  }elseif(empty($v1) && !empty($v2)){
     $sql = "SELECT * FROM TABLE WHERE v2=$v2 AND action='action_report'";
  }elseif(empty($v1) && empty($v2)){
     $sql = "SELECT * FROM TABLE WHEREAND action='action_report'";
  }
}

Answer

The code would look like this:

$sql = 'SELECT * FROM TABLE WHERE ';
$first = true;
foreach($_POST as $paramName => $value) {
    if ($first) {
        $sql .= "{$paramName}='{$value}'";
        $first = false;
        continue;
    }
    $sql .= " AND {$paramName}='{$value}'";
}

Since the $_POST is an array of the incoming variables you can go through on it with a simple foreach cycle and attach it to the SQL query string. The first possible parameter wont need an AND operator, so you have to handle it differently, that’s why the $first variable is for.

However this code is has SQL injection vulnerabilities, so it’s better to attach the parameter name and the value to the SQL string like this:

$sql .= ' AND ' . mysqli_real_escape_string($connection, htmlspecialchars($paramName)) . "='" . mysqli_real_escape_string($connection, htmlspecialchars($value)) . "'";

You will also receive empty values, you wouldn’t like to attach to the SQL query string. So the final code needs to handle that too, and after a bit of formatting it would look like this:

$sql = 'SELECT * FROM TABLE WHERE ';
$first = true;
foreach($_POST as $paramName => $value) {
    $protectedParamName = mysqli_real_escape_string($connection, htmlspecialchars($paramName));
    $protectedValue = mysqli_real_escape_string($connection, htmlspecialchars($value));

    if (empty($value)) {
        continue;
    }

    if ($first) {
        $sql .= "{$protectedParamName}='{$protectedValue}'";
        $first = false;
        continue;
    }

    $sql .= " AND {$protectedParamName}='{$protectedValue}'";
}

In the example the $connection variable is a mysqli object:

$connection = new mysqli(
    $dbConfig['host'],
    $dbConfig['user'],
    $dbConfig['password'],
    $dbConfig['databaseName'],
    $dbConfig['port']
);

The foreach($_POST as $paramName => $value) goes through on each $_POST array values, so if you would you don’t want some fields to be used in the SQL query, then you can use blacklist filtering, where you specify if the $paramName is in the blacklist, then you wont attach it to the SQL query.

For example:

$blackList = [
    'action'
];
foreach($_POST as $paramName => $value) {
    if (in_array($paramName, $blackList)) {
        continue;
    }
}