PHP MySQL WHERE Clause
The WHERE clause is used to extract only those records that fulfill a specified condition. This is essential when you don't want to pull every single row from a table, but rather search for specific data.
1. The WHERE Syntax
SELECT column_name(s) FROM table_name WHERE column_name operator value;
2. Filter Data using MySQLi (Object-Oriented)
In this example, we select all records from the "MyGuests" table where the lastname is 'Doe'.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, firstname, lastname FROM MyGuests WHERE lastname='Doe'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
3. Comparison Operators
The WHERE clause can be used with various operators:
| Operator |
Description |
| = |
Equal |
| <> or != |
Not equal |
| > |
Greater than |
| < |
Less than |
| LIKE |
Search for a pattern (e.g., WHERE name LIKE 'J%') |
| IN |
To specify multiple possible values for a column |
4. Filtering with Multiple Conditions (AND / OR)
You can combine conditions using AND and OR operators to create more complex filters.
$sql = "SELECT * FROM MyGuests WHERE lastname='Doe' AND firstname='John'";
// Returns only rows where both conditions are true
Security Warning: When using variables inside a WHERE clause (like searching for a name entered by a user), always use Prepared Statements. Putting variables directly into the SQL string makes your site vulnerable to hackers via SQL Injection.