php - mySQL、PHP、带有可排序列的 HTML 表 - 但某些列值存在问题

Question

我有一个 mySQL 数据库表，其中包含如下列:

项目

• item_id
• 姓名
• 大小(例如，以字节为单位)
• 描述
• 日期

另一个表与第一个表具有 1:* 关系，其中包含描述由第一个表中的字段表示的项目的“关键字”:

Item_Keyword

• item_id
• 关键字

我使用 PHP 从 Item 表中“select *”，然后迭代返回的行(使用 while 循环和 fetch_assoc())来构建包含所有数据的 HTML 表。

在 while 循环的每次迭代中，我对 Item_Keyword 表中与 Item 表中当前元组的 id 匹配的所有关键字执行另一个查询，然后将它们添加到表中的列中，连接在一起:

$res = getItems();
if ($res)
{
  while ($row = $res->fetch_assoc())
  {
    ...
    $itemID = $row['item_id];
    $res2 = getKeywords($itemID);
    if ($res2)
    {
      $keywords = "";
      while ($row2 = $res2->fetch_assoc())
      {
        if (strlen($keywords) > 0)
          $keywords .= ", ";
        $keywords .= $row2['keyword'];
      }
      $text .= "<td> " . $keywords . " </td>";
     }
   }
}

所以我最终得到了一个看起来像这样的表格:

ID  Name    Size  Description  Date    Keywords
0   Widget  1024  Widget one   010101  widget,one,item

我正在尝试使 HTML 表格列可排序。我可以想象这将如何与 Item 表上的查询中的 ORDER BY 一起使用。问题是，由于关键字存储在单独的表中并使用独立查询检索，我只能按一组查询结果或另一组查询结果进行排序，但不能同时对两者进行排序 - 更简洁的是，我看不到任何排序方式按关键字列中的值。

是否有某种深奥的查询可以让我选择项目的关键字，并将这些包含在项目表查询的“FROM”参数中？像这样的东西:

SELECT item_id, name, size, description, date (SELECT keyword FROM Item_Keyword WHERE item_id = 0)
FROM Item
WHERE item_id = 0

Answer 1

首先，您可能应该通过像这样的单个查询来获取所有数据:

SELECT
  i.item_id AS `item_id`,
  i.name AS `name`,
  i.size AS `size`,
  i.description AS `description`,
  i.date AS `date`,
  GROUP_CONCAT(ik.keyword) AS `keywords`
FROM item AS i
LEFT JOIN item_keyword AS ik
  ON item.item_id = item_keyword.item_id
GROUP BY i.item_id
ORDER BY i.item_id ASC, ik.keyword ASC /* OR whatever sort you desire */

请注意，我使用 GROUP_CONCAT() 和 GROUP BY 子句，以便将所有关键字条目折叠到与每个 item_id 关联的单行中。

这可以在一次数据库调用中为您提供所需的一切，从而消除所有不必要的额外查询。您也可以在没有分组依据的情况下执行此操作，您只会有更多行(即具有 3 个关键字的项目将产生 3 行)。通过这种方法，您可以在渲染表格之前将数据读入多维数组。