build recursive navigation menu php

Question

To build dynamic navigation menu, I've run the query

select `category_id`,`category_name`,`parent_category_id`,`sort_order` from `item_category` 

and I get the result

Array
(
    [0] => Array
        (
            [category_id] => 9
            [category_name] => cat1
            [parent_category_id] => 0
            [sort_order] => 0
        )

    [1] => Array
        (
            [category_id] => 11
            [category_name] => cat3
            [parent_category_id] => 0
            [sort_order] => 0
        )

    [2] => Array
        (
            [category_id] => 15
            [category_name] => cat7
            [parent_category_id] => 10
            [sort_order] => 0
        )

    [3] => Array
        (
            [category_id] => 17
            [category_name] => cat9
            [parent_category_id] => 10
            [sort_order] => 1
        )

    [4] => Array
        (
            [category_id] => 13
            [category_name] => cat5
            [parent_category_id] => 11
            [sort_order] => 0
        )

    [5] => Array
        (
            [category_id] => 16
            [category_name] => cat8
            [parent_category_id] => 15
            [sort_order] => 0
        )

    [6] => Array
        (
            [category_id] => 10
            [category_name] => cat2
            [parent_category_id] => 9
            [sort_order] => 1
        )

    [7] => Array
        (
            [category_id] => 12
            [category_name] => test5
            [parent_category_id] => 9
            [sort_order] => 0
        )

)

I tried to build navigation bar out of it by

function create_array($number, $data)
{
    $result = array();
    foreach ($data as $row)
    {
        if ($row['parent_category_id'] == $number)
        {
            $result[$row['category_id']] = create_array($row['category_id'], $data);

        }
    }
    return $result;
}
$menu_array=create_array(0, $all_categories);
echo "menu_array<pre>"; print_r($menu_array); echo "</pre>";

It has perfectly build menu like this

Array
(
    [9] => Array
        (
            [10] => Array
                (
                    [15] => Array
                        (
                            [16] => Array
                                (
                                )

                        )

                    [17] => Array
                        (
                        )

                )

            [12] => Array
                (
                )

        )

    [11] => Array
        (
            [13] => Array
                (
                )

        )

)

My problem now is I don't understand how to do the sorting in create_array function using sort_order and also retrieve how to retrieve category_name from through create_array function recursion.

EDIT: Thanks @user3091574 I was sorting by parent_category_id earlier for some other reason, for now to make it simple taking your suggestion, now i want to retrieve category_name and build <ul> out of it! any suggestions?

EDIT-2 I found solution to build navigation menu at https://stackoverflow.com/a/3380296/1528701

Answer 1

for sorting:

add order by sort_order DESC in your query

For adding catogary

function create_array($number, $data)
{
    $result = array();

    foreach ($data as $row)
    {
        if ($row['parent_category_id'] == $number)
        {
            $result[$row['category_id']]['category_name']=$row['category_name'];
            $result[$row['category_id']]['id']= create_array($row['category_id'], $data);

        }
    }
    return $result;
}