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

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

<?php
// 数据库连接参数
$dbHost = "your_db_host";      // 例如: localhost
$dbUser = "your_db_username";  // 您的数据库用户名
$dbPass = "your_db_password";  // 您的数据库密码
$dbName = "your_db_name";      // 您的数据库名称

// 建立MySQLi连接
$connection = new mysqli($dbHost, $dbUser, $dbPass, $dbName);

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

// 设置字符集，防止中文乱码
$connection->set_charset("utf8mb4");

// 声明用于存储各个表数据的数组
$countriesData = [];
$citiesData = [];
$neighborhoodsData = [];

// ... 后续代码 ...

?>

<?php
// ... (数据库连接代码) ...

// 查询 Countries 表
$resultCountries = $connection->query("SELECT * 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 * 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 * FROM Neighborhoods");
if ($resultNeighborhoods) {
    while ($row = $resultNeighborhoods->fetch_assoc()) {
        $neighborhoodsData[] = $row;
    }
    $resultNeighborhoods->free(); // 释放结果集内存
} else {
    error_log("Error querying Neighborhoods table: " . $connection->error);
}

// ... (构建最终数据结构和JSON编码代码) ...

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

<?php
// ... (数据库连接和查询代码) ...

// 构建最终的PHP关联数组，键名与期望的JSON键名一致
$finalOutput = [
    "Countries" => $countriesData,
    "Cities" => $citiesData,
    "Neighborhoods" => $neighborhoodsData
];

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

// 将PHP数组编码为JSON字符串并输出
echo json_encode($finalOutput, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);

// 关闭数据库连接 (确保在所有操作完成后执行)
$connection->close();
?>

<?php
// 1. 数据库连接参数
$dbHost = "localhost";
$dbUser = "root";
$dbPass = "your_password"; // 请替换为您的数据库密码
$dbName = "your_database"; // 请替换为您的数据库名称

// 2. 建立MySQLi连接
$connection = new mysqli($dbHost, $dbUser, $dbPass, $dbName);

// 检查连接是否成功
if ($connection->connect_errno) {
    http_response_code(500);
    echo json_encode(["error" => "Failed to connect to MySQL: " . $connection->connect_error]);
    exit();
}

// 设置字符集
$connection->set_charset("utf8mb4");

// 3. 声明用于存储各个表数据的数组
$countriesData = [];
$citiesData = [];
$neighborhoodsData = [];
$errors = []; // 用于收集查询过程中的错误

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

// 查询 Countries 表
$resultCountries = $connection->query("SELECT * FROM Countries");
if ($resultCountries) {
    while ($row = $resultCountries->fetch_assoc()) {
        $countriesData[] = $row;
    }
    $resultCountries->free();
} else {
    $errors[] = "Error querying Countries table: " . $connection->error;
    error_log("Error querying Countries table: " . $connection->error);
}

// 查询 Cities 表
$resultCities = $connection->query("SELECT * FROM Cities");
if ($resultCities) {
    while ($row = $resultCities->fetch_assoc()) {
        $citiesData[] = $row;
    }
    $resultCities->free();
} else {
    $errors[] = "Error querying Cities table: " . $connection->error;
    error_log("Error querying Cities table: " . $connection->error);
}

// 查询 Neighborhoods 表
$resultNeighborhoods = $connection->query("SELECT * FROM Neighborhoods");
if ($resultNeighborhoods) {
    while ($row = $resultNeighborhoods->fetch_assoc()) {
        $neighborhoodsData[] = $row;
    }
    $resultNeighborhoods->free();
} else {
    $errors[] = "Error querying Neighborhoods table: " . $connection->error;
    error_log("Error querying Neighborhoods table: " . $connection->error);
}

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

// 6. 构建最终的PHP关联数组
$finalOutput = [
    "Countries" => $countriesData,
    "Cities" => $citiesData,
    "Neighborhoods" => $neighborhoodsData
];

// 如果有错误，可以考虑将错误信息也包含在JSON输出中，或者直接返回错误状态
if (!empty($errors)) {
    // 示例：将错误信息添加到输出中，或者直接返回HTTP 500
    // http_response_code(500);
    // echo json_encode(["error" => "Data retrieval issues", "details" => $errors]);
    // exit();
    // 对于本教程，我们暂时忽略错误，继续输出已获取的数据
}

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

// 8. 将PHP数组编码为JSON字符串并输出
echo json_encode($finalOutput, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);

?>