How to create a search filter for mysqli

  1. Create the query dynamically
  2. Comments (7)

This is quite a common task when we need to create a search query based on the arbitrary number of parameters.

The main problem here is that we cannot create a query beforehand, and therefore we don't know which parameters to bind. Hence we got to add conditions one by one, and collect the respective data variables into array.

Create the query dynamically

Mysqli doesn't have a functionality for this out of the box, but it's not much a problem. We can just create conditions dynamically based on the user input and collect all the data for parameters accordingly:

// just stub values for pagination
// calculate your own values
$offset 0;
$limit 10

// always initialize a variable before use!
$conditions = [];
$parameters = [];

// conditional statements
if (!empty($_GET['name']))
{
    
// here we are using LIKE with wildcard search
    // use it ONLY if really need it
    
$conditions[] = 'name LIKE ?';
    
$parameters[] = '%'.$_GET['name']."%";
}

if (!empty(
$_GET['sex']))
{
    
// here we are using equality
    
$conditions[] = 'sex = ?';
    
$parameters[] = $_GET['sex'];
}

if (!empty(
$_GET['car']))
{

    
// here we are using not equality
    
$conditions[] = 'car != ?';
    
$parameters[] = $_GET['car'];
}

if (!empty(
$_GET['date_start']) && $_GET['date_end'])
{

    
// BETWEEN
    
$conditions[] = 'date BETWEEN ? AND ?';
    
$parameters[] = $_GET['date_start'];
    
$parameters[] = $_GET['date_end'];
}

// the main query
$sql "SELECT * FROM users";

// a smart code to add all conditions, if any
if ($conditions)
{
    
$sql .= " WHERE ".implode(" AND "$conditions);
}

// a search query always needs at least a `LIMIT` clause, 
// especially if no filters were used. so we have to add it to our query:
$sql .= " LIMIT ?,?";
$parameters[] = $offset;
$parameters[] = $limit;

// the usual prepare/bind/execute/fetch routine
$stmt $mysqli->prepare($sql);
$stmt->bind_param(str_repeat("s"count($parameters)), ...$parameters);
$stmt->execute();
$data $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

Now you have a code which is is flexible, simple, elegant and 100% safe from SQL injections!


Related articles: