How to create a search filter for mysqli

Create the query dynamically
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!

Latest comments:

21.12.24 23:34
Paul Harding for (The only proper) PDO tutorial:
Fantastic articles, thank you very much for putting all this effort into them.
read more
17.12.24 07:19
Charles Roth for UPDATE query using PDO:
As described in one of your other excellent :-) phpdelusions pages, fetch() can do a (say)...
read more
16.12.24 20:08
Myles for (The only proper) PDO tutorial:
You only have silver on SQL-INJECTION. No one has gold.
read more
06.12.24 22:23
Charles Roth for (The only proper) PDO tutorial:
In your wonderful phpdelusions.net/pdo, you state that the types of results will always be...
read more
03.12.24 14:37
Adrian for Articles:
Have you written any books on PHP, or recommend any books? Thank you very much, and keep up the...
read more

Comments:

Ian McKinnon, 10.06.23 17:18

Please tell me how I can convert this into a prepared statement. 'A' indicates that the retrieved data is active. $a that is matches the query.

$conn = mysqli_connect($dbhost,$dbuser,$dbpass,$dbname); if (mysqli_connect_errno()) { echo "Failed to Connect to MySQL :" .mysqli_connect_error(); }
$sql = "SELECT site_name, site_link from site_list
where active=Y' and site_code='$a'";

Reply:
Hello Ian!

What is your particular problem? Here is a complete mysqli guide, https://phpdelusions.net/mysqli#prepared_select with a query almost identical to yours. Did you try it?
Elli, 19.10.21 01:17

Hi there, Id love you to create the following tutorial, as I want to make a website to help people, and this filter system would be very helpful. One of the things I am working on is making a main database for all lost pets in UK. Getting all places around UK to send me their data and making one site for all that is free for all as many charge.. Anyway I digress. What I really want in a filtering system is say you have a standard page with table, displaying database with cat; fur colour, fur length, eye colour, gender, type, where found, date found, .... Ive done this in PHP I want a way that one ticks the various variables, and only when one presses enter does the page search the database many thanks Elli
xyz, 29.09.21 14:19

hjgj
Neil, 30.06.21 04:20

Hello. It appears my lack of experience caused me to use the wrong terminology in my previous question. Basically, I have a SINGLE condition but there are multiple variations (up to 10) that could be used in that same condition. This is similar to WHERE membership_number IN (1,4,5,6,9). Can this be done using your prepared statement methodology? I am looking to pull multiple rows from a table using a single column variable that could have multiple variations for each select statement. Thank you.

Reply:
Regarding your other question, it looks like JSON, hence you can use json_decode(). But it's a very bad idea to have such kind of data in the database. Please read here for the explanation, https://stackoverflow.com/q/3653462/285587
Jawad, 10.03.21 09:02

I have a select box with multiple select and an input field. I want to search if the selected options are in column 1 and input value equal to column 2. How would you do this in pdo?
paolo, 02.07.20 15:19

Hi please can u help me to understand, i have this error:

Warning: Wrong parameter count for mysqli_stmt::bind_param() in line 449

the line is: $stmt->bind_param(str_repeat("s", count($parameters)), ...$parameters))

I need to send some kind of POST S and I
Reply:
Hello Paolo!

I am terribly sorry, it's my bad. Indeed this code doesn't work when no filters were selected. Please see a quick workaround below.

However, in reality you would use a LIMIT clause anyway, and I will fix the code in the article to add this clause, so the query would always has parameters to bind.
```
if ($conditions)
{
    $sql .= " WHERE ".implode(" AND ", $conditions);
    $stmt = $conn->prepare($sql);
    $stmt->bind_param(str_repeat("s", count($parameters)), ...$parameters);
    $stmt ->execute();
    $result = $stmt ->get_result();
} else {
    $result = $conn->query($sql);
}
```
Ahmed Rashid, 11.05.20 04:34

What is ...$parameters meaning?

How can i passing it in $stmt->bind_param? I added like you " ...$parameters" but didn't work

Reply:
Hello Ahmed!

...$parameters is called argument unpacking operator and it was introduces in PHP 5.6, something like 4 years ago. If your PHP version is that old, you should really really get yourself a more recent version.

21.12.24 23:34
Paul Harding for (The only proper) PDO tutorial:
Fantastic articles, thank you very much for putting all this effort into them.
read more

17.12.24 07:19
Charles Roth for UPDATE query using PDO:
As described in one of your other excellent :-) phpdelusions pages, fetch() can do a (say)...
read more

16.12.24 20:08
Myles for (The only proper) PDO tutorial:
You only have silver on SQL-INJECTION. No one has gold.
read more

06.12.24 22:23
Charles Roth for (The only proper) PDO tutorial:
In your wonderful phpdelusions.net/pdo, you state that the types of results will always be...
read more

03.12.24 14:37
Adrian for Articles:
Have you written any books on PHP, or recommend any books? Thank you very much, and keep up the...
read more