MySQL WHERE Clause
Filter and retrieve specific data from your database
🔍 What is WHERE Clause?
The WHERE clause filters database records based on specific conditions. It allows you to retrieve, update, or delete only the rows that match your criteria, making queries more precise and efficient.
<?php
// Filter data with WHERE
$sql = "SELECT * FROM users WHERE age > 25";
$result = $conn->query($sql);
?>
Key Operators
Comparison
Equal, not equal, greater, less
// Operators
=, !=, >, <, >=, <=Logical
Combine multiple conditions
// AND, OR, NOT
WHERE age > 25 AND city = 'NYC'Pattern Match
Search with wildcards
// LIKE operator
WHERE name LIKE 'John%'Range
Check values in range
// BETWEEN operator
WHERE age BETWEEN 20 AND 30🔹 Basic WHERE Clause
Filter records using simple comparison operators. The WHERE clause comes after the FROM clause and specifies which rows to include in the result.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Select users with age greater than 25
$sql = "SELECT name, email, age FROM users WHERE age > 25";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<h3>Users older than 25:</h3>";
while($row = $result->fetch_assoc()) {
echo "Name: " . $row["name"] . " - Age: " . $row["age"] . "<br>";
}
} else {
echo "No matching records";
}
$conn->close();
?>Output:
Users older than 25:
Name: John Doe - Age: 30
Name: Bob Johnson - Age: 28
🔹 WHERE with Text Values
Filter records by text fields using the equality operator. Always enclose text values in quotes to avoid SQL errors.
<?php
$conn = new mysqli("localhost", "root", "", "myDB");
// Select user by name (text comparison)
$sql = "SELECT * FROM users WHERE name = 'John Doe'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
$row = $result->fetch_assoc();
echo "Found user: " . $row["name"] . "<br>";
echo "Email: " . $row["email"] . "<br>";
echo "Age: " . $row["age"];
} else {
echo "User not found";
}
$conn->close();
?>Output:
🔹 AND Operator
Combine multiple conditions where all must be true. Use AND when you need records that satisfy every condition simultaneously.
<?php
$conn = new mysqli("localhost", "root", "", "myDB");
// Multiple conditions with AND
$sql = "SELECT name, age, city FROM users WHERE age > 25 AND city = 'New York'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<h3>Users over 25 in New York:</h3>";
while($row = $result->fetch_assoc()) {
echo $row["name"] . " - Age: " . $row["age"] . " - City: " . $row["city"] . "<br>";
}
} else {
echo "No matching users";
}
$conn->close();
?>Output:
Users over 25 in New York:
John Doe - Age: 30 - City: New York
Alice Smith - Age: 28 - City: New York
🔹 OR Operator
Retrieve records that match any of the specified conditions. Use OR when you want results that satisfy at least one condition.
<?php
$conn = new mysqli("localhost", "root", "", "myDB");
// Multiple conditions with OR
$sql = "SELECT name, city FROM users WHERE city = 'New York' OR city = 'Los Angeles'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<h3>Users in NY or LA:</h3>";
while($row = $result->fetch_assoc()) {
echo $row["name"] . " - " . $row["city"] . "<br>";
}
} else {
echo "No users found";
}
$conn->close();
?>Output:
Users in NY or LA:
John Doe - New York
Jane Smith - Los Angeles
Bob Johnson - New York
🔹 LIKE Operator
Search for patterns in text fields using wildcards. The percent sign (%) matches any sequence of characters, while underscore (_) matches a single character.
<?php
$conn = new mysqli("localhost", "root", "", "myDB");
// Pattern matching with LIKE
// % = any characters, _ = single character
$sql = "SELECT name, email FROM users WHERE email LIKE '%@gmail.com'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<h3>Gmail users:</h3>";
while($row = $result->fetch_assoc()) {
echo $row["name"] . " - " . $row["email"] . "<br>";
}
} else {
echo "No Gmail users found";
}
$conn->close();
?>Output:
🔹 BETWEEN Operator
Select values within a specific range. BETWEEN is inclusive, meaning it includes both the start and end values in the results.
<?php
$conn = new mysqli("localhost", "root", "", "myDB");
// Range selection with BETWEEN
$sql = "SELECT name, age FROM users WHERE age BETWEEN 25 AND 35";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<h3>Users aged 25-35:</h3>";
while($row = $result->fetch_assoc()) {
echo $row["name"] . " - Age: " . $row["age"] . "<br>";
}
} else {
echo "No users in this age range";
}
$conn->close();
?>Output:
Users aged 25-35:
John Doe - Age: 30
Jane Smith - Age: 25
Bob Johnson - Age: 28
🔹 IN Operator
Check if a value matches any value in a list. IN is a shorthand for multiple OR conditions and makes queries more readable.
<?php
$conn = new mysqli("localhost", "root", "", "myDB");
// Check multiple values with IN
$sql = "SELECT name, city FROM users WHERE city IN ('New York', 'Chicago', 'Boston')";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<h3>Users in major cities:</h3>";
while($row = $result->fetch_assoc()) {
echo $row["name"] . " - " . $row["city"] . "<br>";
}
} else {
echo "No users in these cities";
}
$conn->close();
?>Output:
Users in major cities:
John Doe - New York
Sarah Wilson - Chicago
Mike Brown - Boston
💡 Best Practices
- Always use prepared statements with user input
- Use appropriate operators for better performance
- Combine conditions logically with AND/OR
- Use LIKE sparingly as it can be slow on large tables
- Index columns used frequently in WHERE clauses