Filtering MySQL Results by Applying PHP Conditions

In this article we are going to discuss about 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 student admission program to shortlist students who had applied before six months at least.

Following steps are used to implement this example and there by understand conditional filtering.

  • Step 1: we should create database and to add students table.
  • Step 2: Insert students table entries.
  • Step 3: Connect database from PHP using database configuration details.
  • Step 4: Select students rows from table.
  • Step 5: Apply PHP conditions to filter selected rows.

php_conditional_filtering

Database Requirements

First two steps are used to make prepare database requirements that are used while accessing from front end. For that, student table containing three columns as student_name, applied_date and student_email is added into payload database.

And then, the immediate two steps following step 2 are used to setup database configuration in PHP and for selecting records from database. The way how can access MySQL from PHP, that we have discussed already.

Before applying conditions, we should have list of students information. To retrieve students records from database, the following code will be useful.

$conn = mysql_connect("localhost","root","");
mysql_select_db("payload",$conn);
$result = mysql_query("SELECT * FROM students");
while($row=mysql_fetch_array($result)) {
...
}

mysql_fetch_array() returns array of information about individual student record. And there by, we can process with each row inside while loop.

Conditional Filtering

After retrieving array of students record, we can filter out limited rows which satisfies the condition specified for finding students who had applied at least six months before.

For that, using PHP explode() function, current date is split into an array of day, month and year and month value is reduced by integer 6. And then, time stamp will be created by passing this manipulated month value. Using this time stamp we can create date before 6 month from current date. We can see this manipulation in the following code.

$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 match found, then the record will be shortlisted. And, the code for applying condition is as follows.

while($row=mysql_fetch_array($result)) {
if($row["applied_date"] <= $dateBefore6Month) {
$shortlistedStudents[] = $row;
}
}

We can check if the students list is shortlisted by the applied PHP condition as expected by using print statement inside pre tags as follows.

print "<pre>";
print_r($shortlistedStudents);
print "</pre>";

This MySQL code tutorial was published on May 22, 2013.

↑ Back to Top