在MySQL表上可选的多个过滤器

所以我有一个数据库表,其中包含以下列:

ID
Foo
Bar
Gux

Foo可以保存值[a,b,c],Bar [1、2、3]和Gux [x,y,z]。

在我的界面中,我有一个HTML选择框以对Foo,Bar和Gux进行过滤。问题在于用户可以按随机顺序过滤一个,两个或三个参数。

这给我带来了一个进行有效的SQL查询和bind_param的问题。

我可以做这样的事情:

//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=?";
}

但是,如果使用选择了foo和bar怎么办?这也将非常丑陋并且也难以实现bind_params。

理想情况是使用通配符,所以我可以

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

我想到的另一个选择是执行以下操作:

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

评论
eet
eet

正如RiggsFolly提到的那样,动态地构建WHERE子句。此代码假定将至少有1个选项(否则也有条件地添加WHERE。)

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 = [];
if (isset($_GET['foo'])) {
    $where[] = "foo=?";
    $binds[] = $_GET['foo'];
}
if (isset($_GET['bar'])) {
    $where[] = "bar=?";
    $binds[] = $_GET['bar'];
}
if (isset($_GET['gux'])) {
    $where[] = "gux=?";
    $binds[] = $_GET['gux'];
}

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

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

在foo值中有1给出...

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