从MySQL多表获取数据并生成结构化JSON输出的PHP教程

花韻仙語

发布时间：2025-12-13 16:10:23

923人浏览过

来源于php中文网

原创

从MySQL多表获取数据并生成结构化JSON输出的PHP教程

本教程详细讲解如何使用php从mysql数据库中获取多个表的数据，并将其整合成一个结构化的json对象。通过执行独立的sql查询，将每个表的数据分别存入php数组，最终合并这些数组并使用json_encode函数生成符合api接口规范的json输出。

在现代Web应用开发中，后端API经常需要从数据库中获取多个相关或不相关的数据集，并将它们以统一的、易于前端消费的JSON格式返回。例如，一个地理信息服务可能需要同时提供国家、城市和社区的数据。本文将指导您如何使用PHP和MySQL实现这一功能，将来自不同表的数据聚合到一个JSON响应中。

数据库结构示例

假设我们有以下三个MySQL表，用于存储地理位置信息：

Countries (国家表)
- countryId (主键)
- countryName
Cities (城市表)
- cityId (主键)
- cityName
- countryId (外键，关联Countries表)
Neighborhoods (社区表)
- neighborhoodId (主键)
- neighborhoodName
- cityId (外键，关联Cities表)

我们期望的JSON输出格式是这样的：

{
  "Countries": [
    { "countryId": "...", "countryName": "..." }
  ],
  "Cities": [
    { "cityId": "...", "cityName": "...", "countryId": "..." }
  ],
  "Neighborhoods": [
    { "neighborhoodId": "...", "neighborhoodName": "...", "cityId": "..." }
  ]
}

核心思路：分步查询与数据整合

要实现上述JSON结构，最直接有效的方法是：

LOGO.com

在线生成Logo，100%免费

下载

立即学习“PHP免费学习笔记（深入）”；

建立数据库连接。
针对每个需要获取数据的表，执行独立的SQL SELECT 查询。 这样做的好处是每个查询都只关注一个表的数据，逻辑清晰。
将每个查询的结果集分别转换为PHP关联数组的数组。
创建一个主PHP关联数组，以表名（或您希望在JSON中作为键的名称）作为键，以对应表的数据数组作为值。
使用PHP的 json_encode() 函数将这个主数组转换为JSON字符串。

PHP实现：代码示例与解析

下面是实现这一功能的完整PHP代码示例：

connect_errno) {
    // 连接失败，输出错误信息并终止脚本
    error_log("Failed to connect to MySQL: " . $connection->connect_error);
    http_response_code(500); // 设置HTTP状态码为500 (Internal Server Error)
    echo json_encode(["error" => "Database connection failed."]);
    exit();
}

// 初始化存储所有表数据的数组
$allTablesData = [];

// 2. 针对每个表执行独立的SQL查询并获取数据

// 获取 Countries 表数据
$countriesResult = $connection->query("SELECT countryId, countryName FROM Countries");
$countriesData = [];
if ($countriesResult) {
    while ($row = $countriesResult->fetch_assoc()) {
        $countriesData[] = $row;
    }
    $countriesResult->free(); // 释放结果集内存
} else {
    error_log("Error fetching Countries data: " . $connection->error);
    // 可根据需求决定是否在此处终止或返回错误
}
$allTablesData['Countries'] = $countriesData;


// 获取 Cities 表数据
$citiesResult = $connection->query("SELECT cityId, cityName, countryId FROM Cities");
$citiesData = [];
if ($citiesResult) {
    while ($row = $citiesResult->fetch_assoc()) {
        $citiesData[] = $row;
    }
    $citiesResult->free(); // 释放结果集内存
} else {
    error_log("Error fetching Cities data: " . $connection->error);
}
$allTablesData['Cities'] = $citiesData;


