Very stuck on this simple PHP/MySQL ouptut

I have two tables, tasks and tasks_categories and there’s a foreign key linking the two called categoryID in each table.

In the tasks table, there are tasks and subtasks (subtasks have a parentID back to the taskID). Easy stuff so far.

Where I’m stuck is putting this into a tree with the category at the top as a header. Whenever I try to add the categoryName field into my query or PHP, it gets listed over and over on the page instead of just once at the top.

Here’s my code for building the tree, which works like a charm. I just want the category at the top, then the tasks for that category. Then next category, then those tasks. Simple enough but I can’t do it!


$query = mysqli_query($mysqli,'SELECT tasks.taskID,
									tasks.taskName,
									tasks.parentID,
									tasks.categoryID,
									tasks.required,
									tasks_categories.categoryID,
									tasks_categories.categoryName
									FROM tasks
									JOIN tasks_categories ON tasks.categoryID=tasks_categories.categoryID
									ORDER by tasks_categories.categoryID ASC');

while ($row = mysqli_fetch_assoc($query)) {
    $menu_array[$row['taskID']] = array(
    	'taskName' => $row['taskName'],
    	'parentID' => $row['parentID'],
    	'categoryID' => $row['categoryID'],
    	'categoryName' => $row['categoryName'],
    	'required' => $row['required']
    	);
}

function generate_menu($parentID)
{
    global $menu_array;
    $has_child = false;

    foreach($menu_array as $key => $value) {
	        
	if ($value['parentID'] == $parentID)  {
		
            if ($has_child === false) {
                $has_child = true;
                echo '<ul>';
            }
            echo '<li>' . $value['taskName'];
            if ($value['required'] == 1) {
            	echo '<span class="required">*</span>';
            }
            generate_menu($key);
            echo '</li>';
        }
    }
    if ($has_child === true) echo '</ul>';
}

generate_menu(0);

I think you can try to modify your function as following:

function generate_menu($parentID)
{
    global $menu_array;
    $has_child = false;

    $count=1;
    foreach($menu_array as $key => $value) {
	        
	if ($value['parentID'] == $parentID)  {
            if ($count==1) {
                echo $value['categoryName'];
            }		
            if ($has_child === false) {
                $has_child = true;
                echo '<ul>';
            }
            echo '<li>' . $value['taskName'];
            if ($value['required'] == 1) {
            	echo '<span class="required">*</span>';
            }
            generate_menu($key);
            echo '</li>';
        }
        $count++;
    }
    if ($has_child === true) echo '</ul>';
}

That puts the first category name at the top, then lists the tree out in full, without listing any other category names.

i’m sorry guy, you are right, that’s my bad. Please try the following:


function generate_menu($parentID)
{
    global $menu_array;
    $has_child = false;

    $previous_category_id = 0;
    foreach($menu_array as $key => $value) {
	        
	if ($value['parentID'] == $parentID)  {
            if($previous_category_id!=$value['categoryID']) {
                echo $value['categoryName'];
                $previous_category_id = $value['categoryID'];
            }
            if ($has_child === false) {
                $has_child = true;
                echo '<ul>';
            }
            echo '<li>' . $value['taskName'];
            if ($value['required'] == 1) {
            	echo '<span class="required">*</span>';
            }
            generate_menu($key);
            echo '</li>';
        }
    }
    if ($has_child === true) echo '</ul>';
}

This one should work I think. :slight_smile:

Close! That one puts the category name at the top of every tree. So, it starts with the correct category. Then, if there’s a subcategory, it relists the category name.

I’ll play around with it. Thanks!

To prevent it relist the category if there is subcategory you just need to add parentID=0 to condition. The above code I wrote from what I think, I haven’t run it yet. :slight_smile:

Sorry, I don’t understand.

something like this, change the following:

if($previous_category_id!=$value['categoryID'])

to:

if($previous_category_id!=$value['categoryID'] && $parentID==0)