9 replies to this topic

[Demonslay]

Ok, yet another stupid sounding question I bet, lol.

I've worked on my own comment systems and what-not that may be similar to that of IPB's in form of use, functionability and flexibility atleast. I was wanting to develop a reply part, like you'd see on dA or some blogs where the reply appears just below the original comment, but with a slight indent.

The only way I found this able to happen is to recursively run a SQL command to find any comments with a reply_id equal to the current comment. While this is easy, I'm wondering if it is at all practical, since this easily runs up to possibly hundreds of queries on just stupid comments! I can only imagine how much of a work-load this must be on dA with how complex and deep their comment/reply system goes (I literally see replies to replies of replies of replies of replies of replies..... and so on until the final reply is nearly on the complete far right of the page with a very small size because of the style).


Is there another, and more practical way without recursion of SQL queries for every single comment retrieved?

[Hayden]

couldn't you just have a table for replies and each reply is assigned what post_id they belong to?

SELECT * replies WHERE post_id = '$post_id'

or am i missing something specific that you're wanting to do?

Edited by SpatialVisionary, 03 February 2007 - 10:44 PM.

[Demonslay]

Ah, interesting, lol.
So I guess everyone else has come to pretty much the same conclusion as I then.

Ok thanks guys, was just making sure there wasn't some other way I wouldn't have been able to think of before I got too deep into it all.

[Av-]

assign a parent id on each comment, then use a subquery in the main query to check if there are any child comments. If there are, run another query which will get the child comments and so on.

"SELECT c1.id, c1.message, (SELECT COUNT(c2.id) FROM COMMENTS c2 WHERE c2.parent_id = c1.id) as count FROM comments c1"

if count >= 1 run another query blabla, you get the idea


edit: never knew about the sql bbcode

Edited by Av-, 04 February 2007 - 06:34 PM.

[Demonslay]

Hmm, I'm having a tad bit trouble getting that through my head, lol. I think I get the concept, just not actually how it's working in that instance.

Maybe a small example?

[Av-]

Well, maybe i'm bringing it more complicated than it actually is and I just realized the method I had in mind is only going to work for a single level comment system, not multi level...

Actually, would it not be easier to get all rows at once, sort them (parent->child->child child ) and have an extra field in your row which holds how many indents that particular comment has...

[Demonslay]

Ah, I see. So I would be able to grab all comments corresponding to a certain video/tutorial/whatever the comments are on, then I would try to arrange them by parent-child relation level, and then try to order them and pair them together by some sort of parent_id and display them by rank.
In theory it all makes sense to me now, but the fun part will be trying to actually do that sorting. :heh:
Any ideas on how to do this?
I'll draft an example for anyone who is more of a visual learner, lol.

Table `comments`
_____________________________
| c_id | p_id | p_lvl |	  txt	 |
----------------------------------------
|   1   |NULL |   1   | 1st comment |
|   2   |   1   |   2	| Reply to #1   |
|   3   |   2   |   3	| Reply to #2   |
|   4   |   1   |   2	| Reply to #1   |
|   5   |NULL |   1   | 2nd comment|
|   6   |   2   |   3	| Reply to #2   |
-----------------------------------------

c_id - Comment id (auto_increment)
p_id - Parent id (NULL if root comment)
p_lvl - Level of nesting (how deep is this comment in the whole schema)
txt - Actual text of the comment obviously

(ASCII art is hard on IPB...)

Then when this is all sorted and displayed, should look like this.

#1: 1st comment
-----#2: Reply to #1
----------#3: Reply to #2
----------#6: Reply to #2
-----#4: Reply to #1
#5: 2nd comment


Hope that helps someone understand what I was thinking, lol.
Now the fun part is constructing a SQL query that should be able to sort it like this...
All I can think of at the moment now is to use this.

SELECT c_id, p_id, p_lvl, txt FROM comments ORDER BY c_time, p_id

(The c_time would be a column with the timestamps of each comment)

Then the thing is, how to use arrays to sort the replies and pair them with their respective parent comments...

Arg, if only I knew how dA and P2L did it! Lol.

Edited by Demonslay, 05 February 2007 - 04:37 PM.

[Mr. Matt]

To display the comments like that you need to use recursion.

All your top level comments have a parent ID of 0, and you make a function to pull out all of the comments depending on parentID.

As you loop through the comments, one by one you look to see if it has children, either by doing a query to see if it has children, or storing in the current row if it has children, and if it has children, then you call the function again witin itself with the current rows ID as the parent ID.

A quiick example:

<?php

function recurse( $parent_id ) {

	$select = mysql_query( "SELECT * FROM `comments` WHERE `parent_id` = '".$parent_id."' " ) or die( mysql_error() );
	if( mysql_num_rows( $select ) > 0 ) {
		while( $row = mysql_fetch_assoc( $select ) ) {
			if( $row['has-children'] == 'y' ) { #If the current comment has children
				recurse( $row['id'] ); #Call the function again with the current ID to get all its children.
			} else {
				$display .= 'Comment with ID '.$row['id'].' with parent ID '.$parent_id.'<br />';
			}
		}
	}

	return $display;

}

#To get it going you call the function with the parent ID of the top level categories, for example I am using 0 as top level parent ID's
echo resurse( '0' );

?>

Be careful as you can get yourself stuck in a infinitave loop if your not careful. But thats the bascis to it, doesn't really get too much harder then that.

Matt

[Demonslay]

Alright, that's what I thought in the first place, only you showed it a tad different from what I would do.

Thanks Matt.

I'm starting to wonder about Av-'s method of a 'sub-query' nested inside of another query though... I've never seen that, and I'm starting to wonder if it is possible even...

[Mr. Matt]

By using the sub query method you are limited to the number of levels of comments you can show, by the number of sub queries you are doing.

So if you have a sub query on the main select query you are limited to showing 1 level of sub comments.

By using the recursion you are not limited, as the script will run through and keep checking if the current comment has children, and if it does it finds those children out, if not it carries on and will find all the comments that are linked.

Your welcome

Matt