group-by-json输出

66bbxpm5  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(347)
  1. <?php
  2. $link = mysqli_connect("localhost", "root", "", "college");
  3. $query = "SELECT branch, year,semester, COUNT(*) count FROM students GROUP BY branch,year,semester;";
  4. $result = mysqli_query($link,$query);
  5. $rows= mysqli_fetch_all($result,MYSQLI_ASSOC);
  6. echo json_encode($rows);
  7. ?>

这是我的输出:
[{“branch”:“cse”,“year”:“1”,“term”:“1”,“count”:“4”},{“branch”:“cse”,“year”:“2”,“term”:“1”},{“branch”:“cse”,“year”:“2”,“term”:“2”,“count”:“2”},{“branch”:“cse”,“year”:“2”,“term”:“4”,“count”:“1”},{“branch”:“cse”,“year”:“3”,“term”:“count”:“2”},{“branch”:“cse”,“年”:“4”,“学期”:“3”,“计数”:“1”},{“分行”:“cse”,“年”:“4”,“学期”:“4”,“计数”:“2”},{“分行”:“ece”,“年”:“1”,“学期”:“4”,“计数”:“2”},{“分行”:“ece”,“年”:“2”,“学期”:“2”,“计数”:“2”},{“分行”:“ece”,“年”:“2”,“学期”:“3”,“计数”:“1”},{“分行”:“ece”,“年”:“3”,“学期”:“1”,“计数”:“1”},{“分行”:“ece”,“年”:“3”,“学期”:“3”,“计数”:“2”},{“分行”:“ece”,“年”:“4”,“学期”:“2”,“计数”:“2”},{“分行”:“ece”,“年”:“4”,“学期”:“4”,“计数”:“2”},{“分行”:“eee”,“年”:“1”,“学期”:“1”,“计数”:“1”},{“分行”:“eee”,“年”:“1”,“学期”:“4”,“计数”:“1”},{“分行”:“eee”,“年”:“2”,“学期”:“1”,“计数”:“1”},{“分行”:“eee”,“年”:“2”,“学期”:“2”,“count”:“3”},{“branch”:“eee”,“year”:“2”,“term”:“3”,“count”:“2”},{“branch”:“eee”,“year”:“2”,“term”:“4”,“count”:“1”},{“branch”:“eee”,“year”:“3”,“term”:“1”,“count”:“1”},{“branch”:“eee”,“year”:“3”,“term”:“3”,“count”:“4”},{“branch”:“eee”,“year”:“3”,“term”:“4”,“count”:“3”},{“分行”:“eee”,“年”:“4”,“学期”:“2”,“计数”:“3”},{“分行”:“eee”,“年”:“4”,“学期”:“3”,“计数”:“2”},{“分行”:“eee”,“年”:“4”,“学期”:“4”,“计数”:“1”}]
但我想要这样的输出:

  1. {"cse":
  2. [{"year":"1","semester":"1","count":"4"},
  3. {"year":"2","semester":"1","count":"1"},
  4. {"year":"2","semester":"2","count":"2"},
  5. {"year":"2","semester":"4","count":"1"},
  6. {"year":"3","semester":"2","count":"1"},
  7. {"year":"3","semester":"3","count":"2"},
  8. {"year":"4","semester":"3","count":"1"},
  9. {"year":"4","semester":"4","count":"2"}],
  10. "ece":
  11. [{"year":"1","semester":"4","count":"2"},
  12. {"year":"2","semester":"2","count":"2"},
  13. {"year":"2","semester":"3","count":"1"},
  14. {"year":"3","semester":"1","count":"1"},
  15. {"year":"3","semester":"3","count":"2"},
  16. {"year":"4","semester":"2","count":"2"},
  17. {"year":"4","semester":"4","count":"2"}],
  18. "eee":
  19. [{"year":"1","semester":"1","count":"1"},
  20. {"year":"1","semester":"4","count":"1"},
  21. {"year":"2","semester":"1","count":"1"},
  22. {"year":"2","semester":"2","count":"3"},
  23. {"year":"2","semester":"3","count":"2"},
  24. {"year":"2","semester":"4","count":"1"},
  25. {"year":"3","semester":"1","count":"1"},
  26. {"year":"3","semester":"3","count":"4"},
  27. {"year":"3","semester":"4","count":"3"},
  28. {"year":"4","semester":"2","count":"3"},
  29. {"year":"4","semester":"3","count":"2"},
  30. {"year":"4","semester":"4","count":"1"}]}

建议任何其他方式来显示上述输出

c86crjj0

c86crjj01#

而不是 mysqli_fetch_all ,一次获取每一行并在一次操作中将其转换为正确格式可能更容易(也可能更快)。。。

  1. $result = mysqli_query($link,$query);
  2. $output = [];
  3. while ( $rows= mysqli_fetch_assoc($result)) {
  4. $output [$rows['branch']][] = ["year" => $rows['year'],
  5. "semester" => $rows['semester'],
  6. "count" => $rows['count']];
  7. }
  8. echo json_encode($output);

相关问题