在php中选择WHERE子句

I'm trying to make a quiz application and I need to retrieve "n" number of questions from a database. I also need to filter the result with categories or difficulties filters.

我正在尝试制作一个测验应用程序，我需要从数据库中检索“n”个问题。我还需要使用类别或难度过滤器过滤结果。

I don't have any problem if the user make a category choice (for example) :

如果用户选择类别（例如），我没有任何问题：

$size = $_POST['param0'];
$category = $_POST['param1'];

$stmt = $db->prepare("SELECT *
                      FROM questions_fr
                      WHERE categories = :categories
                      LIMIT 0, $size");

$stmt->bindparam(":category", $category);
$stmt->execute();

But what if he wants all the categories ?

但如果他想要所有类别怎么办？

Can I do something like that to make only one query in my php file ?

我可以做这样的事情在我的php文件中只做一个查询吗？

$stmt = $db->prepare("SELECT *
                      FROM questions_fr
                      WHERE categories = * (here, select all the categories)
                      LIMIT 0, $size");

Or should I do something like this ?

或者我应该这样做？

(pseudocode)
if ($category != "all_categories")
{
    $stmt = $db->prepare("SELECT *
                          FROM questions_fr
                          WHERE categories = :categories
                          LIMIT 0, $size");
}
else if ($category == "all_categories")
{
    $stmt = $db->prepare("SELECT *
                          FROM questions_fr
                          LIMIT 0, $size");
}

2 个解决方案

#1

If they select 'all categories' set the category variable to a wildcard and use the LIKE clause.

如果他们选择“所有类别”，则将类别变量设置为通配符并使用LIKE子句。

if ($category == "all_categories") {
    $category = '%';
}

...

SELECT *
FROM questions_fr
WHERE categories LIKE :categories
LIMIT 0, $size

#2

Add another parameter.

添加另一个参数。

SELECT *
  FROM questions_fr
  WHERE categories = :categories
    OR 1 = :all_categories
  LIMIT 0, $size

Then just pass it as 1 if you want all categories.

如果你想要所有类别，那么只需将其传递为1即可。

#1