$sql = "SELECT test.tree_data.id, nm AS 'text', pid FROM test.tree_data INNER JOIN test.tree_struct ON test.tree_data.id = test.tree_struct.id; "; $res = mysqli_query($conn, $sql) or die("database error:" . mysqli_error($conn)); //iterate on results row and create new index array of data while ($row = mysqli_fetch_assoc($res)) { $data[] = $row; } $itemsByReference = array(); // Build array of item references: foreach ($data as $key => &$item) { $itemsByReference[$item['id']] = &$item; // Children array: $itemsByReference[$item['id']]['children'] = array(); // Empty data class (so that json_encode adds "data: {}" ) $itemsByReference[$item['id']]['data'] = new StdClass(); } // Set items as children of the relevant parent item. foreach ($data as $key => &$item) if ($item['pid'] && isset($itemsByReference[$item['pid']])) $itemsByReference [$item['pid']]['children'][] = &$item; // Remove items that were added to parents elsewhere: foreach ($data as $key => &$item) { if ($item['pid'] && isset($itemsByReference[$item['pid']])) unset($data[$key]); } // Encode: echo json_encode($data);
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter