When you first tackle a product catalog, the instinct is usually to treat filters as a series of optional "add-ons" to a SQL query. You've got a basic SELECT * FROM products, and then you start thinking about how to handle the category, brand, or price_max parameters coming in via the URL. I've seen a lot of developers start down a path that feels intuitive but quickly turns into a maintenance nightmare.

The Trap of Concatenated If-Statements

The naive approach is to write a long chain of if statements that manually append strings to your SQL query. It looks something like this:

$sql = "SELECT * FROM products WHERE 1=1";
if (!empty($_GET['category'])) {
    $sql .= " AND category = '" . $_GET['category'] . "'";
}
if (!empty($_GET['brand'])) {
    $sql .= " AND brand = '" . $_GET['brand'] . "'";
}
if (!empty($_GET['max_price'])) {
    $sql .= " AND price <= " . $_GET['max_price'];
}
// ... and so on for 10 more filters

At first, this feels fine. It's explicit, and you can see exactly what's happening. But this approach breaks in two major ways. First, it's a security disaster. I can't stress this enough: concatenating $_GET variables directly into a query is an open invitation for SQL injection. Even if you use mysqli_real_escape_string(), you're still writing repetitive, boilerplate code.

Second, it doesn't scale. Imagine your boss asks you to add a "multi-select" for brands, or a "price range" with a minimum and maximum. Suddenly, your if block is 200 lines long. You're spending more time managing string spaces and AND keywords than actually building a feature. It's brittle; one missing space before an AND and your whole page crashes with a syntax error.

Moving Toward a Dynamic Query Map

The better way to handle this is to decouple the input from the query logic. Instead of writing logic for every single filter, we can create a map that defines how each URL parameter relates to a database column. I prefer to collect the conditions and the values separately, then join them at the end.

Think of it as building a list of requirements first, and then translating those requirements into SQL in one clean pass. Here is how I'd actually structure this in a production environment:

$filters = [
    'category' => 'category = :category',
    'brand'    => 'brand = :brand',
    'max_price' => 'price <= :max_price',
    'min_price' => 'price >= :min_price',
];

$conditions = [];
$params = [];

foreach ($filters as $key => $sqlFragment) {
    if (!empty($_GET[$key])) {
        $conditions[] = $sqlFragment;
        $params[$key] = $_GET[$key];
    }
}

$sql = "SELECT * FROM products";
if ($conditions) {
    $sql .= " WHERE " . implode(' AND ', $conditions);
}

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

Why This Wins in the Long Run

The beauty here is the separation of concerns. If you need to add a new filter—say, color—you don't have to write a new if block or worry about the SQL syntax. You simply add one line to the $filters array. The logic that handles the WHERE clause and the parameter binding remains untouched.

By using named placeholders (like :category) and passing the $params array directly into execute(), we've completely eliminated the risk of SQL injection. We've also solved the "trailing AND" problem because implode() only puts the separator between elements. It's cleaner, safer, and most importantly, it's a pattern you can reuse across every single listing page in your application without rewriting the core engine every time.

📋 Practical Task

Exercise: Implementing a Multi-Attribute Filter for a High-End Camera Store

You are building a catalog for a specialty camera shop. The database table cameras has the following columns: id, model, brand, sensor_type (e.g., Full Frame, APS-C), megapixels, and price.

Your task is to implement a dynamic filtering system using the "Query Map" approach discussed in the lesson. Your code must:

• Define a filter map that supports brand, sensor_type, and a min_megapixels filter (where the price is greater than or equal to the input).
• Correctly handle cases where no filters are provided (the query should simply return all cameras).
• Use PDO named placeholders to prevent SQL injection.
• Use implode() to construct the WHERE clause dynamically based on which $_GET parameters are present.

Submit your PHP script showing the filter map definition, the loop that processes the $_GET array, and the final PDO execution.