使用PHP从MySQL获取多张表数据并结构化为JSON的教程

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

<?php
// 1. 数据库连接参数配置
$dbHost = 'localhost';     // 数据库主机名
$dbUser = 'your_username'; // 数据库用户名
$dbPass = 'your_password'; // 数据库密码
$dbName = 'your_database'; // 数据库名称

// 2. 建立数据库连接 (使用面向对象的mysqli)
$connection = new mysqli($dbHost, $dbUser, $dbPass, $dbName);

// 检查连接是否成功
if ($connection->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);

?>

{
  "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"
    }
  ]
}