// 获取 Neighborhoods 表数据
$neighborhoodsResult = $connection->query("SELECT neighborhoodId, neighborhoodName, cityId FROM Neighborhoods");
$neighborhoodsData = [];
if ($neighborhoodsResult) {
    while ($row = $neighborhoodsResult->fetch_assoc()) {
        $neighborhoodsData[] = $row;
    }
    $neighborhoodsResult->free(); // 释放结果集内存
} else {
    error_log("Error fetching Neighborhoods data: " . $connection->error);
}
$allTablesData['Neighborhoods'] = $neighborhoodsData;


// 3. 设置HTTP响应头为JSON
header('Content-Type: application/json');

// 4. 将整合后的PHP数组编码为JSON并输出
echo json_encode($allTablesData, JSON_PRETTY_PRINT); // JSON_PRETTY_PRINT 可使输出格式更易读

// 5. 关闭数据库连接
$connection->close();

?>

代码解析：

数据库连接： 使用 new mysqli() 建立与MySQL数据库的连接。务必替换 your_username, your_password, your_database_name 为您的实际数据库凭据。通过 connect_errno 和 connect_error 进行错误检查是至关重要的，以确保数据库连接成功。
初始化数据容器： $allTablesData = []; 创建了一个空的PHP关联数组，它将最终包含所有表的数据，并作为 json_encode() 的输入。
独立查询与结果处理：
- 对于每个表（Countries, Cities, Neighborhoods），都执行一个独立的 SELECT * FROM TableName (或者指定列名 SELECT column1, column2 FROM TableName) 查询。
- $connection->query() 方法执行SQL语句并返回一个结果集对象（mysqli_result）。
- 通过 if ($result) 检查查询是否成功。如果查询失败，query() 方法会返回 false。
- while ($row = $result->fetch_assoc()) 循环遍历结果集中的每一行，并以关联数组的形式获取数据。
- $data[] = $row; 将每一行数据添加到对应的表数据数组中。
- $result->free(); 在处理完结果集后，及时释放内存是一个良好的实践。
整合数据： 将每个表的数据数组（$countriesData, $citiesData, $neighborhoodsData）分别赋值给 $allTablesData 数组中对应的键（'Countries', 'Cities', 'Neighborhoods'）。这些键将直接成为最终JSON对象的顶级键。
设置HTTP头： header('Content-Type: application/json'); 告知客户端响应内容是JSON格式，这对于API接口至关重要。
JSON编码与输出： echo json_encode($allTablesData, JSON_PRETTY_PRINT); 将 $allTablesData 数组转换为JSON字符串并输出。JSON_PRETTY_PRINT 常量用于格式化输出，使其更具可读性，在生产环境中通常可以省略以减少带宽。
关闭连接： $connection->close(); 在脚本结束前关闭数据库连接，释放资源。

注意事项与最佳实践

错误处理： 示例代码中包含了基本的数据库连接和查询错误处理。在生产环境中，应该实现更健壮的错误日志记录机制，并向客户端返回友好的错误信息，而不是直接暴露数据库错误详情。
资源管理： 确保在获取数据后使用 $result->free() 释放结果集内存，并在脚本结束时使用 $connection->close() 关闭数据库连接。
性能考量： 对于非常大的数据集，一次性将所有数据加载到内存中可能会消耗大量资源。如果单个表的数据量极大，可以考虑分页查询或流式传输数据。然而，对于大多数API场景，这种多表聚合方式是高效且实用的。
安全性： 虽然本例中的 SELECT * 查询不涉及用户输入，但如果您的查询包含动态参数（如 WHERE id = ?），务必使用预处理语句（Prepared Statements）来防止SQL注入攻击。mysqli 提供了 prepare() 和 bind_param() 方法来实现这一点。
字段选择： 尽可能只选择您需要的字段，而不是使用 SELECT *。这可以减少数据传输量，提高性能。例如，SELECT countryId, countryName FROM Countries 比 SELECT * FROM Countries 更优。
JSON键名： 您可以根据API设计规范自由定义 $allTablesData 数组的键名，它们将直接映射到JSON对象的顶级键。