How can I format the entire tree recursively of PHP Nested Category array output?

Question

I have a nested model category tree in array format as follows ....

$data = [
  [
    'categoryId' => '08adf337-a577-4038-86a6-a5cd16676dff',
    'name' => 'ELECTRONICS',
    'parentId' => 0,
    'lft' => 1,
    'rgt' => 20,
    'level' => 0,
  ],
  [
    'categoryId' => '20150966-bbc5-438f-b3eb-1ff0bbc71b8b',
    'name' => 'TELEVISIONS',
    'parentId' => '08adf337-a577-4038-86a6-a5cd16676dff',
    'lft' => 2,
    'rgt' => 9,
    'level' => 1,
  ],
  [
    'categoryId' => '3fb68583-b9f3-4996-86fa-897c825767e4',
    'name' => 'TUBE',
    'parentId' => '20150966-bbc5-438f-b3eb-1ff0bbc71b8b',
    'lft' => 3,
    'rgt' => 4,
    'level' => 2,
  ],
  [
    'categoryId' => 'bca4d77f-9f6f-41da-ad66-e747edcb9482',
    'name' => 'LCD',
    'parentId' => '20150966-bbc5-438f-b3eb-1ff0bbc71b8b',
    'lft' => 5,
    'rgt' => 6,
    'level' => 2,
  ],
  [
    'categoryId' => '9726c8ff-7664-4494-bb2f-3b365e7a6743',
    'name' => 'PLASMA',
    'parentId' => '20150966-bbc5-438f-b3eb-1ff0bbc71b8b',
    'lft' => 7,
    'rgt' => 8,
    'level' => 2,
  ],
  [
    'categoryId' => '576a2266-9f11-4430-b81f-2ac2a536ad9a',
    'name' => 'PORTABLE ELECTRONICS',
    'parentId' => '08adf337-a577-4038-86a6-a5cd16676dff',
    'lft' => 10,
    'rgt' => 19,
    'level' => 1,
  ],
  [
    'categoryId' => '062921a4-e9af-48f6-adba-d623ca2de4ae',
    'name' => 'MP3 PLAYERS',
    'parentId' => '576a2266-9f11-4430-b81f-2ac2a536ad9a',
    'lft' => 11,
    'rgt' => 14,
    'level' => 2,
  ],
  [
    'categoryId' => '3a80ae89-5eba-4407-819c-944e1d360ed9',
    'name' => 'FLASH',
    'parentId' => '062921a4-e9af-48f6-adba-d623ca2de4ae',
    'lft' => 12,
    'rgt' => 13,
    'level' => 3,
  ],
  [
    'categoryId' => '0a03f267-8646-4460-bce7-dc6993e337a3',
    'name' => 'CD PLAYERS',
    'parentId' => '576a2266-9f11-4430-b81f-2ac2a536ad9a',
    'lft' => 15,
    'rgt' => 16,
    'level' => 2,
  ],
  [
    'categoryId' => 'fc9a25d0-a67a-4753-aed8-c148c9c95573',
    'name' => '2 WAY RADIOS',
    'parentId' => '576a2266-9f11-4430-b81f-2ac2a536ad9a',
    'lft' => 17,
    'rgt' => 18,
    'level' => 2,
  ],
];

.. and using PHP, I need to convert the format I obtained from the mysql output above into the following format:

The response that i need to:

$result = array();
$result['items'] = [
    [
      id =>  08adf337-a577-4038-86a6-a5cd16676dff,
      title => 'ELECTRONICS',
      children =>  [
        [ id: 2, title: 'Child #1' ],
        [ id: 3, title: 'Child #2' ],
        [
          id: 4,
          title: 'Child #3',
          children: [
            [ id: 5, title: 'Grandchild #1' ],
            [ id: 6, title: 'Grandchild #2' ],
          ],
        ],
      ],
    ]
];

For better understanding, I added my mysql table below visually.

Here is my mysql table:

------------------------
MySQL categories table
------------------------

categoryId                              parentId                                name                    lft rgt level
-------------------------------------------------------------------------------------------------------------------
08adf337-a577-4038-86a6-a5cd16676dff    0                                       ELECTRONICS             1   20  0
20150966-bbc5-438f-b3eb-1ff0bbc71b8b    08adf337-a577-4038-86a6-a5cd16676dff    TELEVISIONS             2   9   1
3fb68583-b9f3-4996-86fa-897c825767e4    20150966-bbc5-438f-b3eb-1ff0bbc71b8b    TUBE                    3   4   2
bca4d77f-9f6f-41da-ad66-e747edcb9482    20150966-bbc5-438f-b3eb-1ff0bbc71b8b    LCD                     5   6   2
9726c8ff-7664-4494-bb2f-3b365e7a6743    20150966-bbc5-438f-b3eb-1ff0bbc71b8b    PLASMA                  7   8   2
576a2266-9f11-4430-b81f-2ac2a536ad9a    08adf337-a577-4038-86a6-a5cd16676dff    PORTABLE ELECTRONICS    10  19  1
062921a4-e9af-48f6-adba-d623ca2de4ae    576a2266-9f11-4430-b81f-2ac2a536ad9a    MP3 PLAYERS             11  14  2
3a80ae89-5eba-4407-819c-944e1d360ed9    062921a4-e9af-48f6-adba-d623ca2de4ae    FLASH                   12  13  3
0a03f267-8646-4460-bce7-dc6993e337a3    576a2266-9f11-4430-b81f-2ac2a536ad9a    CD PLAYERS              15  16  2
fc9a25d0-a67a-4753-aed8-c148c9c95573    576a2266-9f11-4430-b81f-2ac2a536ad9a    2 WAY RADIOS            17  18  2

