CODEWITHSUNDEEP
phpmysqlgroup-byparent-child
SoulieBaby
SoulieBaby

Reputation: 5471

mysql query help needed - Group by parent

I'm trying to do a mysql query that will create an array of parent items, with their child items underneath. But I'm not 100% sure how. Here's what I have done so far:

SELECT * FROM categories as rf ORDER BY parent, name ASC

And here's what is being outputted (array):

Array
(
    [0] => stdClass Object
        (
            [id] => 7
            [name] => Safety Clothing
            [parent] => 0
        )

    [1] => stdClass Object
        (
            [id] => 8
            [name] => Safety Footwear
            [parent] => 0
        )

    [2] => stdClass Object
        (
            [id] => 9
            [name] => Workwear
            [parent] => 0
        )

    [3] => stdClass Object
        (
            [id] => 4
            [name] => Polos
            [parent] => 7
        )

    [4] => stdClass Object
        (
            [id] => 3
            [name] => Shirts
            [parent] => 7
        )

    [5] => stdClass Object
        (
            [id] => 6
            [name] => Jackets
            [parent] => 9
        )

    [6] => stdClass Object
        (
            [id] => 1
            [name] => Pants
            [parent] => 9
        )

    [7] => stdClass Object
        (
            [id] => 2
            [name] => Shirts
            [parent] => 9
        )

    [8] => stdClass Object
        (
            [id] => 5
            [name] => Shorts
            [parent] => 9
        )

)

As you can see the child items have the id of the parent items (parent is set to 0), but I'm not sure how to merge it all together to do an array something like this:

parent

-- child

-- child

parent

parent

-- child

-- child

-- child

Any help would be appreciated :)

Upvotes: 0

Views: 1149

Answers (2)

Antti Rytsölä
Antti Rytsölä

Reputation: 1545

Also

SELECT parent,GROUP_CONCAT(name) as names FROM categories as rf GROUP BY 1;

You will get one row for every parent with parent id in the first column and child names separated by comma on the second column.

Upvotes: 2

Lee Louviere
Lee Louviere

Reputation: 5262

If it is possible to build nested arrays using the data from that array: Each object would have an Array variable. Then after the initial array is built, move the children under their parent manually by parsing the initial array.

The method that parses would be recursive, and would take the initial array and the child array it is currently building.

Upvotes: 1

Related Questions