Pages with level

Question

Hello guys, sorry if you'll find this question stupid, but I really need help. Here's some info about it.

Database structure

---

id | parent_id | level | name

1 | 0 | 1 | Home page

2 | 1 | 2 | Child of homepage

3 | 1 | 2 | Another child of homepage

4 | 2 | 3 | Sub child of page id 2

5 | 3 | 3 | Sub child of page id 3

---

Question

---

How do I make the SQL statement to group my rows by level and make this output.

Desired output

---

Home page

~~Child of homepage

~~~~Sub child of page id 2

~~Another child of homepage

~~~~Sub child of page id 3

I know how to do this with a recursion method, but I want to know if I can accomplish this with one SQL statement. Thanks.

Answer 1

Well, you could order the data by parent_id so that it is sorted correctly:

$menu = mysql_query("SELECT * FROM menu ORDER BY parent_id, level, name");

Then, use the level to determine your indent:

while ($row = mysql_fetch_object($menu)) {
  echo str_repeat('~~', $row->level).' '.$row->name.'<br />';
}

Say you want them in nested lists or some other element (rather than indented by a prefix such as '~~'):

$level = 0;
echo '<ul>';
while ($row = mysql_fetch_object($menu)) {
  if ($row->level > $level) {
    echo '<ul>';
  } else if ($row->level < $level) {
    echo '</ul>';
  }
  echo '<li> '.$row->name.'</li>';
  $level = $row->level;
}
for ($i = $level; $i >= 0; $i--) {
  echo '</ul>';
}

Answer 2

I often recommend this article on Storing Hierarchical Data in a Database for questions like this.

The answer to your question is yes, you can get it in just one query. Your method (recursive) is discussed on the first page. Check out page two for the alternative solution, which is the Modified Preorder Tree Traversal (MPTT) design. Using that set-up, you can run a single query and retrieve all the children of a given node of your structure. It's very similar to what you have now, but not quite the same, and will require a bit of work to implement.

For a read-heavy application however, the MPTT is a great algorithm. It requires a bit more work when doing writes, since you have to modify the entire tree structure, but from what you've got set up there I think it might work very well for you.

Answer 3

It can be done using single query which will return the result exactly as you've described. There's a great tutorial on hierarchical data on mysql site.

You can go straight to "Nested Set Model", but I suggest reading the whole tutorial.

Here's the link: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html