php - 从 MYSQL PHP 创建嵌套 JSON 时只得到一个结果

Question

我对 mysql 和 php 都很陌生。

因此，我正在制作 2 个 sql 查询并将它们与 kampid 绑定在一起，作为来自这两个查询的。

一切都在同一张桌子上，没有不同，所以我知道我不能

            $array1 = array();
            $array2 = array();

            $sql = "select r.kampid, q.trade from (
            select kampid
            from posts where kampid != 0
            group by kampid
            ) r
            join
            (
             select p.kampid, p.trade
             from
             (
              select kampid, trade, count(*) as cnt
              from posts
              group by kampid, trade
              ) p
             join
             (
              select kampid, max(cnt) as maxcnt
              from (
                    select kampid, trade, count(*) as cnt
                    from posts
                    group by kampid, trade) x
              group by kampid) y
             on y.kampid = p.kampid and y.maxcnt = p.cnt
             ) q
            on r.kampid = q.kampid";

            //prepare statement
            $statement = $this->conn->prepare($sql);

            //catch error
            if (!$statement){
                throw new Exception($statement->error);
            }
            $statement->execute();

            //result we got in execution
            $result = $statement->get_result();

            // each line append to array new row one by one when it is found
            while ($row = $result->fetch_assoc()){



               // $array1[$row["kampid"]][] = array( $row['trade'] );
                $array1['kampid'] = $row['kampid'];
                $array1['trade'] = array($row['trade']);
                $array1['department'] = array();






                $sql2 = "SELECT COUNT(posts.companyName) AS companies,
                posts.kampid, posts.department, SUM(posts.people)AS total
                           FROM posts WHERE kampid = '".$row['kampid']."' GROUP BY posts.department, posts.kampid ORDER BY posts.kampid, posts.department ASC";

                           //prepare statement
                           $statement = $this->conn->prepare($sql2);

                           //catch error
                           if (!$statement){
                               throw new Exception($statement->error);
                           }
                           $statement->execute();
                           //result we got in execution
                           $result = $statement->get_result();

                while ($row2 = $result->fetch_assoc()){



                    $array2['department'] = $row2['department'];
                    $array2['companies'] = $row2['companies'];
                    $array2['total'] = $row2['total'];
                    array_push($array1[department], $array2);



                      }



            }


            $output_arr = array();
            array_push($output_arr, $array1);

            $jsonData = json_encode($output_arr, JSON_PRETTY_PRINT);


echo $jsonData; 

        }

我得到这个输出：

[
  {
    "kampid": 1901,
    "trade": [
      "Åkeri"
    ],
    "department": [
      {
        "department": 3,
        "companies": 8,
        "total": 63
      },
      {
        "department": 4,
        "companies": 38,
        "total": 114
      }
    ]
  }
]

但是json在第一个“kampid”之后停止，当有更多“kampid”1902、1903等时

我想要的结果如下：

[
  {
    "kampid": 1901,
    "trade": [
      "Truck"
    ],
    "department": [
      {
        "department": 3,
        "companies": 8,
        "total": 63
      },
      {
        "department": 4,
        "companies": 38,
        "total": 114
      }
    ]
  },
{
    "kampid": 1902,
    "trade": [
      "Mechanic"
    ],
    "department": [
      {
        "department": 3,
        "companies": 8,
        "total": 63
      },
      {
        "department": 4,
        "companies": 38,
        "total": 114
      }
    ]
  },
{
    "kampid": 1903,
    "trade": [
      ""
    ],
    "department": [
      {
        "department": 3,
        "companies": 8,
        "total": 63
      },
      {
        "department": 4,
        "companies": 38,
        "total": 114
      }
    ]
  }
]

有人可以指出我做错了什么吗？

编辑：得到移动“array_push($output_arr, $array1);”的答案 在外循环内，但对于一致的结果，我需要重命名第二个 $result 和 $statement

标签： phpmysqljsonnested