Created
May 10, 2019 22:21
-
-
Save akki-io/0d172bcc0f36b856708b7fa74c5d779f 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(); | |
| // Transform the data | |
| $outputTree = transformTree($treeArray, [ID_OF_ENTITY]); | |
| // Output the response | |
| header('Content-Type: application/json'); | |
| echo json_encode($outputTree[0]); | |
| /** | |
| * Transform the tree | |
| * | |
| * @param $treeArray | |
| * @param null $parentId | |
| * @return array | |
| */ | |
| function transformTree($treeArray, $parentId = null) | |
| { | |
| $output = []; | |
| // Read through all nodes of the tree | |
| foreach ($treeArray as $node) { | |
| // If the node parent is same as parent passed in argument | |
| if ($node['parent_id'] == $parentId) { | |
| // Get all the children for that node, using recursive method | |
| $children = transformTree($treeArray, $node['id']); | |
| // If children are found, add it to the node children array | |
| if ($children) { | |
| $node['children'] = $children; | |
| } | |
| // Add the main node with/without children to the main output | |
| $output[] = $node; | |
| // Remove the node from main array to avoid duplicate reading, speed up the process | |
| unset($node); | |
| } | |
| } | |
| return $output; | |
| } | |
| /** | |
| * 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