JOIN table with itself and show on screen

Question

This is my table

row_id | category   | parent_id
1      | News       | 0
2      | Toys       | 0
3      | national   | 1
4      | local      | 1

The rows with parent_id = 0 are categories the ones with a parent_id != 0 are subcategories

this is how I want them displaying on my screen with PHP on my website

+news
 - national
 - local
+toys
 - ...
 - ...
+ ...

because I can't get it to work with PHP as well,

Answer 1

Since OP does not mention whether the menu needs to be printed immediately or returning a string of HTML, this is how I will do it with just one SQL query and handling it with PHP recursive function.

Please note that there are many ways to do this, I'm just showing you one of that many ways.

<?php
// Replace this with your MySQL result
$data = [
    [
        'row_id' => 1,
        'category' => 'News',
        'parent_id' => 0,
    ],
    [
        'row_id' => 2,
        'category' => 'Toys',
        'parent_id' => 0,
    ],
    [
        'row_id' => 3,
        'category' => 'national',
        'parent_id' => 1,
    ],
    [
        'row_id' => 4,
        'category' => 'local',
        'parent_id' => 1,
    ],
];

/**
 * Print the menu recursively
 */
function printRecursive($currentMenuItem, $currentParentID = 0, $menuListInParentIDs) {

    if($currentMenuItem['parent_id'] == $currentParentID) {
        echo "<li>{$currentMenuItem['category']}</li>";

        if(isset($menuListInParentIDs[$currentMenuItem['row_id']])) {
            $children = $menuListInParentIDs[$currentMenuItem['row_id']];

            echo "<ul>";

            foreach($children as $childMenuItem) {
                printRecursive($childMenuItem, $childMenuItem['parent_id'], $menuListInParentIDs);
            }

            echo "</ul>";
        }
    }
}

// First let's group the menu with parent ID as it's key to make it easier to loop
$dataToKey = [];
foreach($data as $item) {
    if(!isset($dataToKey[$item['parent_id']])) {
        $dataToKey[$item['parent_id']] = [];
    }
    $dataToKey[$item['parent_id']][] = $item;
}

// Now let's render
echo "<ul>";
foreach($data as $item) {
    printRecursive($item, 0, $dataToKey);
}
echo "</ul>";

Here comes the explanation:

The way I approach this is by grouping the list of menu items into parent_id list.

If you try to print_r($dataToKey), it will show the following:

Array
(
    [0] => Array
        (
            [0] => Array
                (
                    [row_id] => 1
                    [category] => News
                    [parent_id] => 0
                )

            [1] => Array
                (
                    [row_id] => 2
                    [category] => Toys
                    [parent_id] => 0
                )

        )

    [1] => Array
        (
            [0] => Array
                (
                    [row_id] => 3
                    [category] => national
                    [parent_id] => 1
                )

            [1] => Array
                (
                    [row_id] => 4
                    [category] => local
                    [parent_id] => 1
                )

        )

)

By doing this, you know which menu items belong to parent_id 0 and which one goes to 1, and so on and so on.

Why do I do this?

I'm doing this so our recursive function does not waste time looping the data source over and over again.

Now we have this helper variable, we can start performing recursive function.

This is our recursive function

/**
 * Print the menu recursively
 */
function printRecursive($currentMenuItem, $currentParentID = 0, $menuListInParentIDs) {

    if($currentMenuItem['parent_id'] == $currentParentID) {
        echo "<li>{$currentMenuItem['category']}</li>";

        if(isset($menuListInParentIDs[$currentMenuItem['row_id']])) {
            $children = $menuListInParentIDs[$currentMenuItem['row_id']];

            echo "<ul>";

            foreach($children as $childMenuItem) {
                printRecursive($childMenuItem, $childMenuItem['parent_id'], $menuListInParentIDs);
            }

            echo "</ul>";
        }
    }
}

What this function does is it takes the current menu item, check if this particular menu item is part of the given parent ID $currentParentID. If it is, prints it, otherwise don't do anything.

After printing, it then checks if there are any other menu items that are the children of the current menu by using the variable that we created before which comes from $dataToKey.

If there is at least one child, it calls the same recursive function to print the current child menu item and then check if it has any grandchildren, and so on and so on.

Now let's print it

We start it by calling the following snippet.

// Now let's render
echo "<ul>";
foreach($data as $item) {
    printRecursive($item, 0, $dataToKey);
}
echo "</ul>";

You might notice that the second argument has 0 in it, because we want to print from the very top. If you want to print from parent_id 1, you just need to change it to 1.

---

I hope this makes sense for you because it can be quite challenging to understand how recursive function works.

Is there any other way?

There is! There are different ways to do this. And it's your challenge to discover different ways to achieve it. Think of it as a good exercise.

Is this the best way of doing recursive function?

Probably not, I believe other people can come up with a better way of doing it than me. But if it works and does the job, it's good until you need to optimize it.