PHP从MySQL获取多表数据并生成JSON教程

本教程详细介绍了如何使用PHP连接MySQL数据库，执行多条SQL查询以获取不同表的数据，然后将这些数据分别收集到PHP数组中，并最终整合成一个统一的关联数组，通过`json_encode`函数将其转换为结构化的JSON格式输出。这为前端应用提供了一种高效的数据接口实现方式。

引言

在现代Web应用开发中，后端API通常需要从数据库中检索数据并以JSON格式返回给前端。当数据分散在多个关联或非关联的数据库表中时，如何高效地一次性获取这些数据并将其结构化为一个统一的JSON对象是一个常见的需求。本教程将以一个典型的地理位置数据模型为例，演示如何使用PHP和MySQL实现这一目标。

数据库模型概述

我们将使用以下三个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": "..." }
  ]
}

实现步骤

要实现上述目标，主要包括以下几个步骤：

1. 建立数据库连接：使用PHP连接到MySQL数据库。
2. 执行多条查询：针对每张需要获取数据的表，分别执行SQL SELECT查询。
3. 收集查询结果：将每条查询的结果集逐行获取，并存储到独立的PHP数组中。
4. 构建最终数据结构：将这些独立的数组组合成一个顶级的关联数组，其键名与最终JSON的键名一致。
5. 编码为JSON并输出：使用json_encode()函数将PHP数组转换为JSON字符串，并通过HTTP响应头将其发送给客户端。

示例代码

以下是实现上述功能的PHP代码示例：

connect_errno) {
    // 连接失败时，记录错误并返回JSON错误信息
    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. Please try again later.']);
    exit();
}

// 设置字符集，防止数据乱码，推荐使用 utf8mb4
$connection->set_charset("utf8mb4");

// 3. 初始化用于存储各个表数据的数组
$countriesData = [];
$citiesData = [];
$neighborhoodsData = [];

// 4. 执行多条查询并收集结果

// 查询 Countries 表
$resultCountries = $connection->query("SELECT countryId, countryName FROM Countries");
if ($resultCountries) {
    while ($row = $resultCountries->fetch_assoc()) {
        $countriesData[] = $row;
    }
    $resultCountries->free(); // 释放结果集，减少内存占用
} else {
    // 查询失败时，记录错误
    error_log("Error querying Countries table: " . $connection->error);
    // 根据业务需求，可以选择终止脚本或继续执行
}

// 查询 Cities 表
$resultCities = $connection->query("SELECT cityId, cityName, countryId FROM Cities");
if ($resultCities) {
    while ($row = $resultCities->fetch_assoc()) {
        $citiesData[] = $row;
    }
    $resultCities->free(); // 释放结果集
} else {
    error_log("Error querying Cities table: " . $connection->error);
}

// 查询 Neighborhoods 表
$resultNeighborhoods = $connection->query("SELECT neighborhoodId, neighborhoodName, cityId FROM Neighborhoods");
if ($resultNeighborhoods) {
    while ($row = $resultNeighborhoods->fetch_assoc()) {
        $neighborhoodsData[] = $row;
    }
    $resultNeighborhoods->free(); // 释放结果集
} else {
    error_log("Error querying Neighborhoods table: " . $connection->error);
}

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

// 6. 构建最终的JSON数据结构
$output = [
    "Countries" => $countriesData,
    "Cities" => $citiesData,
    "Neighborhoods" => $neighborhoodsData
];

// 7. 设置HTTP响应头为JSON格式，并输出JSON数据
header('Content-Type: application/json');
// JSON_UNESCAPED_UNICODE 防止中文等非ASCII字符被转义
// JSON_PRETTY_PRINT 使输出的JSON格式化，更易读 (仅用于开发或调试，生产环境可省略以减小体积)
echo json_encode($output, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);

?>

输出示例

当上述PHP脚本成功执行后，它将输出一个类似以下结构的JSON字符串：

{
  "Countries": [
    {
      "countryId": "1",
      "countryName": "USA"
    },
    {
      "countryId": "2",
      "countryName": "Canada"
    }
  ],
  "Cities": [
    {
      "cityId": "101",
      "cityName": "New York",
      "countryId": "1"
    },
    {
      "cityId": "102",
      "cityName": "Los Angeles",
      "countryId": "1"
    },
    {
      "cityId": "201",
      "cityName": "Toronto",
      "countryId": "2"
    }
  ],
  "Neighborhoods": [
    {
      "neighborhoodId": "1001",
      "neighborhoodName": "Manhattan",
      "cityId": "101"
    },
    {
      "neighborhoodId": "1002",
      "neighborhoodName": "Brooklyn",
      "cityId": "101"
    },
    {
      "neighborhoodId": "2001",
      "neighborhoodName": "Downtown",
      "cityId": "201"
    }
  ]
}

注意事项与最佳实践

1. 错误处理：在实际生产环境中，数据库连接和查询的错误处理至关重要。示例代码中包含了基本的错误记录和友好的错误响应，这有助于调试和提升用户体验。
2. 安全性：虽然本教程中的SELECT *查询不涉及用户输入，但如果SQL查询中包含任何动态参数（例如，基于用户输入的WHERE条件），务必使用预处理语句（Prepared Statements）来防止SQL注入攻击。mysqli和PDO都支持预处理语句。
3. 性能优化：
   • 选择所需字段：避免使用SELECT *，而是明确列出所需的字段（例如SELECT countryId, countryName FROM Countries），这样可以减少数据传输量和数据库负载。
   • 索引优化：确保数据库表上的查询字段（尤其是WHERE子句和JOIN条件中的字段）有适当的索引，以提高查询速度。
   • 分页：如果某个表的数据量非常大，一次性获取所有数据可能会导致内存溢出或响应时间过长。在这种情况下，应考虑实现数据分页功能。
   • 缓存：对于不经常变动但频繁访问的数据，可以考虑使用缓存机制（如Redis或Memcached）来存储JSON结果，减少数据库查询次数。
4. 数据库抽象层：对于更复杂的项目，可以考虑使用PHP的PDO扩展，它提供了更统一的数据库访问接口，支持多种数据库类型，并且通常与ORM（对象关系映射）框架（如Laravel Eloquent, Doctrine）结合使用，进一步简化数据库操作。
5. HTTP响应头：设置Content-Type: application/json头是标准做法，它告诉客户端响应体是JSON格式，有助于客户端正确解析数据。
6. json_encode选项：JSON_UNESCAPED_UNICODE对于包含中文等非ASCII字符的数据非常有用，可以避免这些字符被转义成\uXXXX的形式。JSON_PRETTY_PRINT则用于格式化输出，方便阅读，但在生产环境中通常会省略以减小JSON文件大小。

总结

通过本教程，您应该已经掌握了如何使用PHP连接MySQL数据库，执行多条独立的SQL查询，并将这些查询结果有效地聚合到一个结构化的JSON对象中。这种方法是构建RESTful API或为前端应用提供数据接口的基础。在实际应用中，请务必考虑安全性、性能和可维护性，并根据项目需求选择最合适的技术和最佳实践。