• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Where

← Previous Next →

The WHERE clause is one of the most fundamental tools in SQL. It allows you to filter data based on specific conditions. In PHP applications, using WHERE ensures that only the relevant records are retrieved, updated, or deleted.

This tutorial explains how to use the WHERE clause in MySQL queries, how to implement it in PHP MySQLi and PDO, and provides examples and best practices for dynamic filtering.

What is WHERE Clause?

The WHERE clause restricts the rows affected by a SQL statement. You can use it with SELECT, UPDATE, DELETE, and even INSERT ... ON DUPLICATE KEY UPDATE.

Basic Syntax

SELECT column1, column2 FROM table_name WHERE condition;

Example Table: students

id name age email
1 Neha 16 neha@example.com
2 Anika 17 anika@example.com
3 Riya 16 riya@example.com 
SELECT * FROM students WHERE age = 16;

This query selects only the students who are 16 years old.

Comparison Operators

  • = : Equals

  • != or <> : Not equal

  • > : Greater than

  • < : Less than

  • >= : Greater than or equal

  • <= : Less than or equal

SELECT * FROM students WHERE age > 16;
SELECT * FROM students WHERE name != 'Riya';

Logical Operators

Combine conditions with:

  • AND : All conditions must be true

  • OR : At least one condition must be true

  • NOT : Negates a condition

SELECT * FROM students WHERE age = 16 AND name = 'Neha';
SELECT * FROM students WHERE age = 16 OR age = 17;
SELECT * FROM students WHERE NOT age = 16;

Pattern Matching with LIKE

LIKE allows searching for patterns in string data using % (any number of characters) or _ (single character).

SELECT * FROM students WHERE name LIKE 'R%';
SELECT * FROM students WHERE email LIKE '%@example.com';

  • 'R%' matches any name starting with 'R'.

  • '%@example.com' matches any email ending with '@example.com'.

IN, BETWEEN, and IS NULL

  • IN : Matches any value in a list

  • BETWEEN : Matches a range of values

  • IS NULL / IS NOT NULL : Checks for null values

SELECT * FROM students WHERE age IN (16, 17);
SELECT * FROM students WHERE age BETWEEN 16 AND 17;
SELECT * FROM students WHERE email IS NOT NULL;

Using WHERE in PHP MySQLi

Procedural Style

<?php
$conn = mysqli_connect("localhost", "root", "", "school_db");
if (!$conn) die("Connection failed: " . mysqli_connect_error());

$sql = "SELECT id, name, email FROM students WHERE age = 16 AND name LIKE 'N%'";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "ID: {$row['id']} - Name: {$row['name']} - Email: {$row['email']}<br>";
    }
} else {
    echo "No records found.";
}

mysqli_close($conn);
?>

Object-Oriented Style

<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);

$sql = "SELECT id, name, age, email FROM students WHERE age BETWEEN 16 AND 17 ORDER BY name ASC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: {$row['id']}, Name: {$row['name']}, Age: {$row['age']}, Email: {$row['email']}<br>";
    }
} else {
    echo "No records found.";
}

$conn->close();
?>

Using WHERE with PDO

PDO provides a secure way to include dynamic input using prepared statements.

<?php
try {
    $conn = new PDO("mysql:host=localhost;dbname=school_db", "root", "");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $age = 16;
    $stmt = $conn->prepare("SELECT id, name, email FROM students WHERE age = :age");
    $stmt->bindParam(':age', $age, PDO::PARAM_INT);
    $stmt->execute();

    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if ($students) {
        foreach ($students as $student) {
            echo "ID: {$student['id']}, Name: {$student['name']}, Email: {$student['email']}<br>";
        }
    } else {
        echo "No records found.";
    }
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

$conn = null;
?>

Explanation:

  • :age is a named placeholder.

  • bindParam() ensures dynamic input is safely passed.

  • Using prepared statements prevents SQL injection when using WHERE with user input.

Best Practices

  1. Always use prepared statements for dynamic values in WHERE clauses.

  2. Use specific conditions to avoid fetching unnecessary data.

  3. Combine AND, OR, and NOT logically to match your filtering requirements.

  4. Use LIKE, IN, and BETWEEN for advanced filtering.

  5. Handle NULL values correctly with IS NULL or IS NOT NULL.

Common Errors

Error Cause Solution
No results Conditions do not match Verify the data and WHERE conditions
SQL syntax error Missing operators or quotes Check syntax carefully
Injection risk Concatenating user input Always use prepared statements
Null issues Using = NULL instead of IS NULL Use IS NULL or IS NOT NULL

Real-World Example

In a school management system, you may want to list students aged 16 whose names start with 'N':

SELECT * FROM students WHERE age = 16 AND name LIKE 'N%';

This ensures only the relevant subset of students is retrieved, reducing unnecessary data handling and improving performance.

Summary of the Tutorial

  • The WHERE clause filters records for SELECT, UPDATE, DELETE, and conditional inserts.

  • Logical, comparison, and pattern operators help define precise conditions.

  • Always use prepared statements when dealing with dynamic input to prevent SQL injection.

  • Combining WHERE with ORDER BY, LIMIT, and functions allows powerful and efficient queries.

  • Proper usage is essential for creating dynamic dashboards, reports, and user-specific data views.

Filtering data correctly is crucial in PHP applications such as reporting systems, dashboards, and user management to improve performance, security, and usability.

Practice Questions

  1. Write a SQL query to select all students aged 17 from the students table.

  2. Write a PHP MySQLi procedural script to fetch students whose name starts with 'R'.

  3. Write a PHP MySQLi object-oriented script to select students aged between 16 and 18.

  4. Write a PDO PHP script to retrieve students whose email ends with '@example.com'.

  5. Write a SQL query using IN to select students aged 16 or 17.

  6. Write a SQL query using NOT to select students whose name is not 'Neha'.

  7. Write a PHP script that retrieves all students with NULL email addresses using IS NULL.

  8. Write a SQL query using multiple conditions combined with AND and OR to fetch students aged 16 whose name starts with 'A'.

  9. Explain why prepared statements are recommended when using WHERE with user input.

  10. Write a real-world scenario where filtering data with WHERE is essential, such as selecting orders placed within a specific date range.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

PHP

online coding class codepractice

PHP Basics

PHP Data

Control Flow

PHP Functions

PHP Arrays

PHP Forms

PHP Advanced

Cookies & Sessions

Filters & JSON

PHP Exceptions

PHP OOP

MySQL with PHP

PHP and XML

AJAX with PHP

codepractice logo
Login
Go Back Top