How to create a search filter for mysqli
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
// here we are using LIKE with wildcard search
// use it ONLY if really need it
$conditions = 'name LIKE ?';
$parameters = '%'.$_GET['name']."%";
// here we are using equality
$conditions = 'sex = ?';
$parameters = $_GET['sex'];
// here we are using not equality
$conditions = 'car != ?';
$parameters = $_GET['car'];
if (!empty($_GET['date_start']) && $_GET['date_end'])
$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
$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);
$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!
- Mysqli SELECT query with prepared statements
- How to run an INSERT query using Mysqli
- How to run a SELECT query using Mysqli
- How to run an UPDATE query using Mysqli
- Using mysqli prepared statements with LIKE operator in SQL
- Mysqli prepared statement with multiple values for IN clause
- Mysqli examples
- How to call stored procedures with mysqli
- How to run 1000s insert queries with mysqli?
O for An SQL injection against which prepared statements won't help:
Please, exemple with select and backtrik
Ron for The Hitchhiker's Guide to SQL Injection prevention:
Terrible website. I suggest you contact the person who had designed this & get your money back....
Robery for Mysqli helper function:
I want to thank you for your sane content. Iím not a developer or trained on PHP. My introduction...
muhammad Sanusi for Mysqli SELECT query with prepared statements:
there is this query in procedural mysql, where u can compare two columns of the same rows. a am...
Globaliser for Simple yet efficient PDO wrapper:
This is really nice. I also extended PDO and PDOStatement classes, because default naming is very...