Nested fetch_assocs help

[Xaero]

Hey guys, I'm fairly new to PHP and MySql, I've come quite far but still obviously missing something here. Basically I've grabbed a query using the standard mysqli command

$db->query("SELECT * FROM categories")

Then I run a big nested while loop for linked categories to output as nested unordered lists.

 

The issue is currently I only get the first parent category and the first set of each of the respective siblings to output.

 

I have the following sample data to test this out

id	parent	name	
1	0	Wetsuits
2	1	Summer
3	1	Shortie
4	1	Youth
5	2	Mens
6	2	Womens
7	3	Mens
8	3	Womens
9	0	Wetsuit Accessories
10	9	Boots
11	9	Shoes
12	9	Rash Vests
13	9	Wetsuit Shorts
14	9	Hoods
15	9	Gloves
16	9	Boardshorts

Basically my root categories are parent 0, then all other categories branch from those with parent id assignments.

 

The code I'm currently using to iterate through these results and output them as ULs is

<ul>
<?php
while ($cat = $categories->fetch_assoc()) {
  if ($cat['parent'] == 0) {
    echo "<li>" . $cat['name'] . "</li>";
    echo "<ul>";
    while ($subcat = $categories->fetch_assoc()) {
      if ($subcat['parent'] == $cat['id']) {
        echo "<li>" . $subcat['name'] . "</li>";
        echo "<ul>";
        while ($subsubcat = $categories->fetch_assoc()) {
          if ($subsubcat['parent'] == $subcat['id']) {
            echo "<li>" . $subsubcat['name'] . "</li>";
          }
        }
        echo "</ul>";
      }
    }
    echo "</ul>";
  }
}
?>
</ul>

This only outputs the following:

• Wetsuits
  • Summer
    • Mens
    • Womens

So I'm currently trying to figure out why and coming up empty.

 

Any suggestions would be greatly appreciated!

[Xaero]

Ok so I got it working, realised the pointer for the query doesn't reset so it was only getting what it could on the way down the list.

 

For anyone interested, I solved the problem by converting to an array and then converting my while loops above into for loops, then iterating through the array like this:

while ($row = $categories->fetch_array(MYSQLI_ASSOC)) {
  $category[] = $row;
}

echo "<ul>";
for ($cat=0; $cat < count($category); $cat++) { 
  if ($category[$cat]['parent'] == 0) {
    echo "<li>" . $category[$cat]['name'] . "</li>";
    echo "<ul>";
    for ($subcat=0; $subcat < count($category); $subcat++){
      if ($category[$subcat]['parent'] == $category[$cat]['id']) {
        echo "<li>" . $category[$subcat]['name'] . "</li>";
        echo "<ul>";
        for ($subsubcat=0; $subsubcat < count($category); $subsubcat++){
          if ($category[$subsubcat]['parent'] == $category[$subcat]['id']) {
            echo "<li>" . $category[$subsubcat]['name'] . "</li>";
          }
        }
        echo "</ul>";
      }
    }
    echo "</ul>";
  }
}
echo "</ul>";