Filtering MySQL Results by Applying PHP Conditions
by Vincy. Last modified on July 2nd, 2022.
In this article, we are going to discuss how to filter selected database rows based on the conditions applied using PHP. Let us have an example to deal with this conditional filtering process.
For that, We can take a student admission program to shortlist students who had applied for six months at least.
The following steps are used to implement this example and thereby understand conditional filtering.
- Step 1: we should create a database and add the students tables.
- Step 2: Insert students table entries.
- Step 3: Connect database from PHP using database configuration details.
- Step 4: Select students rows from the table.
- Step 5: Apply PHP conditions to filter selected rows.
Database Requirements
First, two steps are used to make prepare database requirements that are used while accessing from the front end. For that, a student table containing three columns as student_name, applied_date, and student_email is added to the payload database.
And then, the immediate two steps following step 2 are used to set up database configuration in PHP and for selecting records from the database. The way how can access MySQL from PHP, that we have discussed already.
Before applying conditions, we should have a list of students’ information. To retrieve students’ records from the database, the following code will be useful.
<?php
$conn = mysqli_connect("localhost", "root", "test", "blog_samples") or die("Connection Error: " . mysqli_error($conn));
$result = mysqli_query($conn, "SELECT * FROM students");
while ($row = mysqli_fetch_array($result)) {
// Do code here
}
?>
mysqli_fetch_array() returns array of information about individual student record. And thereby, we can process with each row inside while loop.
Conditional Filtering
After retrieving an array of students’ records, we can filter out limited rows which satisfy the condition specified for finding students who had applied at least six months before.
For that, using PHP explode() function, the current date is split into an array of day, month and year and the month value is reduced by integer 6. And then, the timestamp will be created by passing this manipulated month value.
Using this timestamp we can create a date before 6 months from a current date. We can see this manipulation in the following code.
<?php
$currentDateArray = explode("-", date('d-m-y'));
$newMonthValue = $currentDateArray[1] - 6;
$timestamp = mktime(0, 0, 0, $newMonthValue, $currentDateArray[0], $currentDateArray[2]);
$dateBefore6Month = date('Y-m-d', $timestamp);
?>
By iterating through the $row array, for each student, the applied_date field should be compared with the newly computed date. If a match is found, then the record will be shortlisted. And, the code for applying conditions is as follows.
<?php
while ($row = mysqli_fetch_array($result)) {
if ($row["applied_date"] <= $dateBefore6Month) {
$shortlistedStudents[] = $row;
}
}
?>
We can check if the student’s list is shortlisted by the applied PHP condition as expected by using print statement inside pre tags as follows.
<?php
print "<pre>";
print_r($shortlistedStudents);
print "</pre>";
?>
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.
Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I'm currently available for freelance work.