MySQL Where

Filtering Rows With WHERE

In simple terms: Usually you do not want all rows from a table — you want the products in one category, the orders of one customer, or the items cheaper than ₹1,000. The WHERE clause tells the database which rows to return (or update, or delete), so only matching data is processed and sent to PHP.

Technically: WHERE takes a condition that is evaluated for every row; only rows for which it is true are included. Conditions use comparison operators, BETWEEN, IN, LIKE, IS NULL, and logical operators AND, OR, NOT. Whenever a condition contains values from outside the code, they must be passed as prepared statement parameters (see MySQL Prepared).

The examples use the products from MySQL Insert Multiple:

A Basic WHERE Clause

<?php
require __DIR__ . '/src/db.php';

$category = 'electronics';   // e.g. from $_GET['category']

$result = db()->execute_query(
    'SELECT name, price FROM products WHERE category = ? ORDER BY id',
    [$category]
);

foreach ($result as $row) {
    echo "{$row['name']}: ₹{$row['price']}\n";
}

Output

Desk Lamp: ₹1299.00
Wireless Mouse: ₹599.00

The ? placeholder receives the category value. MySQL compares each row's category with it and returns only the matches. Because the table uses a case-insensitive collation, 'Electronics' would match too.

Comparison Operators

Ranges With BETWEEN

<?php
require __DIR__ . '/src/db.php';

$result = db()->execute_query(
    'SELECT name, price FROM products WHERE price BETWEEN ? AND ? ORDER BY id',
    [50, 900]
);

foreach ($result as $row) {
    echo "{$row['name']}: ₹{$row['price']}\n";
}

Output

Notebook A5: ₹60.00
Backpack: ₹899.00
Wireless Mouse: ₹599.00

BETWEEN 50 AND 900 includes both limits (equivalent to price >= 50 AND price <= 900).

Searching Text With LIKE

<?php
require __DIR__ . '/src/db.php';

function likePattern(string $term): string
{
    return '%' . addcslashes($term, '%_\\') . '%';
}

foreach (['lamp', 'pen', '100%'] as $term) {
    $names = array_column(
        db()->execute_query('SELECT name FROM products WHERE name LIKE ? ORDER BY id', [likePattern($term)])->fetch_all(MYSQLI_ASSOC),
        'name'
    );

    echo str_pad("'$term'", 8) . '→ ' . ($names === [] ? 'no results' : implode(', ', $names)) . "\n";
}

Output

'lamp'  → Desk Lamp
'pen'   → Gel Pen
'100%'  → no results

How It Works

1. % around the term means "any characters before and after", so %lamp% matches "Desk Lamp" (case-insensitively).
2. % and _ are wildcards in LIKE. If a user searches for 100%, the % would otherwise match anything. addcslashes() escapes %, _, and `` with a backslash, so they are matched literally.
3. The pattern is still passed as a parameter, never inserted into the SQL.

LIKE '%term%' cannot use a normal index, so it scans the whole table. That is fine for small tables; for large catalogs, use MySQL's FULLTEXT indexes or a search engine.

Combining Conditions: AND, OR, and Parentheses

AND binds more tightly than OR — just as multiplication binds more tightly than addition. Missing parentheses are a classic source of bugs:

<?php
require __DIR__ . '/src/db.php';

$db = db();

$withoutParentheses = "SELECT name FROM products
    WHERE category = 'stationery' OR category = 'electronics' AND stock > 50
    ORDER BY id";

$withParentheses = "SELECT name FROM products
    WHERE (category = 'stationery' OR category = 'electronics') AND stock > 50
    ORDER BY id";

foreach (['Without' => $withoutParentheses, 'With' => $withParentheses] as $label => $sql) {
    $names = array_column($db->query($sql)->fetch_all(MYSQLI_ASSOC), 'name');
    echo "$label parentheses: " . implode(', ', $names) . "\n";
}

Output

Without parentheses: Gel Pen, Notebook A5
With parentheses: Gel Pen

