在一个SQL查询中向后调度父项？

Question

I have a MySQL database (created by Wordpress) and this is similar to what it looks like:

ID    parentID     otherStuff
54    55           this is a test
55    56           another test
56    0            last test

What I need to do is to check how deep down a page is. I know that when it reaches parentID 0 it's finished.

I could write 3 querys and then check when is equal to 0, but it would be more nice if it's possible with only 1 query. Is it possible? How?

Here is an example:

• ID: 56 has parent 0 and has DEPTH 0. (now 1 query)
• ID: 55 has parent 56 then 0 and has DEPTH 1. (now 2 querys)
• ID: 54 has parent 55 then 56 then 0 and has DEPTH 2. (now 3 querys)

I have solved it "the wrong way" (with one query each depth level) here get_depth()

The problem is that it's a recursive function and every depth requires one more query.

Answer 1

I found a very easy way to solve it, even without SQL. In my case I'm using Wordpress which have many tags and classes.

<?php $depth = count($post->ancestors); echo $depth; ?>

Simple but it works.

Your solutions on this problem works on more than just Wordpress, if they work. Should I set my solution as the right one, what do you think?