CODEWITHSUNDEEP
phpmysql
O'Niel
O'Niel

Reputation: 1773

Multiple filters who are optional on MySQL table

So I have a database table with the following columns:

ID
Foo
Bar
Gux

Foo can hold the values [a, b, c], Bar [1, 2, 3], and Gux [x, y, z].

In my interface I have a HTML select box to filter on Foo, Bar, and Gux. The problem is that the user can filter on one, two, or three parameters in random order.

This gives me a problem to make an efficient SQL-query and bind_param.

I could do something like this:

//Pseudo code

$sql = "";

if (isset($_GET['foo'])) {
    $sql = "SELECT * FROM ... WHERE foo=?";
}
if (isset($_GET['bar'])) {
    $sql = "SELECT * FROM ... WHERE bar=?";
}
if (isset($_GET['gux'])) {
    $sql = "SELECT * FROM ... WHERE gux=?";
}

But what if the use selected foo and bar? This would also be very ugly and hard to implement the bind_params too.

Ideal would be a wildcard so I could do

//Pseudo code

$sql = "SELECT * FROM ... WHERE foo=* AND bar=* AND gux=*";

and only replace the * with an actual value when a filter for that column is selected. But this is not supported for integers.

Another option I thought of would be to do the following:

//Pseudo code

$sql = "SELECT * FROM ... WHERE foo=filter1 OR bar=filter2 OR gux=filter3";

And to programmatically replace the OR with AND when a value for that column is selected. This also would solve the bind_param issue. But I don't know if this is the best way to do it.

Upvotes: 0

Views: 686

Answers (1)

Nigel Ren
Nigel Ren

Reputation: 57121

As RiggsFolly has mentioned, build the WHERE clause dynamically. This code assumes there will be at least 1 option (otherwise add the WHERE conditionally as well.)

Each part is added to a list along with a corresponding bind array, this shows the data, you will need to add the actual API part for the database. The WHERE part is put together using implode() with AND as the glue...

$binds = [];
$where = [];
$types = '';
if (isset($_GET['foo'])) {
    $where[] = "foo=?";
    $binds[] = $_GET['foo'];
    $types .= "s";
}
if (isset($_GET['bar'])) {
    $where[] = "bar=?";
    $binds[] = $_GET['bar'];
    $types .= "s";
}
if (isset($_GET['gux'])) {
    $where[] = "gux=?";
    $binds[] = $_GET['gux'];
    $types .= "i";
}

$sql = "SELECT * FROM ... WHERE " . implode(" AND ", $where);

echo $sql.PHP_EOL;
print_r($binds);

with 1 in the foo value gives...

SELECT * FROM ... WHERE foo=?
Array
(
    [0] => 1
)

Then use the types and the binds in the bind_param()...

bind_param($types, ...$binds);

Upvotes: 2

Related Questions