How can I do this in pure MySQL?

[fenway]

Oh okay thanks ^^ I was putting it in the wrong place. It stopped giving an error now. But it seems to only return the post count 1 level deep..

 

I could have done that with just a simple select id,(select count(*) from posts where question_id=topics.id) from topics

 

It needs to recurse as deep as it goes. That's what I don't know how to do in MySQL..

If you look at one my first posts in this thread, I told you that you're going to have to JOIN in as many tables as you want levels.

[roopurt18]

I could be wrong, but I don't really think you can do all of this in a single query.  What fenway is hinting at is you'll need to run two queries.

 

The first query will be one that calculates the deepest nesting that exists, call this number X.  The next query will contain X - 1 joins.  You'll have to generate appropriate aliases for columns at each join which could possibly get messy.

 

IMO, it may be easier to add a small integer column to the table, call it nesting.  Items at the top have a nesting of zero, any time you insert a child item, just set it's nesting to one more than the parent's.  Then you can query everything at once if you slap ORDER BY `nesting` into the query.  Then with an appropriate loop, you can create the entire hierarchy in a single pass and with a single query.

 

That may be the only way to do this without repeated database queries and without creating what could be a potentially complicated series of JOINS.

 

(edit) I've never dealt with a table that was joined with itself to create nesting though, so someone else jump in and tell me if I'm full of crap if necessary.

[fenway]

You can do this in a single query... you just have to know ahead of time how many levels deep to want to go.

[roopurt18]

Which will require a first query, bringing the total to two.

 

In addition, since you're joining the table to itself you'd have to constantly generate aliases:

SELECT
  t1.*, t2.pid AS t2pid, t3.pid AS t3pid, etc.

 

I'm not saying it's not do-able, just that it adds a level of complexity to the PHP that may make it not worth the trouble.

[Azu]

Thanks guys! And it's not a problem if more then 1 mysql query is required, as long as there isn't a limit on recursion.

 

I still don't understand how I need to use joins to do this though.. if it would be against forum policy for you to post the whole query for me.. maybe I could buy it? What would it cost? *doesn't have much*

[fenway]

Thanks guys! And it's not a problem if more then 1 mysql query is required, as long as there isn't a limit on recursion.

 

I still don't understand how I need to use joins to do this though.. if it would be against forum policy for you to post the whole query for me.. maybe I could buy it? What would it cost? *doesn't have much*

It's not against forum policy, I just don't have the time -- and it's still just a single mysql query, unless I've already forgotten the task at hand.