How to create a search filter for mysqli

  1. Create the query dynamically
  2. Comments

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 (well, actually we can but thet's another story) therefore we don't know which parameters to bind.

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:

// 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);
}

// 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: