Filter Data in MySQL Using PHP

In this tutorial, we will learn how to filter data in MySQL using PHP. Filtering data means retrieving only specific records from a database table based on certain conditions. For example:

  • Show only students with marks greater than 80.
  • Show products under ₹1000.
  • Search users by name.
  • Display records between two dates.

In MySQL, filtering is done using the WHERE clause. So, let us understand first what WHERE clause is?

What is WHERE Clause in MySQL?

The WHERE clause in MySQL filters records from a database table based on specific conditions. It extracts only those records that fulfill a specified condition. This clause is used with SELECT, UPDATE, DELETE, and sometimes INSERT.

Basic Syntax to Filter Data in MySQL

The basic syntax to filter data using SQL WHERE clause from a table is:

SELECT column1, column2, column 3
FROM table_name
WHERE condition;

In the above syntax:

  • SELECT → Specifies which columns you want to retrieve from the database table.
  • FROM → Specifies the table from which you want to fetch data.
  • WHERE → Filters records from the table based on a specified condition.
  • condition → A logical expression that checks each row of the table and returns only those rows that satisfy the specified condition. The specified condition may consist of comparison or logical operators.
Important Note:

The WHERE clause is optional. If you don’t use it, MySQL returns all rows from the database table.

[blocksy-content-block id=”12371″]

Requirements Before Filtering Records from Table

Before filtering records from a MySQL table using PHP, make sure you meet the following requirements:

  • You must create a MySQL database.
  • You must establish a successful connection between PHP and the MySQL database.
  • The table must exist in the selected database.
  • The table should contain records that you want to retrieve.
  • The database user must have permission to access and query the table.

We hope you have met all these basic requirements before attempting to filter data from a MySQL table using PHP.

Ways to Filter Data in MySQL with PHP

PHP provides three main ways to filter data from a MySQL database table:

  • Procedural MySQLi
  • Object-Oriented MySQLi
  • PDO (PHP Data Objects)

Let us understand each method one by one.

Filter Data in MySQLi Using PHP

Inside the htdocs folder, create a new file with name filter_data.php and paste this code into it.

Example 1: Procedural Method

<?php
// Include the database connection file
require_once "connect.php";

// Write the SQL query to select name and email
// from students whose age is 24 or above
$sql = "SELECT name, email FROM students WHERE age >= 24";

// Execute the SQL query
$result = mysqli_query($conn, $sql);

// Check if the query returned any rows
if (mysqli_num_rows($result) > 0) {
   // Loop through each row of the result set
      while($row = mysqli_fetch_assoc($result)) {
      // Display the student's name and email.
         echo "Name: " . $row["name"] . "<br>";
         echo "Email: " . $row["email"] . "<br><br>";
      }
} else {
  // Display message if no records are found
     echo "No records found.";
}
// Close the database connection
mysqli_close($conn);
?>

Now save this file using Ctrl + S.
[blocksy-content-block id=”12121″]
Now open the browser and go to http://localhost/filter_data.php. If the record exists into the table, you will see the following output in your browser.

Output:

Name: Tripti
Email: tripti@gmail.com

Name: Deepak
Email: deepak@gmail.com

Explanation of PHP Code

  • The line require_once “connect.php”; includes the database connection file. This file contains the code that connects PHP to the MySQL database.
  • Without this connection, PHP cannot communicate with the database.
  • Then, we created a SQL statement and stored it inside the variable named $sql.
  • The SQL SELECT statement retrieves only name and email columns from the students table.
  • The WHERE clause filters records where age is 24 or greater.
  • The mysqli_query() function executes the SQL query. This function takes two parameters:
    • $conn → database connection
    • $sql → SQL query
  • The result set is stored in a variable named $result.
  • The mysqli_num_rows() function defined inside if statement returns the number of rows in the result set.
  • If rows exist (i.e. greater than 0), the condition becomes true. Then, the program processes the data.
  • If no records exist inside the table, it goes to the else block to display “No records found”.
  • We have used while loop to iterate over multiple records inside the table.
  • The mysqli_fetch_assoc() function fetches one record at a time and returns the record as an associative array. The loop runs until all records are fetched.
  • The line mysqli_close($conn); closes the database connection. This frees up server resources.

[blocksy-content-block id=”12153″]

Example 2: Object-Oriented Method

<?php
// Include the database connection file
require_once "connect.php";

// Write the SQL query to select name and email
// from students whose age is 24 or above.
$sql = "SELECT name, email FROM students WHERE age >= 24";

// Execute the SQL query using the query() method.
$result = $conn->query($sql);

// Check if the query returned any rows.
if ($result->num_rows > 0) {
  // Loop through each row of the result set.
     while ($row = $result->fetch_assoc()) {
     // Display the student's name and email.
        echo "Name: " . $row["name"] . "<br>";
        echo "Email: " . $row["email"] . "<br><br>";
     }
} else {
  // Display message if no matching records are found.
     echo "No records found.";
}
// Close the database connection
$conn->close();
?>

Filter Data Using PDO Method

Inside the htdocs folder, create a new file with name filter_data_pdo.php and paste this code into it.

Example 3: Filter Data with PDO Method

<?php
// Include database connection.
require_once "config.php";

try {
// Write the SQL query to select name and email
// from students whose age is 24 or above
   $sql = "SELECT name, email FROM students WHERE age >= 24";

// Prepare and execute the SQL query.
   $stmt = $conn->prepare($sql);
   $stmt->execute();

// Fetch all matching records as associative array.
   $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Check if records exist
   if (count($result) > 0) {
   // Loop through each row
      foreach ($result as $row) {
     // Display the student's name and email.
        echo "Name: " . $row["name"] . "<br>";
        echo "Email: " . $row["email"] . "<br><br>";
      }
    } else {
     // Display message if no records are found.
        echo "No records found.";
     }
} catch (PDOException $e) {
  // Display error message if connection fails
     echo "Connection failed: " . $e->getMessage();
}

// Close the connection
$conn = null;
?>

Conclusion

PHP provides three ways to filter data from a MySQL database table, which we have discussed in this tutorial. We hope you learned how to filter data from a MySQL database using PHP and practiced all examples.

DEEPAK GUPTA

DEEPAK GUPTA

Deepak Gupta is the Founder of Scientech Easy, a Full Stack Developer, and a passionate coding educator with 8+ years of professional experience in Java, Python, web development, and core computer science subjects. With strong expertise in full-stack development, he provides hands-on training in programming languages and in-demand technologies at the Scientech Easy Institute, Dhanbad.

He regularly publishes in-depth tutorials, practical coding examples, and high-quality learning resources for both beginners and working professionals. Every article is carefully researched, technically reviewed, and regularly updated to ensure accuracy, clarity, and real-world relevance, helping learners build job-ready skills with confidence.

Related Posts