如何从数据库数据构建特定结构的PHP三维数组

Hey there! Let's figure out how to generate that exact 3D array structure from your database. First, let's recap the target structure you want (formatted properly for clarity):

[
    'User Role Management' => [
        '' => ['Create User Role']
    ],
    'Config Management' => [
        'Organisation' => ['Create Country', 'Create Organisation'],
        'Site' => ['Create Site', 'Edit Site']
    ]
]

假设你的数据库表结构 #

I'm guessing your database table has at least these three fields: header_name, sub_header_name, and name (since those map directly to your array layers). If your field names are different, just swap them out in the code below.

完整实现代码 #

Here's a practical PHP example using PDO (adjust the database connection details to match your setup):

// 1. 建立数据库连接（示例用PDO，你也可以用mysqli）
$dbHost = 'your_host';
$dbName = 'your_database';
$dbUser = 'your_username';
$dbPass = 'your_password';

try {
    $pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("数据库连接失败: " . $e->getMessage());
}

// 2. 查询数据，按header和subheader排序，确保同组数据连续
$query = "SELECT header_name, sub_header_name, name FROM your_table_name ORDER BY header_name, sub_header_name";
$stmt = $pdo->prepare($query);
$stmt->execute();
$records = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 3. 初始化目标数组并逐步构建
$targetArray = [];

foreach ($records as $record) {
    $header = $record['header_name'];
    // 处理sub_header_name为空的情况，默认用空字符串作为键
    $subHeader = $record['sub_header_name'] ?? '';
    $name = $record['name'];

    // 如果当前header还没在数组里，先创建它
    if (!isset($targetArray[$header])) {
        $targetArray[$header] = [];
    }

    // 如果当前subHeader还没在header下，先创建它
    if (!isset($targetArray[$header][$subHeader])) {
        $targetArray[$header][$subHeader] = [];
    }

    // 将name添加到对应的层级中
    $targetArray[$header][$subHeader][] = $name;
}

// 验证结果（可选）
print_r($targetArray);

关键说明 #

• 处理空sub_header: 用?? ''确保即使sub_header_name为NULL，也会用空字符串作为第二层键，和你的示例结构完全匹配。
• 排序查询结果: 按header_name和sub_header_name排序可以让同组的记录连续，避免重复检查键的存在（虽然代码里已经做了检查，但排序会让逻辑更顺畅）。
• 灵活性: 如果你的字段名不同，比如header instead of header_name，只需要修改$record['xxx']里的键名即可适配。

这个代码会完美生成你想要的三维数组结构，不管是有sub_header的场景还是无sub_header的场景都能覆盖到。

内容的提问来源于stack exchange，提问作者kevin_marcus