← Back to PHP Course | Chapter 11: Database | Lesson 17 of 21

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:

  1. Basic WHERE Filtering
  2. Comparison Operators in WHERE
  3. Combining Conditions with AND and OR
  4. Pattern Matching with LIKE
  5. Checking for NULL and Matching a List with IN
Syntax
php 
SELECT * FROM table_name WHERE column = value;
SELECT * FROM table_name WHERE column > value AND other_column = value;
SELECT * FROM table_name WHERE column = value OR other_column = value;

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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT, status TEXT)");
$db->exec("INSERT INTO users VALUES ('Alice','active'), ('Bob','inactive')");
// Declare `$result`, set to `$db->query("SELECT * FROM users WHERE status = 'active'")`
$result = $db->query("SELECT * FROM users WHERE status = 'active'");
// Print a human-readable dump of `$result->fetchArray(SQLITE3_ASSOC)`
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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE products (name TEXT, price INTEGER)");
$db->exec("INSERT INTO products VALUES ('Book', 60), ('Pen', 10)");
// Declare `$result`, set to `$db->query("SELECT * FROM products WHERE price > 50")`
$result = $db->query("SELECT * FROM products WHERE price > 50");
// Print a human-readable dump of `$result->fetchArray(SQLITE3_ASSOC)`
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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT, status TEXT, age INTEGER)");
$db->exec("INSERT INTO users VALUES ('Alice','active',20)");
// Declare `$result`, set to `$db->query("SELECT * FROM users WHERE status = 'active' AND age > 18")`
$result = $db->query("SELECT * FROM users WHERE status = 'active' AND age > 18");
// Print a human-readable dump of `$result->fetchArray(SQLITE3_ASSOC)`
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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
$db->exec("INSERT INTO users VALUES ('John Smith'), ('Jane Doe')");
// Declare `$result`, set to `$db->query("SELECT * FROM users WHERE name LIKE '%smith%'")`
$result = $db->query("SELECT * FROM users WHERE name LIKE '%smith%'");
// Print a human-readable dump of `$result->fetchArray(SQLITE3_ASSOC)`
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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT, phone TEXT)");
$db->exec("INSERT INTO users VALUES ('Alice', NULL), ('Bob', '555-1234')");
// Declare `$result`, set to `$db->query("SELECT * FROM users WHERE phone IS NULL")`
$result = $db->query("SELECT * FROM users WHERE phone IS NULL");
// Print a human-readable dump of `$result->fetchArray(SQLITE3_ASSOC)`
print_r($result->fetchArray(SQLITE3_ASSOC));
// Declare `$result2`, set to `$db->query("SELECT * FROM users WHERE name IN ('Alice', 'Carol')")`
$result2 = $db->query("SELECT * FROM users WHERE name IN ('Alice', 'Carol')");
// Print a human-readable dump of `$result2->fetchArray(SQLITE3_ASSOC)`
print_r($result2->fetchArray(SQLITE3_ASSOC));
?>
Related Topics

PHP MySQL Select Data · PHP MySQL Order By · PHP MySQL Prepared Statements · PHP MySQL Delete Data

{# common_mistakes/chapter_summary/browser_support: on Hindi pages the view already swaps in the hi_ translation fields (or blanks these out if untranslated), so this renders correctly for both languages without a lang_code check here. #}
Common Mistakes
  1. Building a WHERE clause by concatenating raw user input directly into the SQL string, opening the same SQL injection risk as an unsafe INSERT.
  2. 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.
  3. 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

Login to run this code

C/C++/Java/PHP execution requires a free account. Your code is saved — you'll land right back in the editor after logging in.