时间戳

首页 > 解决方案 > Mysql嵌套并声明为数组

mysql - Mysql嵌套并声明为数组

问题描述

我有 2 个表想要调用并从中创建另一个数组。第一个表是组

| id |  name   | type |
  1    premium    pr
  2    basic      bs

第二个表是子组

| id |  group_id   |    name   |
  1        1          forever
  2        2          short

其实我想展示这样的代码。让另一个数组函数声明为子组

Array ( 
[id] => 1 
[name] => Premium 
[type] => pr 

)[sub-groups] => Array (
[0] => Array (
[id] => 1
[group_id] => 1
[name] => forever))

我创建了这个 PDO sql 连接

=================编辑代码=====================

function getGroups(){
    global $conn;
    $stmt = $conn->prepare("SELECT * FROM groups");
    $stmt->execute();
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    $groups = $stmt->fetchAll();
    foreach($groups as $key => $val){

        $stmt = $conn->prepare("SELECT * FROM sub_groups WHERE group_id = {$val['id']}");
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $program = $stmt->fetchAll();
        foreach($program as $key => $val){
            $groups['sub-groups'] = $program;
        }
    }
    return $groups;
}

该代码成功显示了高级组和基本组，但它没有显示主组内的子组。我错过了什么？任何有帮助的人都会很好。

标签： mysqlarraysnesteddeclare

解决方案

数组键必须是唯一的。如果您有两列同名，则只有其中一列可以出现在结果的关联数组中。

您需要为至少一个具有相同名称的列分配别名，以便它在结果中以不同方式显示。

SELECT g.name as group_name, sg.group_id, sg.id AS subgroup_id, sg.name AS subgroup_name
FROM groups AS g
LEFT JOIN subgroups AS sg ON sg.group_id = g.id

当您创建 PHP 结果时，$groups['sub-groups']需要是一个数组。每次通过循环时，您都用一个元素覆盖它。

<?php
function getGroups(){
    global $conn;
    $groups = [];
    $stmt = $conn->prepare("
                SELECT g.name as group_name, sg.group_id, sg.id AS subgroup_id, sg.name AS subgroup_name
                FROM groups AS g
                LEFT JOIN subgroups AS sg ON sg.group_id = g.id");
    $stmt->execute();
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    while ($row = $stmt->fetch()){
        if (!isset($groups[$row['group_name']])) {
            $groups[$row['group_name']] = $row;
            $groups[$row['group_name']]['sub-groups'] = [$row['subgroup_name']];
        } else {
            $groups[$row['group_name']]['sub-groups'][] = $row['subgroup_name'];
        }
    }
    return $groups;
}

推荐阅读