PHP Navigation with MySQL database

Question

i made a navigation where a MySQL Database is needed. This is my connection to the database to get all informations.

$stmt = $pdo->prepare("SELECT * FROM navigation");
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_OBJ);
if($stmt->rowCount() > 0){
    $primary_nav = [];
    foreach ($results as $result){
        if($result->sub == 0){
            $primary_nav[] = array(
                'name'  => $result->name,
                'url'   => $result->url,
                'icon'  => $result->icon,
            );
        }elseif($result->sub == 1){
            $primary_nav[] = array(
                'name'  => $result->name,
                'icon'  => $result->icon,
                'sub'   => array(
                    array(
                        'name'  => $result->name_sub,
                        'url'   => $result->url_sub
                    )
                )
            );
        }
    }
}

This works fine, if I add the navigation into the database everything looks perfect and works amazing. Now the problem i've now is when I want to a new sub menu than everytime I get a new top menu entrie with just 1 sub menu.

So my question is, how do I get this part working without breaking the code. Normally the code looks like this:

// first sub
array(
    'name'  => 'Test1',
    'icon'  => 'fa fa-bullhorn',
    'sub'   => array(
        array(
            'name'  => 'First Sub 1',
            'url'   => 'sub1.php'
        ),
        array(
            'name'  => 'First Sub 2',
            'url'   => 'sub2.php'
        )
    )
),
// second sub
array(
    'name'  => 'Test3',
    'icon'  => 'fa fa-bullhorn',
    'sub'   => array(
        array(
            'name'  => 'Second Sub',
            'url'   => 'sub1_1.php'
        )
    )
)

database structure:

|-----name-----|----url----|----icon----|----sub----|----name_sub----|----url_sub----|----category----|
|  Dashboard  | index.php |    icon    |     0     |                |               |                |
------------------------------------------------------------------------------------------------------
|    Test     | test.php  |    icon    |     0     |                |               |                |
------------------------------------------------------------------------------------------------------
|    Test1    |           |    icon    |     1     |    First Sub 1 |   sub1.php    |       1        |
------------------------------------------------------------------------------------------------------
|             |           |    icon    |     1     |    First Sub 2 |   sub2.php    |       1        |
------------------------------------------------------------------------------------------------------
|    Test3    |           |    icon    |     1     |    Second Sub  |  sub1_1.php   |       2        |
------------------------------------------------------------------------------------------------------**

So if the category equals the same number as the other it should be doing this:

Test1
-- First Sub 1
-- First Sub 2
Test3
-- Second Sub

but with my code it looks like this:

Test1
-- First Sub 1
Test2 (it would be empty because in the database it is empty just for example I puted Test2)
-- First Sub 2
Test3
-- Second Sub

maybe someone understand what I need, because my english is not the best to explain it. Thanks for any help/solution for this problem.

Answer 1

$stmt = $pdo->prepare("SELECT * FROM navigation");
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_OBJ);
if($stmt->rowCount() > 0){

    $categories = [];
    $primary_nav = [];

    foreach ($results as $result){

        if ($result->name) {

            if ($result->category) {
                $categories[$result->category] = sizeof($primary_nav);
            }

            $primary_nav[] = array(
                'name'  => $result->name,
                'url'   => $result->url,
                'icon'  => $result->icon,
            );

        }

        if ($result->name_sub) {
            $primary_nav[$categories[$result->category]]['sub'][] = array(
                'name'  => $result->name_sub,
                'url'   => $result->url_sub
            );
        }

    }
    
}

I've added an extra $categories array.

For each "parent" entry with a category, the $categories array stores the category value from the database and the key of the "parent" entry in the $primary_nav array. The $categories array can then be used to add subsequent subcategories to the correct parent entry using their category value.

In your current setup however, the database allows you to have subcategories without a parent category and (sub)categories without a name. So I would suggest using a table setup like this instead:

id      name        url         icon    parent

1       Dashboard   index.php   icon    null
2       Test        test.php    icon    null
3       Test1       null        icon    null
4       First sub 1 sub1.php    null    3
5       First sub 2 sub2.php    null    3
6       Test3       null        icon    null
7       Second sub  Sub1_1.php  null    6

Parent categories have the column "parent" set to null, and subcategories have their "parent" column set to the id of their parent entry. This also allows you to have sub-sub-(and so on)-categories.

You would need to query it recursively:

function buildNav($pdo, $id = null) {
    $array = [];
    
    if ($id) {
        $stmt = $pdo->prepare("SELECT * FROM navigation WHERE parent = :id");
        $stmt->bindValue('id', $id);
    } else {
        $stmt = $pdo->prepare("SELECT * FROM navigation WHERE parent IS NULL");
    }
    
    $stmt->execute();
    $results = $stmt->fetchAll(PDO::FETCH_OBJ);
    
    if ($stmt->rowCount() > 0){
        foreach ($results as $result){
            $array[] = array(
                'name'  => $result->name,
                'url'   => $result->url,
                'icon'  => $result->icon,
                'sub'   => buildNav($pdo, $result->id)
            );
        }
    }
    
    return $array;
}

$primary_nav = buildNav($pdo);