Created
May 10, 2019 22:22
-
-
Save akki-io/fd4b75737caaae05fe3fb6e04e2dc801 to your computer and use it in GitHub Desktop.
PHP – Performance – Adjacency set to nested JSON - https://blog.tekz.io
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| // Initialize php setting - @node - NOT RECOMMENDED FOR PRODUCTION, update your php.ini if needed. | |
| error_reporting(E_ALL); | |
| ini_set('display_errors', 1); | |
| ini_set('memory_limit', '-1'); | |
| ini_set('max_execution_time', 30000); | |
| // Get the tree array from DB | |
| $treeArray = getDataFromDatabase(); | |
| // Group by parent id | |
| $treeArrayGroups = []; | |
| foreach ($treeArray as $record) { | |
| $treeArrayGroups[$record['parent_id']][] = $record; | |
| } | |
| // Get the root | |
| $rootArray = $treeArray[0]; | |
| // Transform the data | |
| $outputTree = transformTree($treeArrayGroups, $rootArray); | |
| // Output the response | |
| header('Content-Type: application/json'); | |
| echo json_encode($outputTree); | |
| /** | |
| * Transform the tree | |
| * | |
| * @param $treeArrayGroups | |
| * @param $rootArray | |
| * @return mixed | |
| */ | |
| function transformTree($treeArrayGroups, $rootArray) | |
| { | |
| // Read through all nodes where parent is root array | |
| foreach ($treeArrayGroups[$rootArray['id']] as $child) { | |
| // If there is a group for that child, aka the child has children | |
| if (isset($treeArrayGroups[$child['id']])) { | |
| // Traverse into the child | |
| $newChild = transformTree($treeArrayGroups, $child); | |
| } else { | |
| $newChild = $child; | |
| } | |
| // Assign the child to the array of children in the root node | |
| $rootArray['children'][] = $newChild; | |
| } | |
| return $rootArray; | |
| } | |
| /** | |
| * Get all records from DB and return array | |
| * | |
| * @return array | |
| */ | |
| function getDataFromDatabase() | |
| { | |
| // Create database connection | |
| $dbConnection = new mysqli("localhost", "root", "secret", "adjacency"); | |
| // Get the result from DB Table | |
| $records = $dbConnection->query(" | |
| WITH RECURSIVE tree AS | |
| ( | |
| SELECT id, name, parent_id | |
| FROM entities | |
| WHERE id = [ID_OF_ENTITY] | |
| UNION ALL | |
| SELECT e.id, e.name, e.parent_id | |
| FROM tree AS t | |
| JOIN entities AS e ON t.id = e.parent_id | |
| ) | |
| SELECT id, name, parent_id FROM tree; | |
| "); | |
| // Fetch all records | |
| // @MYSQLI_ASSOC - Columns are returned into the array having the field name as the array index. | |
| $output = mysqli_fetch_all($records, MYSQLI_ASSOC); | |
| // Close the connection | |
| $dbConnection->close(); | |
| return $output; | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment