使用PHP和MySQL通过自连接查询显示层级分类数据_技术教程

本文详细介绍了如何利用mysql数据库的自连接（self-join）技术，结合php编程语言，从单一的分类表中高效地提取并展示具有父子层级关系的数据。教程将涵盖sql查询的构建，特别是left join的应用，以及如何在php中处理查询结果，最终生成一个结构清晰、包含子类别及其对应父类别信息的html表格。

场景概述：单一表中的层级数据

在许多应用中，例如产品分类、菜单导航或组织架构，我们经常需要存储具有层级关系的数据。一种常见的数据库设计模式是将这些层级数据存储在同一张表中，通过一个 parent_id 字段来引用其父级记录的 id。

考虑以下名为 category_table 的数据库表结构：

id	category	parent_id
1	category1
2	category2
3	category3
4	subcategory4	2
5	subcategory5	1
6	subcategory6	3
7	subcategory7	1

在这个表中，category1、category2、category3 是顶级分类，它们的 parent_id 为空（或NULL）。subcategory4 的 parent_id 为 2，表示它的父级是 category2。

我们的目标是生成一个表格，清晰地列出每个分类及其对应的父分类名称，即使是顶级分类也应被包含在内，且其父分类列显示为空。

解决方案核心：SQL自连接（Self-Join）

要从同一张表中获取父子关系，我们需要使用SQL的自连接（Self-Join）技术。自连接是指将表与其自身连接起来。通过为表的两个实例设置不同的别名，我们可以像处理两个不同的表一样来查询它们。

选择合适的连接类型

INNER JOIN: 如果只希望显示那些有明确父分类的子分类（即 parent_id 不为空且有效），可以使用 INNER JOIN。
LEFT JOIN: 如果需要显示所有分类（包括顶级分类和子分类），并且为顶级分类的父分类列显示为空，那么 LEFT JOIN 是更合适的选择。它会返回左表（所有分类）的所有行，即使右表（父分类）没有匹配项。

鉴于我们的目标是显示所有分类及其父分类（包括顶级分类），我们将采用 LEFT JOIN。

构建SQL查询

以下是实现预期输出的SQL查询：

SELECT
    c.id AS category_id,
    c.category AS category_name,
    p.category AS parent_category_name
FROM
    category_table c
LEFT JOIN
    category_table p ON c.parent_id = p.id
ORDER BY
    c.id;

查询解释：

FROM category_table c: 我们将 category_table 视为“子分类”表，并给它一个别名 c。
LEFT JOIN category_table p ON c.parent_id = p.id:
- 我们将 category_table 的另一个实例视为“父分类”表，并给它一个别名 p。
- ON c.parent_id = p.id 是连接条件，它将子分类的 parent_id 与父分类的 id 进行匹配。
- LEFT JOIN 确保即使 c.parent_id 没有在 p.id 中找到匹配项（即顶级分类），c 表中的行也会被包含在结果集中，此时 p.category 的值将为 NULL。
SELECT c.id AS category_id, c.category AS category_name, p.category AS parent_category_name: 我们选择子分类的 id、子分类的名称，以及匹配到的父分类的名称。通过使用 AS 关键字，我们为这些列指定了更具描述性的别名。
ORDER BY c.id: 结果按分类ID排序，以便于阅读。

PHP代码实现

接下来，我们将使用PHP来执行上述SQL查询，并将结果动态地渲染成一个HTML表格。

";
$html .= "";
$html .= "ID";
$html .= "Category";
$html .= "Parent Category";
$html .= "";
$html .= "";

// 遍历查询结果，填充表格行
while ($row = mysqli_fetch_assoc($result)) {
    $html .= "";
    $html .= "" . htmlspecialchars($row['category_id']) . "";
    $html .= "" . htmlspecialchars($row['category_name']) . "";
    // 如果 parent_category_name 为 NULL，则显示为空字符串
    $parent_name = $row['parent_category_name'] ? htmlspecialchars($row['parent_category_name']) : '';
    $html .= "" . $parent_name . "";
    $html .= "";
}

$html .= "";
$html .= "";

// 输出HTML表格
echo $html;

// 关闭数据库连接
mysqli_close($con);

?>

代码说明：

数据库连接：代码假设 $con 变量已经包含了有效的MySQL数据库连接。在实际应用中，您需要根据自己的数据库配置初始化 mysqli_connect()。
执行查询：使用 mysqli_query() 函数执行我们之前构建的SQL查询。
错误处理：对数据库连接和查询结果进行基本的错误检查，以确保应用程序的健壮性。
构建HTML表格：
- 初始化一个HTML表格字符串，包括表头（）和列名。
- 使用 while 循环遍历 mysqli_fetch_assoc() 返回的每一行结果。
- 对于每一行，创建和标签，并填充数据。
- 关键点：对于 parent_category_name 字段，我们检查它是否为 NULL。如果为 NULL（表示是顶级分类），则显示为空字符串；否则，显示实际的父分类名称。
- 使用 htmlspecialchars() 对从数据库中取出的数据进行编码，防止跨站脚本攻击（XSS）。
- 输出：最后，将生成的完整HTML表格字符串 echo 到浏览器。
- 关闭连接：使用 mysqli_close() 关闭数据库连接，释放资源。