MySQL Search Filter Query: Optional Filters, No Dynamic SQL

A product list page often has filters: category, minimum and maximum price, a search word, “in stock only”. Each filter is optional. How do you write one query for that?

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

Pattern 1: optional parameters with NULL

Use a parameter value of NULL to mean “no filter”. Here we set variables to show the idea. In a real application, these would be bound parameters of a prepared statement.

query.sql

SQL

SET @category = 'stationery', @min_price = NULL, @max_price = 100, @search = NULL, @in_stock_only = 1;

SELECT name, category, price, stock
FROM products
WHERE (@category IS NULL OR category = @category)
  AND (@min_price IS NULL OR price >= @min_price)
  AND (@max_price IS NULL OR price <= @max_price)
  AND (@search IS NULL OR name LIKE CONCAT('%', @search, '%'))
  AND (@in_stock_only = 0 OR stock > 0)
ORDER BY price;

Output

OUTPUT

+--------------+------------+-------+-------+
| name         | category   | price | stock |
+--------------+------------+-------+-------+
| Gel Pen      | stationery | 15.50 |   300 |
| Sticky Notes | stationery | 35.00 |   210 |
| Notebook     | stationery | 80.00 |   120 |
+--------------+------------+-------+-------+
3 rows in set

Change the variables and run it again:

query.sql

SQL

SET @category = NULL, @min_price = 200, @max_price = NULL, @search = 'a', @in_stock_only = 1;

SELECT name, category, price, stock
FROM products
WHERE (@category IS NULL OR category = @category)
  AND (@min_price IS NULL OR price >= @min_price)
  AND (@max_price IS NULL OR price <= @max_price)
  AND (@search IS NULL OR name LIKE CONCAT('%', @search, '%'))
  AND (@in_stock_only = 0 OR stock > 0)
ORDER BY price;

Output

OUTPUT

+---------------+----------+--------+-------+
| name          | category | price  | stock |
+---------------+----------+--------+-------+
| Water Bottle  | home     | 220.00 |    75 |
| Laptop Sleeve | bags     | 450.00 |    18 |
| Desk Lamp     | home     | 650.00 |    40 |
| Backpack      | bags     | 999.00 |    25 |
+---------------+----------+--------+-------+
4 rows in set

Advantages: one fixed SQL text, safe with prepared statements, easy to read and test.

Drawback: the optimizer sees (? IS NULL OR column = ?) and cannot always choose the best index, because the shape does not change with the parameter. On large tables this can be slow.

Pattern 2: build the WHERE in application code

The application adds only the conditions the user chose, and binds values as parameters. A PHP sketch:

index.php

PHP

$where  = [];
$params = [];

if ($category !== null) { $where[] = 'category = :category'; $params['category'] = $category; }
if ($minPrice !== null) { $where[] = 'price >= :min';        $params['min']      = $minPrice; }
if ($maxPrice !== null) { $where[] = 'price <= :max';        $params['max']      = $maxPrice; }

$sql = 'SELECT name, price FROM products'
     . ($where ? ' WHERE ' . implode(' AND ', $where) : '')
     . ' ORDER BY price LIMIT 20';

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

Only fixed strings written by you go into the SQL text. Every user-supplied value goes into $params. That is what keeps it safe (SQL Injection, PDO Prepared Statements).

The same applies to the sort column and direction. Placeholders cannot be used for identifiers, so allow only values from a fixed list:

index.php

PHP

1
2

$allowed = ['price' => 'price', 'name' => 'name', 'newest' => 'added_on'];
$orderBy = $allowed[$_GET['sort'] ?? ''] ?? 'price';

Pattern 2 gives the optimizer a simple query for each combination, and is the better choice for large tables.

The index for the filters

A product list usually filters by category and sorts or ranges by price. A composite index helps (Composite Indexes):

query.sql

SQL

1
2

CREATE INDEX idx_category_price ON products (category, price);
EXPLAIN FORMAT=TRADITIONAL SELECT name FROM products WHERE category = 'stationery' AND price <= 100 ORDER BY price;

Output

OUTPUT

+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | products | NULL       | range | idx_category_price | idx_category_price | 206     | NULL |    3 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
1 row in set

Text search

name LIKE '%word%' is fine for small catalogues. For thousands of products, use a full-text index (Full-Text Search), or an external search engine (Elasticsearch, Meilisearch, Typesense) when you need typo tolerance, ranking and facets.

Pagination

Always limit the result and use a stable order (LIMIT and Pagination):

query.sql

SQL

SELECT id, name, price FROM products WHERE category = 'stationery' ORDER BY price, id LIMIT 2 OFFSET 0;

Output

OUTPUT

+----+--------------+-------+
| id | name         | price |
+----+--------------+-------+
|  2 | Gel Pen      | 15.50 |
|  8 | Sticky Notes | 35.00 |
+----+--------------+-------+
2 rows in set

Checklist

Every filter is optional and safe with NULL.
Values are always bound parameters.
Column names for sorting come from a whitelist.
There is a LIMIT.
The filters that are used together have a matching index.
You looked at EXPLAIN on a realistic amount of data.

dangerous

Never build SQL by joining user text

"WHERE category = '" . $_GET['c'] . "'" is the textbook SQL injection. Even a single quote in a category name breaks it. Bind every value.