Skip to content

Instantly share code, notes, and snippets.

@akki-io

akki-io/adjacencyToNestedJsonWithPerformanceMethodTwoB9.php

Created May 10, 2019 22:22
Show Gist options

  • Select an option

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

Select an option

Learn more about clone URLs
Save akki-io/fd4b75737caaae05fe3fb6e04e2dc801 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 adjacencyToNestedJsonWithPerformanceMethodTwoB9.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();

    // 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;
    }