PHP Filter Data from MySQL with a Dynamic WHERE

Filters let visitors narrow a list: by category, price range or availability. Each filter is optional, so the WHERE clause is built on the fly.

The safe method has two lists:

$where: pieces of SQL that you write (never user text).
$params: the values, bound with placeholders.

index.php

PHP

<?php
final class ProductRepository
{
    public function __construct(private PDO $pdo) {}

    /**
     * @param array{category?: string, min_price?: float, max_price?: float, in_stock?: bool} $filters
     */
    public function filter(array $filters): array
    {
        $where = [];
        $params = [];

        if (!empty($filters["category"])) {
            $where[] = "category = :category";
            $params["category"] = $filters["category"];
        }

        if (isset($filters["min_price"])) {
            $where[] = "price >= :min_price";
            $params["min_price"] = $filters["min_price"];
        }

        if (isset($filters["max_price"])) {
            $where[] = "price <= :max_price";
            $params["max_price"] = $filters["max_price"];
        }

        if (!empty($filters["in_stock"])) {
            $where[] = "stock > 0";
        }

        $sql = "SELECT name, price, stock FROM products";

        if ($where !== []) {
            $sql .= " WHERE " . implode(" AND ", $where);
        }

        $sql .= " ORDER BY name";

        $statement = $this->pdo->prepare($sql);
        $statement->execute($params);

        return $statement->fetchAll();
    }
}

$repo = new ProductRepository(require __DIR__ . "/db.php");

$result = $repo->filter(["category" => "writing", "min_price" => 20, "in_stock" => true]);
echo implode(", ", array_column($result, "name")) . "\n";

echo count($repo->filter([])) . " products with no filters";

Output

OUTPUT

1
2

Marker
5 products with no filters

Why this is safe

The text in $where always comes from your code.
The visitor’s values go only into $params and are bound.
With no filters, the query has no WHERE at all.

Read the filters from the URL

Turn the raw query string into validated filters first.

index.php

PHP

<?php
function readFilters(array $query): array
{
    $filters = [];

    $category = trim((string) ($query["category"] ?? ""));
    if ($category !== "") {
        $filters["category"] = $category;
    }

    foreach (["min_price", "max_price"] as $key) {
        $value = filter_var($query[$key] ?? "", FILTER_VALIDATE_FLOAT);
        if ($value !== false && $value >= 0) {
            $filters[$key] = $value;
        }
    }

    if (($query["in_stock"] ?? "") === "1") {
        $filters["in_stock"] = true;
    }

    return $filters;
}

print_r(readFilters(["category" => "paper", "min_price" => "abc", "max_price" => "100", "in_stock" => "1"]));

Output

OUTPUT

Array
(
    [category] => paper
    [max_price] => 100
    [in_stock] => 1
)

The invalid min_price was dropped. The filter form shows the same values again, so the visitor can adjust them.

Do not type the options by hand. Read them:

index.php

PHP

<?php
$pdo = require __DIR__ . "/db.php";

$categories = $pdo->query("SELECT DISTINCT category FROM products ORDER BY category")->fetchAll(PDO::FETCH_COLUMN);

echo implode(", ", $categories);

Output

OUTPUT

bags, paper, writing

Validate a submitted category against this list, too.

index.php

PHP

<form method="get" action="index.php">
  <label for="category">Category</label>
  <select id="category" name="category">
    <option value="">All</option>
    <?php foreach ($categories as $c): ?>
      <option value="<?= h($c) ?>" <?= ($filters["category"] ?? "") === $c ? "selected" : "" ?>><?= h($c) ?></option>
    <?php endforeach; ?>
  </select>

  <label for="min_price">Min price</label>
  <input id="min_price" name="min_price" inputmode="decimal" value="<?= h((string) ($filters["min_price"] ?? "")) ?>">

  <label><input type="checkbox" name="in_stock" value="1" <?= !empty($filters["in_stock"]) ? "checked" : "" ?>> In stock only</label>

  <button type="submit">Filter</button>
</form>

Combine with search, sort and pagination

All four features live in the same WHERE and ORDER BY. The mini app in Building a Simple PHP CRUD App puts them together. The count query for pagination must use the same filters.

lightbulb

Never build SQL from field names the user sends

Keep a fixed list of filters you support. A visitor cannot add a filter you did not write.

Filtering Data

Why this is safe

Read the filters from the URL

Filter form

Never build SQL from field names the user sends

Why this is safe

Read the filters from the URL

The category list from the database

Filter form

Combine with search, sort and pagination

Never build SQL from field names the user sends