Skip to content

Instantly share code, notes, and snippets.

@akki-io

akki-io/adjacencyToNestedJsonWithPerformanceMethodOneB9.php

Created May 10, 2019 22:21
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save akki-io/0d172bcc0f36b856708b7fa74c5d779f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save akki-io/0d172bcc0f36b856708b7fa74c5d779f to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. akki-io created this gist May 10, 2019.
    87 changes: 87 additions & 0 deletions adjacencyToNestedJsonWithPerformanceMethodOneB9.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,87 @@
    <?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;
    }