PHP MySQL WHERE: How to Filter Data in MySQL

Filtering data is a big part of getting the right information to show up. That is where the WHERE clause in MySQL shines. It helps you narrow down your results to exactly what you need. If you are using PHP to interact with a MySQL database, knowing how to work with WHERE will make your applications more dynamic.

In this article, you will learn everything about the WHERE clause, step-by-step, in easy.

What is the WHERE Clause in MySQL?

The WHERE clause acts as a filter to narrow down your database queries. Instead of pulling all the data from a table, it allows you to set conditions and fetch only the rows that match those conditions.

For example, if you want to get all users aged over 12 from a users table, the WHERE clause makes it possible.

Here is its syntax:

SELECT column1, column2 FROM table_name WHERE condition;

So, in this line, we have 3 parts which are building the query from database using SQL:

• SELECT: Choose which columns you want to retrieve.
• FROM: This specifies from which the table fetches the data.
• WHERE: Adds the condition to filter results.

Here is a quick example:

SELECT * FROM users WHERE age > 12;

This query fetches all rows where the age column has a value greater than 12.

That was for SQL, let’s see how we can do that using PDO and mysqli in PHP.

Using WHERE with mysqli in PHP

mysqli is a common way to interact with MySQL databases. Let us look at two methods to use the WHERE clause with mysqli: procedural and object-oriented.

Procedural Method for Queries with WHERE

$sqlconn = mysqli_connect("localhost", "username", "password", "database");

if (!$sqlconn ) {
    die("Error in SQL Connection: " . mysqli_connect_error());
}

$sql_qr = "SELECT * FROM users WHERE age > 33";
$result = mysqli_query($sqlconn , $sql_qr);

if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "Full Name:" . $row["name"] . "<br>";
    }
} else {
    echo "No results found.";
}

mysqli_close($sqlconn );

Object-Oriented Method for Queries with WHERE

$connection = new mysqli("localhost", "user", "pass", "db");

if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}

$queryString = "SELECT * FROM users WHERE age > 33";
$queryResult = $connection->query($queryString);

if ($queryResult->num_rows > 0) {
    while ($data = $queryResult->fetch_assoc()) {
        echo "Name: " . $data["name"] . "
";
    }
} else {
    echo "No data found.";
}

$connection->close();

Let’s see another example using PDO in PHP.

Using PDO with WHERE Clause

PDO (PHP Data Objects) provides an alternative for interacting with MySQL. It is highly secure and supports prepared statements by default.

Here is an example:

try {
    $sqlconn = new PDO("mysql:host=localhost;dbname=name_of_database", "db_username", "db_password");
    $sqlconn ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $sqlconn ->prepare("SELECT * FROM users WHERE age > ?");
    $stmt->execute([12]);

    $sqlresults = $stmt->fetchAll();
    foreach ( $sqlresults as $row) {
        echo "Full Name: " . $row["name"] . "<br>";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$sqlconn = null;

Prepared statements are hands down the most reliable way to keep your queries secure. They prevent SQL injection and handle data more safely. Here is an example with PDO:

$stmt = $sqlconn ->prepare("SELECT * FROM users WHERE name = ?");
$stmt->execute(['John']);

So, there are many ways to allow you to filter records, let’s see each one in detail in the following section.

Using Operators in Filters

Fetching results with WHERE is all about using the right condition. For example:

• Use = for exact matches.
• Use > or < for ranges.
• Combine multiple conditions with AND or OR.

Comparison operators help you build powerful queries. Here are a few examples:

• Equal to (=): WHERE age = 25
• Not equal to (!=): WHERE status != 'inactive'
• Greater than (>): WHERE age > 30
• Less than (<): WHERE age < 12

Logical operators let you combine multiple conditions:

• AND: All conditions must be true.
• OR: At least one condition must be true.
• NOT: Excludes a condition.

The LIKE operator is great for searching patterns. Use % as a wildcard:

• WHERE name LIKE '%son' (Names ending with “son”).
• WHERE name LIKE 'J%' (Names starting with “J”).

To find rows with missing data, use:

• IS NOT NULL: Finds rows where a column has a value.
• IS NULL: Finds rows where a column is NULL.

Here is an example for each one:

// => Find rows with missing data
SELECT * FROM users WHERE last_login IS NULL;

// => Find by query from SQL using LIKE operator (Searching Pattern)
SELECT * FROM users WHERE name LIKE '%son';

// => Logical operator (AND)
SELECT * FROM users WHERE age > 12 AND status = 'active';

// => Filter by not equal operator
SELECT * FROM users WHERE status != 'inactive';

Let’s summarize it.

Wrapping Up

The WHERE clause is a handy way to filter and manage your database queries when using MySQL with PHP. It helps you filter data, create dynamic queries, and ensure your applications fetch exactly what they need.

Similar Reads