How can I do this in pure MySQL?

[Azu]

Hello

 

Right now I only know how to do this in PHP, and it only recurses as many times as I put the code.

 

What is the MySQL query that will do this without PHP, and without a limit on recursions? Please help.. I'm stumped..

 

set_time_limit(120);

$a0=timed_query("select `id` from `db`.`Topics`");

while($b0=mysqli_fetch_row($a0)){$q=0;$x=0;

$x=mysqli_result(timed_query("select count(*) from `db`.`Posts` where `question_id`=$b0[0]"));

if(!$a1=timed_query("select `id` from `db`.`Topics` where `parent`=$b0[0]"))break;

$q=mysqli_num_rows($a1);

while($b1=mysqli_fetch_row($a1)){

if(!$a2=timed_query("select `id` from `db`.`Topics` where `parent`=$b1[0]"))break;

$q=$q+mysqli_num_rows($a2);

$x=$x+mysqli_result(timed_query("select count(*) from `db`.`Posts` where `question_id`=$b1[0]"));

while($b2=mysqli_fetch_row($a2)){

if(!$a3=timed_query("select `id` from `db`.`Topics` where `parent`=$b2[0]"))break;

$q=$q+mysqli_num_rows($a3);

$x=$x+mysqli_result(timed_query("select count(*) from `db`.`Posts` where `question_id`=$b2[0]"));

while($b3=mysqli_fetch_row($a3)){

if(!$a4=timed_query("select `id` from `db`.`Topics` where `parent`=$b3[0]"))break;

$q=$q+mysqli_num_rows($a4);

$x=$x+mysqli_result(timed_query("select count(*) from `db`.`Posts` where `question_id`=$b3[0]"));

while($b4=mysqli_fetch_row($a4)){

if(!$a5=timed_query("select `id` from `db`.`Topics` where `parent`=$b4[0]"))break;

$q=$q+mysqli_num_rows($a5);

$x=$x+mysqli_result(timed_query("select count(*) from `db`.`Posts` where `question_id`=$b4[0]"));

while($b5=mysqli_fetch_row($a5)){

if(!$a6=timed_query("select `id` from `db`.`Topics` where `parent`=$b5[0]"))break;

$q=$q+mysqli_num_rows($a6);

$x=$x+mysqli_result(timed_query("select count(*) from `db`.`Posts` where `question_id`=$b5[0]"));

while($b5=mysqli_fetch_row($a5)){

if(!$a6=timed_query("select `id` from `db`.`Topics` where `parent`=$b5[0]"))break;

$q=$q+mysqli_num_rows($a6);

$x=$x+mysqli_result(timed_query("select count(*) from `db`.`Posts` where `question_id`=$b5[0]"));

while($b5=mysqli_fetch_row($a5)){

if(!$a6=timed_query("select `id` from `db`.`Topics` where `parent`=$b5[0]"))break;

$q=$q+mysqli_num_rows($a6);

$x=$x+mysqli_result(timed_query("select count(*) from `db`.`Posts` where `question_id`=$b5[0]"));

while($b5=mysqli_fetch_row($a5)){

if(!$a6=timed_query("select `id` from `db`.`Topics` where `parent`=$b5[0]"))break;

$q=$q+mysqli_num_rows($a6);

$x=$x+mysqli_result(timed_query("select count(*) from `db`.`Posts` where `question_id`=$b5[0]"));

while($b5=mysqli_fetch_row($a5)){

if(!$a6=timed_query("select `id` from `db`.`Topics` where `parent`=$b5[0]"))break;

$q=$q+mysqli_num_rows($a6);

$x=$x+mysqli_result(timed_query("select count(*) from `db`.`Posts` where `question_id`=$b5[0]"));}}}}}}}}}

timed_query("update `db`.`Topics` set `subs`=$q,`reply`=$x where `id`=$b0[0]");}}

exit;}

[roopurt18]

Would it have killed you to just give a brief explanation of what that code does or to at least format it?

 

(EDIT)  Better yet, what is your table structure and what are you trying to accomplish?

[Azu]

Sorry thought it was self explanatory.

 

If not, I'm not really sure how to summarize it.

[Illusion]

tell the functionality atleast.......it just look like 

[fenway]

What's the relationship between posts and topics? What do you want to display?

[Azu]

Topics is like this topic, and posts is like, well, this post. And you can make topics inside of other topics, unlimited, as deep as you want.

 

The code above fixes the counts for the amount of replies in a topic, and amount of sub-topics in it, since my forum isn't finished/perfect yet those values get messed up sometimes.

[fenway]

So you're trying to count the number of posts in each topics?

[Azu]

And all of it's subtopics.. yes. That's what the code I posted in the first post does. But how can I do it in pure MySQL?

[fenway]

And all of it's subtopics.. yes. That's what the code I posted in the first post does. But how can I do it in pure MySQL?

Sure you can... you'll have to join in as many copies of the topics table as you have subtopics first.

[Azu]

Sorry but I have no idea what you just said. I'm not very good at MySQL yet. Could somebody please post a query or something that will do what my PHP code above does? That would be really nice!

[fenway]

Sorry but I have no idea what you just said. I'm not very good at MySQL yet. Could somebody please post a query or something that will do what my PHP code above does? That would be really nice!

Have you ever worked with JOINs before?

[Azu]

No =/ I've never used them before..

[fenway]

No =/ I've never used them before..

 

Then you have some reading to do...

[Azu]

So noone wants to help? (I mean besides saying "OMIGAWD LRN2USE JOINZ LUUUL" which isn't helpful at all)

[fenway]

So noone wants to help? (I mean besides saying "OMIGAWD LRN2USE JOINZ LUUUL" which isn't helpful at all)

 

We're just looking for some effort on your end to work towards a solution....

 

select t.id count(p.question_id) from topics as t left join posts as p on p.question_id=t.id group by t.id

 

Will give you the # of posts per topic.  If you want to deal with subcategories, you'll need to join them in as I said earlier.

[Azu]

We're just looking for some effort on your end to work towards a solution....

Sorry it's just that I have no clue how to do this and in 12 replies the only info I got was "learn how to use joins" and I have no clue how to make use of that.

 

select t.id count(p.question_id) from topics as t left join posts as p on p.question_id=t.id group by t.id

 

Will give you the # of posts per topic.  If you want to deal with subcategories, you'll need to join them in as I said earlier.

Thank you will try that.

[Azu]

Well.. thanks for trying to help me ^^ I give up though. I've been trying for a long time and I still can't get this to work. And when I try to use the query you posted it gives an error

Error Code : 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count(p.question_id) from topics as t left join posts as p on p.question_id=t.id' at line 1

 

 

 

=/

 

I guess I'll try using postgresql or something and see if I have better luck in that? Since it seems this can't be done in MySQL...

[fenway]

What's the query? Don't blame an entire RDMBS for a syntax error.

[Azu]

The one I quoted from you near the top of the page.

 

And I didn't mean that MySQL is bad because of the one error sorry.

 

I just mean that I really want to get this working and I've been trying a long time and can't figure out how to do it in MySQL so I'm starting to think it can't be done in MySQL x_x;

[fenway]

There's a missing comma after t.id.

[Azu]

Thanks but that wasn't it. And I'm not even sure what I'm supposed to do with this query even if I do get it to run :s

[fenway]

Thanks but that wasn't it. And I'm not even sure what I'm supposed to do with this query even if I do get it to run :s

What's the new error?

[Azu]

It doesn't change the error at all.

[effigy]

You added it here?

 

select t.id,

[Azu]

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..