Building the Where Clause for MySQL Dynamically

There are a number of ways to dynamically build the WHERE clause for a MySQL query. You could, for example, run several if() statements in the middle of a query. Or one could tap into the power of the implode() function.

In a previous post (Using PHP's implode() Function to Display an Array as a Value-Separated String), we discussed using the implode() function to generate a comma-separated list with an array. Well, the same idea can be utilized to generate the WHERE clause. Let's say we have the following database table:

Visitors are able to search by title, first name, or last name. All three fields can also be left blank to see everything. To perform the search, the query could be written as follows:

<?php
//START THE QUERY
$sql = "SELECT id, title, firstName, lastName FROM booksDBS ";
 
//IF TITLE, FIRST NAME, OR LAST NAME HAVE A VALUE, START THE WHERE CLAUSE
if($title || $firstName || $lastName) {
     $sql .= "WHERE ";
}
 
//ADD THE QUERY INFORMATION TO THE WHERE CLAUSE
$combine = '';
if($title)     { $sql.="{$combine}title LIKE '%$title%' "; $combine='AND '; }
if($firstName) { $sql.="{$combine}firstName LIKE '%$firstName%' "; $combine='AND '; }
if($lastName)  { $sql.="{$combine}lastName LIKE '%$lastName%' "; $combine='AND '; }
?>

The code could be simplified with the implode() function:

<?php
//PREPARE THE QUERY INFORMATION FOR THE WHERE CLAUSE
$whereParts = array();
if($title)     { $whereParts[] = "title LIKE '%$title%' "; }
if($firstName) { $whereParts[] = "firstName LIKE '%$firstName%' "; }
if($lastName)  { $whereParts[] = "lastName LIKE '%$lastName%' "; }
 
//BUILD THE QUERY
$sql = "SELECT id, title, firstName, lastName FROM booksDBS ";
if(count($whereParts)) {
     $sql .= "WHERE " . implode('AND ', $whereParts);
}
?>

Conclusion

The updated code may not look much different. However, there may be some extra streamlining opportunities depending on what's done with the title, first name, and last name variables. For example, for users who search by title, you'll likely need to prepare the variable for the query. Well, generating the $whereParts array could be incorporated into that block of code.

<?php
if($title) {
     $title        = trim($title);
     $title        = strip_tags($title);
     $whereParts[] = "title LIKE '%$title%' ";
}
?>