Dynamic Database Search with Optional Filters

Dynamic search means adding only the filters a visitor actually supplies. The safe pattern is to build a list of approved SQL conditions while keeping every visitor value in a prepared-statement parameter.

Build conditions instead of trimming AND/OR text

The older version built one long SQL string and then removed trailing AND/OR text. An array of conditions is easier to validate and avoids broken SQL when no filter is selected.

<?php
$conditions = [];
$params = [];

if ($class !== '') {
    $conditions[] = 'class = :class';
    $params[':class'] = $class;
}

if ($greater !== '' && is_numeric($greater)) {
    $conditions[] = 'mark > :greater';
    $params[':greater'] = (float)$greater;
}

$sql = 'SELECT id, name, class, mark, sex FROM student';
if ($conditions) {
    $sql .= ' WHERE ' . implode(' AND ', $conditions);
}
$sql .= ' ORDER BY id';

$stmt = $dbo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

Multiple words in a name search

For an “any word” search, create one placeholder per word and group the LIKE expressions with parentheses.

SELECT id, name, class, mark, sex
FROM student
WHERE class = :class
  AND (name LIKE :name_0 OR name LIKE :name_1)
ORDER BY id;

For a larger free-text search requirement, see MATCH() ... AGAINST(). For a simpler name-only search, see keyword search.

Download the original database-search package Student table SQL dump

Related tutorials

WHERE · LIKE · AND/OR · PDO · Database autocomplete

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: