Refactoring Procedural Code for a Product Filter

original.php

<?php

    $SQL = "SELECT inv.*, COUNT(*) OVER() AS totalrows FROM stones inv WHERE wt >= 2.5";

    if (isset($_POST["ItemSearch"])) $SQL .= "AND number LIKE '" . $_POST["ItemSearch"] . "%'";

    if (isset($_POST["minimum_wt"], $_POST["maximum_wt"]) && !empty($_POST["minimum_wt"]) && !empty($_POST["maximum_wt"])) $SQL .= "AND wt BETWEEN '" . $_POST["minimum_wt"] . "' AND '" . $_POST["maximum_wt"] . "'";

    if (isset($_POST["shape"])) {

        $ShapeFilter = implode("','", $_POST["shape"]);

        $SQL .= "AND stoneshape IN('" . $ShapeFilter . "')";

    }

    if (isset($_POST["color"])) {

        $ColorFilter = implode("','", $_POST["color"]);

        $SQL .= "AND stonecolor IN('" . $ColorFilter . "')";

    }

    if (isset($_POST["enhancement"])) {

        $EnhancementFilter = implode("','", $_POST["enhancement"]);

        $SQL .= "AND enhcode IN('" . $EnhancementFilter . "')";

    }

    if (isset($_POST["matching"])) {

        $MatchingFilter = implode("','", $_POST["matching"]);

        $SQL .= "AND pair IN('" . $MatchingFilter . "')";   

    }

    // Pagination
    $PageNo = $_REQUEST['PageNo']; // Get page number

    $Limit = 25; // Items per page

    $Offset = ($PageNo - 1) * $Limit; // Current page times the amount per page

    $SQL .= "AND inactive LIKE 0 ORDER BY wt ASC LIMIT $Offset, $Limit;"; // $Offset and $Limit are declared in their respective variables

    $MySQLiQuery = mysqli_query($db, $SQL); // Preform the query against the database

?>

refactored.php

<?php
/**
 * Adds a filter to the query that uses a SQL IN() clause to match a series of fields
 *
 * @param string $filterColumnName The column for which the filter is being added
 * @param array $filterValues Array of values used for the IN clause
 * @param string[] $queryFilters Existing array of filter strings
 * @param string[] $queryParams Associative array of parameters
 * @return void
 */
function addComplexFilter(string $filterColumnName, array $filterValues, array &$queryFilters, array &$queryParams): void
{
    $filterString = $filterColumnName . ' IN(';
    foreach ($filterValues as $index => $singleFilterValue) {
        $filterValueKey = ":{$filterColumnName}_" . ($index + 1);
        $filterString .= " $filterValueKey, ";
        $queryParams[$filterValueKey] = $singleFilterValue;
    }
    $filterString = trim($filterString, ',') . ')';
    $queryFilters[] = $filterString;
}

//Create the query filter with required where clauses and array for parameters
$queryFilters = ['inactive = 0', 'wt >= 2.5'];
$queryParams = [];

//Get the current page and calculate teh required pagination data
$page = isset($_REQUEST['PageNo']) && $_REQUEST['PageNo'] > 0 ? (int) $_REQUEST['PageNo'] : 1;
$limit = 25;
$offset = ($page - 1) * $limit;

//Handle single-value filters
if (isset($_POST['ItemSearch'])) {
    $queryFilters[] = 'number LIKE :item_search';
    $queryParams[':item_search'] = '%' . $_POST['ItemSearch'] . '%';
}
if (isset($_POST['minimum_wt'])) {
    $queryFilters[] = 'wt >= :minimum_wt';
    $queryParams[':minimum_wt'] = $_POST['minimum_wt'];
}
if (isset($_POST['maximum_wt'])) {
    $queryFilters[] = 'wt <= :maximum_wt';
    $queryParams[':maximum_wt'] = $_POST['maximum_wt'];
}

//Handle complex filters
if (isset($_POST['shape'])) {
    addComplexFilter('stoneshape', $_POST['shape'], $queryFilters, $queryParams);
}
if (isset($_POST['color'])) {
    addComplexFilter('stonecolor', $_POST['color'], $queryFilters, $queryParams);
}
if (isset($_POST['enhancement'])) {
    addComplexFilter('enhcode', $_POST['enhancement'], $queryFilters, $queryParams);
}
if (isset($_POST['matching'])) {
    addComplexFilter('pair', $_POST['matching'], $queryFilters, $queryParams);
}

//combine the individual filter elements into a single where clause
$filterClause = implode(' AND ', $queryFilters);

//create the query string
$queryString = <<<SQL
SELECT inv.*, COUNT(*) OVER() AS totalrows
FROM stones inv
WHERE $filterClause
ORDER BY wt ASC 
LIMIT $offset, $limit
SQL;

//execute the query
//while the original uses mysqli, I'm rewriting $db as a PDO instance
$query = $db->prepare($queryString);
$query->execute($queryParams);