I've included the full output of the sql code below if you need to add it to your database.

Here is the full sql code:

------------------------
MySQL categories.sql
------------------------

SET NAMES utf8mb4;

DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories`  (
  `categoryId` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `parentId` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `lft` int NOT NULL,
  `rgt` int NOT NULL,
  `level` int NULL DEFAULT NULL,
  PRIMARY KEY (`categoryId`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `categories` VALUES ('062921a4-e9af-48f6-adba-d623ca2de4ae', '576a2266-9f11-4430-b81f-2ac2a536ad9a', 'MP3 PLAYERS', 11, 14, 2);
INSERT INTO `categories` VALUES ('08adf337-a577-4038-86a6-a5cd16676dff', '0', 'ELECTRONICS', 1, 20, 0);
INSERT INTO `categories` VALUES ('0a03f267-8646-4460-bce7-dc6993e337a3', '576a2266-9f11-4430-b81f-2ac2a536ad9a', 'CD PLAYERS', 15, 16, 2);
INSERT INTO `categories` VALUES ('20150966-bbc5-438f-b3eb-1ff0bbc71b8b', '08adf337-a577-4038-86a6-a5cd16676dff', 'TELEVISIONS', 2, 9, 1);
INSERT INTO `categories` VALUES ('3a80ae89-5eba-4407-819c-944e1d360ed9', '062921a4-e9af-48f6-adba-d623ca2de4ae', 'FLASH', 12, 13, 3);
INSERT INTO `categories` VALUES ('3fb68583-b9f3-4996-86fa-897c825767e4', '20150966-bbc5-438f-b3eb-1ff0bbc71b8b', 'TUBE', 3, 4, 2);
INSERT INTO `categories` VALUES ('576a2266-9f11-4430-b81f-2ac2a536ad9a', '08adf337-a577-4038-86a6-a5cd16676dff', 'PORTABLE ELECTRONICS', 10, 19, 1);
INSERT INTO `categories` VALUES ('9726c8ff-7664-4494-bb2f-3b365e7a6743', '20150966-bbc5-438f-b3eb-1ff0bbc71b8b', 'PLASMA', 7, 8, 2);
INSERT INTO `categories` VALUES ('bca4d77f-9f6f-41da-ad66-e747edcb9482', '20150966-bbc5-438f-b3eb-1ff0bbc71b8b', 'LCD', 5, 6, 2);
INSERT INTO `categories` VALUES ('fc9a25d0-a67a-4753-aed8-c148c9c95573', '576a2266-9f11-4430-b81f-2ac2a536ad9a', '2 WAY RADIOS', 17, 18, 2);

After running the sql code you may need to get the entire category tree:

The sql code which is fetch all tree.

SELECT c.name, c.parentId, c.lft, c.rgt, c.level 
            FROM
            categories c
            WHERE
            c.lft BETWEEN c.lft AND c.rgt 
            GROUP BY
            c.name 
            ORDER BY
            c.lft;

Sample output of above the code:

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
+-----------------------+

Reference: https://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Ok. So have you tried anything? Have you researched anything? Where have you got stuck? "I need" isn't a question or problem statement. Reminder: How to Ask. — ADyson
– ADyson, Commented Sep 27, 2024 at 8:37

Wimanicesir · Accepted Answer · 2024-09-27 08:37:14Z

0

You can just map the original response you already have from SQL to the format you want. You could do it with a recursive function like below.

function buildTree($items, $parentId = 0) {
 $branch = [];

 foreach ($items as $item) {
    if ($item['parentId'] == $parentId) {
        $children = buildTree($items, $item['categoryId']);
        $node = [
            'id' => $item['categoryId'],
            'title' => $item['name']
        ];
        
        if (!empty($children)) {
            $node['children'] = $children;
        }
        $branch[] = $node;
    }
  }

 return $branch;

}

answered Sep 27, 2024 at 8:37

Wimanicesir

5,1492 gold badges14 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I format the entire tree recursively of PHP Nested Category array output?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related