时间戳

首页 > 解决方案 > 选择所有提交的复选框值存在于 mySQL 列中的逗号分隔字符串中的所有行

php - 选择所有提交的复选框值存在于 mySQL 列中的逗号分隔字符串中的所有行

问题描述

我希望能够仅将所有提交的SELECT行都存在于. 我的代码选择具有任何已提交复选框值的行。WHERE checkbox valueskeyWords

我有一个带有表格照片的 mySQL 数据库 PhotoArchive

| id  | title   |filePath      | keyWords              |
| 1   | mypic1  |imgs/pic1.jpg | Mono,Portrait (etc)   |
| 2   | mypic1  |imgs/pic2.jpg | Colour,Portrait (etc) |
| 3   | mypic2  |imgs/pic2.jpg | Mono,Landscape (etc)  |
| 4   | mypic2  |imgs/pic2.jpg | Colour,Landscape (etc)|

我有一个带有复选框表单的 html 页面

<form action="selectFromDb.php" method="get">
<input type="checkbox" value="Mono" name="category[]" id="OptMono" />
  <label for="OptMono"> Monochrome</label>
<input type="checkbox" value="Colour" name="category[]" id="OptColour" />
  <label for="OptColour"> Colour</label>
<input type="checkbox" value="Portraits" name="category[]" id="OptPortraits" />
  <label for="OptPortraits"> Portraits</label>
<input type="checkbox" value="Landscape" name="category[]" id="OptLandscape" />
  <label for="OptLandscape"> Landscape</label>     
<input type="submit" value="Submit" />
和使用此 SELECT 语句的 selectFromDb.php 文件 
 <? foreach($_GET['category'] as $category){
     $categories[] = $category;
   }
  $total_imgs_sql = "SELECT id, title, filePath FROM photos WHERE keyWords LIKE '%$category%'
 ?>

这将返回 中$category存在任何值的行keyWords。我只想返回WHERE 所有$category keyWords 存在的行。

如何做到这一点？（完整版有近 200 个复选框，每行可以有大约 20 个逗号分隔的单词keyWords）
谢谢

标签： phpmysqlarraysselectmysqli

解决方案

您可以通过在查询中构建类别值表，然后JOIN将其添加到photos表中，并仅选择返回与类别值表中相同行数的照片来执行此操作。作为示例查询，要查找具有Colour和Portrait类别的照片：

SELECT p.*
FROM (
  SELECT 'Colour' AS keyWord
  UNION ALL
  SELECT 'Portrait' AS keyWord
) k
JOIN photos p ON CONCAT(',', p.keyWords, ',') LIKE CONCAT('%,', k.keyWord, ',%')
GROUP BY p.id
HAVING COUNT(*) = 2

在 db-fiddle 上查询演示

在 PHP 中，您可以像这样生成此查询：

$categories = array('Colour', 'Portrait');

$sql = "SELECT p.*\nFROM (\n  SELECT '";
$sql .= implode("' AS keyword\n  UNION ALL\n  SELECT '", $categories);
$sql .= "' AS keyWord\n) k\nJOIN photos p ON CONCAT(',', p.keyWords, ',') LIKE CONCAT('%,', k.keyWord, ',%')\n";
$sql .= "GROUP BY p.id\nHAVING COUNT(*) = " . count($categories);

3v4l.org 上的PHP演示

请注意，为了便于阅读，我留下了\n使输出查询与上面匹配的内容； 你可以用（空格）替换那些。

另请注意，您可能对 SQL 注入持开放态度，您应该检查$category值以确保它们有效（使用关键字白名单），或转换为准备好的语句以避免这种情况。以下是使用准备好的语句的方法（假设您使用的是 mysqli 并且您的连接位于$conn）：

$categories = array('Colour', 'Portrait');

$sql = "SELECT p.*\nFROM (\n  SELECT ";
$sql .= implode(" AS keyword\n  UNION ALL\n  SELECT ", array_fill(0, count($categories), '?'));
$sql .= " AS keyWord\n) k\nJOIN photos p ON CONCAT(',', p.keyWords, ',') LIKE CONCAT('%,', k.keyWord, ',%')\n";
$sql .= "GROUP BY p.id\nHAVING COUNT(*) = " . count($categories);

$stmt = $conn->prepare($sql);
$stmt->bind_param(str_repeat('s', count($categories)), ...$categories);
$stmt->execute();
$result = $stmt->get_result();
foreach ($result as $row) {
    print_r($row);
}

另请注意，如果您在后者中拥有每张照片/关键字组合一行的表格，而不是使用逗号分隔的数据，这会更简单（我强烈建议阅读keywords此问答）。photo_keywords

推荐阅读