从mysql数据库和php获取多级json

ttvkxqim  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(399)

我有table


我想检索一个json字符串的格式

  1. [{
  2. "topic_id":"1",
  3. "topic_title":"Input and Output devices",
  4. "topic_image":"inputs.png",
  5. "subtopics": {
  6. "subtopic_id":"1",
  7. "subtopic_title":"Inputs",
  8. "subtopic_file_url":"0"
  9. },
  10. {
  11. "subtopic_id":"2",
  12. "subtopic_title":"Outputs",
  13. "subtopic_file_url":"0"
  14. }
  15. }]

我试过的

  1. // set the resulting array to associative
  2. $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
  3. foreach ($result as $key => $value) {
  4. $topic[$key]['topic_id'] = $value['topic_id'];
  5. $topic[$key]['topic_title'] = $value['topic_title'];
  6. $topic[$key]['topic_image'] = $value['topic_image'];
  7. $topic[$key]['subtopics']['subtopic_id'] = $value['subtopicid'];
  8. $topic[$key]['subtopics']['subtopic_title'] = $value['subtopic_title'];
  9. $topic[$key]['subtopics']['subtopic_file_url'] = $value['file_url'];
  10. $topic[$key]['subtopics']['subtopicid'] = $value['subtopicid'];
  11. }
  12. print 'PHP_ARRAY: ';print_r($topic);
  13. $topic = json_encode ( $topic );

上面的脚本返回每个子主题的主题详细信息。

kjthegm6

kjthegm61#

  1. This code has worked. Thank you everyone
  2. <?php
  3. $servername = "***";
  4. $username = "****";
  5. $password = "***";
  6. $dbname = "****";
  7. function searchForId($id, $array) {
  8. foreach ($array as $key => $val) {
  9. if ($val['topic_id'] === $id) {
  10. return $key;
  11. }
  12. }
  13. return null;
  14. }
  15. try {
  16. $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  17. $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  18. $stmt = $conn->prepare("select a.topic_id, a.topic_title, a.topic_image, b.subtopicid,b.subtopic_title,b.file_url from topics a join subtopics b on b.topic_id = a.topic_id ");
  19. $stmt->execute();
  20. // set the resulting array to associative
  21. $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
  22. $x = 0;
  23. foreach ($result as $key => $value) {
  24. if($x != 0){
  25. if($topic[$x-1]['topic_id'] != $value['topic_id']) {
  26. $topic[$x]['topic_id'] = $topic_id = $value['topic_id'];
  27. $topic[$x]['topic_title'] = $value['topic_title'];
  28. $topic[$x]['topic_image'] = $value['topic_image'];
  29. $x++;
  30. }
  31. } else {
  32. $topic[$key]['topic_id'] = $topic_id = $value['topic_id'];
  33. $topic[$key]['topic_title'] = $value['topic_title'];
  34. $topic[$key]['topic_image'] = $value['topic_image'];
  35. $x++;
  36. }
  37. }
  38. $x = $y = 0;
  39. foreach ($result as $key => $value) {
  40. if($y != searchForId($value['topic_id'], $topic))
  41. $x = 0;
  42. $y = searchForId($value['topic_id'], $topic);
  43. $topic[$y]['subtopics'][$x]['subtopic_id'] = $value['subtopicid'];
  44. $topic[$y]['subtopics'][$x]['subtopic_title'] = $value['subtopic_title'];
  45. $topic[$y]['subtopics'][$x]['subtopic_file_url'] = $value['file_url'];
  46. // $topic[$y]['subtopics'][$x]['subtopicid'] = $value['subtopicid'];
  47. $x++;
  48. }
  49. // foreach ($result as $key => $value) {
  50. // }
  51. //print 'PHP_ARRAY: ';print_r($topic);
  52. $topic = json_encode ( $topic );
  53. print_r($topic);
  54. }
  55. catch(PDOException $e) {
  56. echo "Error: " . $e->getMessage();
  57. }
  58. $conn = null;
  59. ?>
展开查看全部

相关问题