php - PDO filter system with Ajax. Is there a better way?

Question

I am trying to filter some inputs of the user with select boxes. I am figuring if there is a better way doing this.

    if(isset($_POST['action']))
    {
        
        $sql = "SELECT * FROM occasions WHERE naam IS NOT NULL";
        
        if(isset($_POST['merk'])){
            
            $merk = $_POST['merk'];
            
            $merkQuery = implode(',', array_fill(0, count($merk), '?'));
            
            $sql .= " AND merk IN(".$merkQuery.")";
        }
        
        if(isset($_POST['brandstof'])){
            
            $brandstof = $_POST['brandstof'];
            
            $brandstofQuery = implode(',', array_fill(0, count($brandstof), '?'));
            
            $sql .= " AND brandstof IN(".$brandstofQuery.")";
        }
        
        
        //We prepare our SELECT statement.
        $statement = $pdo->prepare($sql);
        
        
        
        if(isset($_POST['merk'])){
            //Execute statement.
            $statement->execute(array_merge(array_values($merk)));
        }

        if(isset($_POST['brandstof'])){
            //Execute statement.
            $statement->execute(array_merge(array_values($brandstof)));
        }
        
        if(isset($_POST['merk']) && isset($_POST['brandstof']))
        {
            $statement->execute(array_merge(array_values($merk), array_values($brandstof)));
        }
        else
        {
            $statement->execute();
        }
   }

Cause if there are many select boxes that need filtering, the code would become long. I was wondering if there is a better way of filtering multiple select boxes.

Here is an example: link

Answer 1

I would suggest renaming the post variables; grouping them into a single two dimensional array.

<input type="checkbox" name="data[merk][bmw]" />
<input type="checkbox" name="data[merk][skoda] />

and so forth.

What this does, is it allows you to use a foreach to iterate through whatever values are checked.

$data = $_POST['data'] ?? []; // null coalesce defaults to a blank array if post var is null
foreach($data as $category=>$val) {
    settype($val, 'array');
    $query = implode(',', array_fill(0, count($val), '?'));
    foreach($val as $k=>$v) {
        $params[] = $k;
    }

    // DON'T DO THIS!
    $sql .= " AND $category IN(".$query.")";
}

The reason you shouldn’t do it as shown is because you should never build a query with user-supplied data.

What you can do, however, is map user-supplied data with hard-coded data.

$map = [
    // form value => db field
    'merk'      => 'MERK',
    'brandstof' => 'BRANDSTOF',
    // ... etc
];

and then when building your query,

$sql .= " AND $map[$category] IN($query)";

In the meantime, you have built your parameters in $params.

—-

Bottom line, what we have done is refactor the code since we were noticing things getting repeated. For example, you were having to repeat code for each occasion(?). One solution would be to continue to check each post value and call a function to calculate the ?s. But even then, it would be repetitive to type out all those isset()s.

---

In retrospect, it probably would have been better to do inputs like this:

<input type="checkbox" name="data[merk][]" value="bmw" />
<input type="checkbox" name="data[merk][]" value="skoda" />

This would no doubt be more intuitive, although you would still have to build the params array.

foreach($val as $v) {
    $params[] = $v;
}