php - MySQL对表结构进行查询以制作JSON友好格式

Question

我想要得到这样的结果，展开所有节点并创建一个定义的 TreeView 。

    var defaultData = [
    {
    text: 'Parent 1',
    href: '#parent1',
    tags: ['4'],
    nodes: [
      {
        text: 'Child 1',
        href: '#child1',
        tags: ['2'],
        nodes: [
          {
            text: 'Grandchild 1',
            href: '#grandchild1',
            tags: ['0']
          },
          {
            text: 'Grandchild 2',
            href: '#grandchild2',
            tags: ['0']
          }
        ]
      },
      {
        text: 'Child 2',
        href: '#child2',
        tags: ['0']
      }
    ]
  },
  {
    text: 'Parent 2',
    href: '#parent2',
    tags: ['0']
  },
  {
    text: 'Parent 3',
    href: '#parent3',
     tags: ['0']
  },
  {
    text: 'Parent 4',
    href: '#parent4',
    tags: ['0']
  },
  {
    text: 'Parent 5',
    href: '#parent5'  ,
    tags: ['0']
  }
];

我创建了一个这样的表，它正确吗？

以及如何查询它们以便通过 json_encode 结果获得上述结果？

Answer 1

首先，您需要修复您的 table 。它非常不规则，文本值在任何语言中都无法很好地匹配。尝试这样的表:

------------------------------
| id | parent | desc         |
------------------------------
| 1  | 0      | parent 1     |
------------------------------
| 2  | 0      | parent 2     |
------------------------------
| 3  | 0      | parent 1     |
------------------------------
| 4  | 0      | parent 2     |
------------------------------
| 5  | 0      | parent 1     |
------------------------------
| 6  | 1      | child 1      |
------------------------------
| 7  | 1      | child 2      |
------------------------------
| 8  | 6      | Grandchild 1 |
------------------------------
| 9  | 6      | Grandchild 2 |
------------------------------

id 和parent 应该是整数字段，desc 可以是text/varchar。根或顶级项目的父值应为零 (0)。

然后您可以使用类似于以下的脚本:

$db = new mysqli('127.0.0.1', 'your_db_user', 'your_secure_password', 'your_db_schema');

if( $mysqli->connect_error ) {
    echo 'Something went wrong: ' . $mysqli->connect_error;
}

$json = [];

function sanitize_id($id) {
    return preg_replace( '/[^a-z0-9]/', '', strtolower($id) );
}

function recursive( $parentId, &$json ) {
    global $mysqli;

    if ($stmt = $mysqli->prepare("SELECT * FROM your-table WHERE parent = ?")) {

        // Bind parent id to query
        $stmt->bind_param('i', $parentId);

        $results = $stmt->execute();

        // Loop over results
        while ( $result = $results->fetch_assoc() ) {

            // Add result to JSON structure at referenced location
            $json[] = [
                'text' => $result['desc'],
                'href' => '#' . sanitize_id($result['desc']),
                'tags' => ['0'], // Are the tags another field?
                'nodes' => []
            ];

            // Rerun recusive function to check for and add any children
            recursive( $result['id'], $json['nodes'] );

        }

        $stmt->close();

    }

};

recursive(0, $json);

echo json_encode( $json );

$db->close();