PHP MySQL Where

Selecting every row in a table is rarely useful once that table has more than a handful of rows -- you almost always want just the rows matching some condition, like a specific user's ID, or every product under $50. The WHERE clause filters a SELECT (or UPDATE, or DELETE) statement down to exactly the rows that match.

In this page:

Basic WHERE Filtering
Comparison Operators in WHERE
Combining Conditions with AND and OR
Pattern Matching with LIKE
Checking for NULL and Matching a List with IN

Basic WHERE Filtering

Adding WHERE column = value to a SELECT statement restricts the results to only rows matching that condition -- SELECT * FROM users WHERE status = active returns only the active users, ignoring every other row in the table.

Note: Filter as much as possible in the SQL WHERE clause itself, rather than selecting everything and filtering with PHP afterward -- the database does this far more efficiently.

Warning: A WHERE clause with a typo in the column name produces an SQL error, not a query that simply matches nothing.

Example: Basic WHERE Filtering

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT, status TEXT)");
$db->exec("INSERT INTO users VALUES ('Alice','active'), ('Bob','inactive')");
$result = $db->query("SELECT * FROM users WHERE status = 'active'");
print_r($result->fetchArray(SQLITE3_ASSOC));
?>

Comparison Operators in WHERE

Beyond exact equality, WHERE supports the full range of comparison operators -- >, <, >=, <=, and != -- for numeric and date comparisons, like finding every product with a price greater than 50, or every order placed after a certain date.

Note: Use >= and <= (inclusive) versus > and < (exclusive) deliberately, matching exactly which boundary values should or should not be included.

Warning: Comparing a date stored as a string against another string with the wrong format can produce unexpected results -- ensure date columns and comparison values use a consistent format.

Example: Comparison Operators in WHERE

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE products (name TEXT, price INTEGER)");
$db->exec("INSERT INTO products VALUES ('Book', 60), ('Pen', 10)");
$result = $db->query("SELECT * FROM products WHERE price > 50");
print_r($result->fetchArray(SQLITE3_ASSOC));
?>

Combining Conditions with AND and OR

AND requires every listed condition to be true for a row to match, while OR requires at least one -- combining them (with parentheses for clarity) lets you build precise, multi-part filters, like "active users who are also over 18".

Note: Use parentheses to group OR conditions explicitly when mixing them with AND, since SQL's default operator precedence can otherwise produce a filter different from what you intended.

Warning: Mixing AND and OR without parentheses can silently group conditions differently than expected, since AND has higher precedence than OR by default.

Example: Combining Conditions with AND and OR

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT, status TEXT, age INTEGER)");
$db->exec("INSERT INTO users VALUES ('Alice','active',20)");
$result = $db->query("SELECT * FROM users WHERE status = 'active' AND age > 18");
print_r($result->fetchArray(SQLITE3_ASSOC));
?>

Pattern Matching with LIKE

LIKE matches text against a pattern using % as a wildcard for any number of characters -- WHERE name LIKE '%smith%' finds every name containing smith anywhere within it, which is useful for search-style filtering that an exact = comparison cannot do.

Note: Wrap a LIKE search term in wildcards (%term%) for a "contains" search, or use just one side (term%) for a "starts with" search.

Warning: A LIKE pattern beginning with a wildcard (%term) generally cannot use a standard index efficiently, which can make it noticeably slower on large tables.

Example: Pattern Matching with LIKE

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
$db->exec("INSERT INTO users VALUES ('John Smith'), ('Jane Doe')");
$result = $db->query("SELECT * FROM users WHERE name LIKE '%smith%'");
print_r($result->fetchArray(SQLITE3_ASSOC));
?>

Checking for NULL and Matching a List with IN

WHERE column IS NULL checks for a missing value (= NULL never matches, even against another NULL, due to SQL's three-valued logic), and WHERE column IN (v1, v2, v3) matches any row whose value is one of several listed options, compactly replacing a long chain of OR conditions.

Note: Use IS NULL / IS NOT NULL specifically for null checks -- a plain = or != comparison against NULL always evaluates to unknown, never matching anything.

Warning: Using = NULL instead of IS NULL is a common and confusing mistake, since it silently matches zero rows rather than raising any kind of error.

Example: Checking for NULL and Matching a List with IN

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT, phone TEXT)");
$db->exec("INSERT INTO users VALUES ('Alice', NULL), ('Bob', '555-1234')");
$result = $db->query("SELECT * FROM users WHERE phone IS NULL");
print_r($result->fetchArray(SQLITE3_ASSOC));
$result2 = $db->query("SELECT * FROM users WHERE name IN ('Alice', 'Carol')");
print_r($result2->fetchArray(SQLITE3_ASSOC));
?>

Common Mistakes

Building a WHERE clause by concatenating raw user input directly into the SQL string, opening the same SQL injection risk as an unsafe INSERT.
Using a single = for an exact match when the intended comparison was actually a range or pattern match, like wanting contains behavior but using = instead of LIKE.
Forgetting that string values in a WHERE clause need to be quoted in raw SQL, while numeric values do not -- though prepared statements avoid this distinction entirely.

Chapter Summary

WHERE condition filters a query down to only the rows where that condition evaluates to true.
Comparison operators (=, >, <, LIKE) and logical operators (AND, OR) combine to build precise filtering conditions.
WHERE clause values from user input should always be bound through a prepared statement, never concatenated directly into the SQL string.

Browser Support

The WHERE clause is standard SQL and works identically across all MySQL and MariaDB versions PHP supports.

🔒

Chapter Quiz — Complete all 21 topics to unlock

0/21 topics done

Complete these topics first:

← Previous Lesson PHP MySQL Select Data Next Lesson → PHP MySQL Order By