Best way to transform tag list into structured array from MySQL?

Question

My database is storing a list like this:

ID | name
--- -----
1  | search
2  | search.categories
3  | search.categories.accessories
4  | search.categories.accessories.glasses
5  | search.categories.accessories.hats
6  | filters
7  | filters.payments
8  | filters.payments.creditcard
9  | filters.payments.debitcard

Now I want to SELECT it in a recursivelly array just like this:

[
    name, childs[
        name, childs[ ... ],
        name, childs[ ... ],
        name, childs[
            name, childs[ ... ],
            name, childs[ ... ],
        ],
    ],
    name, childs[
        name, childs[ ... ],
        name, childs[ ... ],
        name, childs[
            name, childs[ ... ],
            name, childs[ ... ],
        ],
    ]
]

What is the best way to achive this? Directly from MySQL or manually string handling?

You will have to use php to parse the strings and add them to an array. There isn't built in functionality to this either, you will have to make something. There are questions that talk about how to assign a value into an array using "dot notation" like this one that should help get you started. Also, storing multiple values in a single string field like this is (grandparent.parent.child.etc) usually considered bad practice. — Jonathan
– Jonathan, Commented Aug 9, 2018 at 21:14
I wrote an example PHP function to do something similar, but with adjacency-list data instead of path-enumeration data like yours. Anyway, it might serve as an example to get your started. stackoverflow.com/questions/3261228/… — Bill Karwin
– Bill Karwin, Commented Aug 9, 2018 at 22:36

alariva · Accepted Answer · 2018-08-10 04:59:13Z

1

If you are working with Laravel you can build a nested array by using array_set and using as key the name column of your table, taking advantage that you are already storing on dot notation.

Despite you are not exposing what's the purpose of this and why you want to build this structure this way, follows one possible approach.

Initialize an empty array ($structure = [])
Retrieve all table records an iterate them (use a DB select or model get as you prefer)
For each table row, perform an array_set($structure, $listname, $value)

In your example, I don't see where $value could be coming from, but I guess you will grasp the idea.

$structure = [];
foreach($rows as $row)
{
    array_set($structure, $row->name, $value);
}

answered Aug 10, 2018 at 4:59

alariva

2,1471 gold badge25 silver badges38 bronze badges

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

Collectives™ on Stack Overflow

Best way to transform tag list into structured array from MySQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related