I am creating a nested comment response system in my application.
Currently, everything is working as intended, but I found that I need to use several MySQL queries to get the necessary data. And even worse, the “response” request is in the foreach loop. Bearing in mind that although it works great at the moment, it is far from optimal and will cause problems as the data set grows.
Therefore, I want to solve this problem before delving into development.
Since the tables for the application are the same as the wordpress blog for the site, I use the shortened wordpress line for queries.
The current way to create the page is as follows:
A comment table is requested and all results related to projectid are retrieved: -
$commentquery = "select projects_comments.*, users.user_url, users.display_name from ".$wpdb->prefix."projects_comments projects_comments left join ".$wpdb->prefix."users users on users.ID=projects_comments.userid where projectid = '$projectid' order by projects_comments.commentid desc "; $comments = $wpdb->get_results($commentquery);
Then I execute the foreach loop as below: -
if($comments) { foreach ( $comments as $c ) { $replyquery = "select project_replies.*, users.user_url, users.display_name from ".$wpdb->prefix."project_replies project_replies left join ".$wpdb->prefix."users users on users.ID=project_replies.uid where project_replies.cid = '$c->commentid' order by project_replies.id desc limit 2 "; $replies = $wpdb->get_results($replyquery); asort($replies); $countquery = "select count(*) from ".$wpdb->prefix."project_replies project_replies where project_replies.cid='".$c->commentid."' "; $replycount = $wpdb->get_var($countquery);
There are two more queries inside this loop. The first one gets answers for each comment, but limits the results to 2 (I want to do this in order to have a “see all answers” button, which then asks the DB for the rest if the user requires them), the second request counts the total number of responses.
Then html is generated for each response inside the loop using the second nested foreach in the above loop (where it says generates html code here), as shown below: -
if ($replies){ foreach ( $replies as $r ){
All data is extracted from these arrays as follows:
$c->userid, $c->body etc... For the comments $r->userid, $r->body etc... For the replies.
I want to keep this format, if at all possible.
So, as stated at the beginning of the question, all this works fine, but I know that by nesting the answer and numerical queries, I execute a lot more queries than necessary. 100 comments will generate responses to 100 responses and 100 response requests, etc.
Thanks to some helpful people on this site, I considered using the connection to get all the raw data at a time for comments and answers. So ...
$commentquery2 = "SELECT c.commentid, c.userid, c.body as cbody, c.projectid, c.posttime, cu.user_url AS cu_url, cu.display_name AS cu_name, r.*, ru.user_url AS ru_url, ru.display_name AS ru_name FROM ".$wpdb->prefix."projects_comments AS c LEFT JOIN ".$wpdb->prefix."users AS cu ON cu.ID = c.userid LEFT JOIN ".$wpdb->prefix."project_replies AS r ON r.cid = c.commentid LEFT JOIN ".$wpdb->prefix."users AS ru ON ru.ID = r.uid WHERE c.projectid = $projectid ORDER BY c.commentid DESC, r.id DESC";
Although this really works (and for me it was enough to mention this issue, as was said), I had several difficulties in putting it into practice.
Firstly, it retrieves all the data as separate lines, which means that if I have 5 comments, each of which has 3 answers, I actually get 15 lines, and not a nested data object with answers nested in each comment line .
To solve this problem, I tried to process several arrays as shown:
$old_id=NULL; $comments=array(); foreach($getcomments as $c){ if($c->commentid !== $old_id){ $comments[$old_id] = $c; $old_id = $c->commentid; } $comments[$old_id]['replies'][] = $c; }
Doing this gives me a nested data object as needed. However, it does not include a response count request and does not limit each set of answers to 2, as expected, it extracts all of them.
And finally, with my current html generation code inside foreach loops:
foreach($comments){ //generate comment html foreach($replies) { //generate replies html } }
I cannot get it to work correctly with a nested data object. Access to the right deep answers seems puzzled to me.
So, to summarize, I want to be able to remove circular queries, combine them into one larger and more efficient query or, in the worst case, into a data query and a separate counting query, and then create a neatly nested data object, comments as strings and any answers nested inside under the heading "answers"
Then I need to be able to scroll them correctly in my PHP code to generate the required html.
I apologize for the length of this question and understand that it can make many of you answer, but I struggled with this for 19 hours right now and really need help.
Many thanks to everyone who offers any suggestions.