Azu Posted September 6, 2007 Share Posted September 6, 2007 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;} Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/ Share on other sites More sharing options...
roopurt18 Posted September 6, 2007 Share Posted September 6, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-343044 Share on other sites More sharing options...
Azu Posted September 7, 2007 Author Share Posted September 7, 2007 Sorry thought it was self explanatory. If not, I'm not really sure how to summarize it. Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-343510 Share on other sites More sharing options...
Illusion Posted September 7, 2007 Share Posted September 7, 2007 tell the functionality atleast.......it just look like Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-343738 Share on other sites More sharing options...
fenway Posted September 8, 2007 Share Posted September 8, 2007 What's the relationship between posts and topics? What do you want to display? Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-344454 Share on other sites More sharing options...
Azu Posted September 9, 2007 Author Share Posted September 9, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-344722 Share on other sites More sharing options...
fenway Posted September 9, 2007 Share Posted September 9, 2007 So you're trying to count the number of posts in each topics? Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-344747 Share on other sites More sharing options...
Azu Posted September 10, 2007 Author Share Posted September 10, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-345460 Share on other sites More sharing options...
fenway Posted September 11, 2007 Share Posted September 11, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-346100 Share on other sites More sharing options...
Azu Posted September 12, 2007 Author Share Posted September 12, 2007 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! Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-346542 Share on other sites More sharing options...
fenway Posted September 12, 2007 Share Posted September 12, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-347058 Share on other sites More sharing options...
Azu Posted September 12, 2007 Author Share Posted September 12, 2007 No =/ I've never used them before.. Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-347173 Share on other sites More sharing options...
fenway Posted September 15, 2007 Share Posted September 15, 2007 No =/ I've never used them before.. Then you have some reading to do... Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-348825 Share on other sites More sharing options...
Azu Posted September 16, 2007 Author Share Posted September 16, 2007 So noone wants to help? (I mean besides saying "OMIGAWD LRN2USE JOINZ LUUUL" which isn't helpful at all) Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-349323 Share on other sites More sharing options...
fenway Posted September 17, 2007 Share Posted September 17, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-349920 Share on other sites More sharing options...
Azu Posted September 17, 2007 Author Share Posted September 17, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-350116 Share on other sites More sharing options...
Azu Posted October 24, 2007 Author Share Posted October 24, 2007 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... Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-376955 Share on other sites More sharing options...
fenway Posted October 24, 2007 Share Posted October 24, 2007 What's the query? Don't blame an entire RDMBS for a syntax error. Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-377028 Share on other sites More sharing options...
Azu Posted October 24, 2007 Author Share Posted October 24, 2007 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; Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-377116 Share on other sites More sharing options...
fenway Posted October 24, 2007 Share Posted October 24, 2007 There's a missing comma after t.id. Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-377144 Share on other sites More sharing options...
Azu Posted October 24, 2007 Author Share Posted October 24, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-377181 Share on other sites More sharing options...
fenway Posted October 24, 2007 Share Posted October 24, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-377187 Share on other sites More sharing options...
Azu Posted October 24, 2007 Author Share Posted October 24, 2007 It doesn't change the error at all. Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-377197 Share on other sites More sharing options...
effigy Posted October 24, 2007 Share Posted October 24, 2007 You added it here? select t.id, Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-377210 Share on other sites More sharing options...
Azu Posted October 24, 2007 Author Share Posted October 24, 2007 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.. Quote Link to comment https://forums.phpfreaks.com/topic/68237-how-can-i-do-this-in-pure-mysql/#findComment-377227 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.