Without parentheses, MySQL reads the condition as "stationery (any stock) OR (electronics with stock > 50)", so the Notebook with only 45 in stock is included. The intended rule — "stationery or electronics, and more than 50 in stock" — requires parentheses. Always use parentheses when mixing AND and OR.

NULL Needs Special Operators

NULL means "unknown". Any comparison with NULL using =, <>, <, or > results in "unknown", which is treated as not true:

SELECT name FROM customers WHERE city = NULL;      -- always returns no rows
SELECT name FROM customers WHERE city IS NULL;     -- customers without a city
SELECT name FROM customers WHERE city IS NOT NULL; -- customers with a city

When a PHP value may be null (for example, an optional filter), the null-safe equality operator <=> compares correctly in both cases: city <=> ? matches NULL when the parameter is null, and matches the city otherwise.

Practical Example: Optional Search Filters

A product search page offers optional filters — category, maximum price, in-stock only, and a search term. The WHERE clause is built from the filters that are actually set, always with placeholders:

<?php
require __DIR__ . '/src/db.php';

function findProducts(mysqli $db, array $filters): array
{
    $conditions = [];
    $params = [];

    if (($filters['category'] ?? '') !== '') {
        $conditions[] = 'category = ?';
        $params[] = $filters['category'];
    }

    if (isset($filters['max_price'])) {
        $conditions[] = 'price <= ?';
        $params[] = $filters['max_price'];
    }

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

    if (($filters['search'] ?? '') !== '') {
        $conditions[] = 'name LIKE ?';
        $params[] = '%' . addcslashes($filters['search'], '%_\\') . '%';
    }

    $where = $conditions === [] ? '' : 'WHERE ' . implode(' AND ', $conditions);

    $result = $db->execute_query(
        "SELECT name FROM products $where ORDER BY id",
        $params === [] ? null : $params
    );

    return array_column($result->fetch_all(MYSQLI_ASSOC), 'name');
}

$db = db();

echo implode(', ', findProducts($db, ['category' => 'electronics', 'max_price' => 1000, 'in_stock' => true])) . "\n";
echo implode(', ', findProducts($db, ['search' => 'note'])) . "\n";
echo implode(', ', findProducts($db, [])) . "\n";

Output

Wireless Mouse
Notebook A5
Gel Pen, Notebook A5, Desk Lamp, Backpack, Wireless Mouse

Step-by-Step Explanation

1. Each active filter adds a condition with placeholders (or a fixed condition such as stock > 0) and, where needed, a parameter.
2. Conditions are combined with AND. Only fixed SQL fragments written in the code are concatenated; all user values go into $params.
3. Without filters, there is no WHERE clause and all products are returned. In that case, null is passed instead of an empty array, because MySQLi rejects an empty parameter array with a ValueError.
4. In the first call, the Desk Lamp (₹1,299) is excluded by the price limit, leaving only the Wireless Mouse.

In real code, the filter values would come from $_GET after validation — for example, max_price with FILTER_VALIDATE_FLOAT (see PHP Filters).

Performance: WHERE and Indexes

Columns that appear frequently in WHERE clauses (foreign keys, status, dates) should be indexed (see MySQL Create Table).

Common Mistakes

Inserting Values Into the WHERE Clause

Effect: SQL injection.

Fix: Always use placeholders.

Mixing AND and OR Without Parentheses

Effect: Wrong rows are returned.

Fix: Group conditions explicitly.

Using = NULL

Effect: No rows match.

Fix: Use IS NULL, IS NOT NULL, or <=>.

Not Escaping LIKE Wildcards

Effect: Searches for % or _ match unexpected rows.

Fix: Escape them with addcslashes($term, '%_0').

Best Practices

• Filter in the database with WHERE instead of loading all rows into PHP.
• Pass every external value as a prepared-statement parameter.
• Use parentheses whenever AND and OR are combined.
• Handle NULL with IS NULL / <=>.
• Write conditions that can use indexes, and index frequently filtered columns.

Official reference: https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php