Filtering MySQL Results by Applying PHP Conditions

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 student admission program to shortlist students who had applied before six months at least.

Following steps are used to implement this example and thereby understanding conditional filtering.

  • Step 1: we should create a 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 the 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 the 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 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.

$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 thereby, we can process with each row inside while loop.

Conditional Filtering

After retrieving an array of students record, 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 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 date before 6 months from a 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 student’s 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 PHP code tutorial was published on May 22, 2013.

↑ Back to Top