...

View Full Version : Creating an advanced comment/reply system



[vengeance]
12-04-2010, 05:48 PM
Hello.

I want to create a comment system for my articles on my site. Usually I would mark each comment with "#<number>" and have people to reply to an individual person by writing the comment id and their message - however, it gets annoying reading comment number 50 replying to comment number 30 - you'd have to scroll all the way back up to look.

I want to create a reply script that appears right underneath the original message and pushed a bit to the right, which is seen on many different sites.
One of them is Cadred (http://www.cadred.org/News/Article/132940/ for example)

I haven't created these types of systems before.. I haven't created the SQL table yet either, because I'm unsure of how it would be set up. I'm not asking to be spoonfed, I'm actually just asking how to set the system up so it arranges the comments like the one shown on Cadred. And help with how to set up the database the easiest way would just be a great plus!

Thanks.

The reaper
12-04-2010, 10:27 PM
I do not believe this is a straight MySQL or PHP issue, to get it to align over, you can either put it in a table which may not be enough for you, or you can add padded/css to move over replies.

[vengeance]
12-04-2010, 10:33 PM
I do not believe this is a straight MySQL or PHP issue, to get it to align over, you can either put it in a table which may not be enough for you, or you can add padded/css to move over replies.
The alignment part isn't the main part of my question. My question is how I would set it up to make the script loop and check the entire database for if the input is a reply - if true, then which comment_id it is a reply to, and then place it correctly..

MattF
12-04-2010, 11:46 PM
You'd have two id's per message. For the sake of example we'll call them id and response_id. Id is for the message itself, the response_id is the id of the message it's in response to. Then just order appropriately when select'ing from the DB.

[vengeance]
12-05-2010, 12:01 AM
You'd have two id's per message. For the sake of example we'll call them id and response_id. Id is for the message itself, the response_id is the id of the message it's in response to. Then just order appropriately when select'ing from the DB.

Yeah, that's also what I had in mind.

But the problem is that I don't know how I would grab all of those informations and set it up perfectly.

I know how to show comments and display one reply underneath the comment (check below) - but how would I make it loop for all replies to the comment? And it'd also check for a reply to a reply (like shown on link at topic start).



$getComments = mysql_query("SELECT `id` FROM `comments` ORDER BY `date` ASC");
while($comments = mysql_fetch_array($getComments)){
$checkReply = mysql_query("SELECT `id` FROM `comments` WHERE `reply_id` = '".$comments['id']."'");
if(mysql_num_rows( ...etc
}


But yet I'm not sure if this method is leading me the correct way. :confused:

MattF
12-05-2010, 12:13 AM
Come to think of it, you would probably need three id's per entry. The id of the parent article would also need including, and then you would do something like:



$sql = 'SELECT id, content FROM comments WHERE article_id='.$id.' ORDER BY id, response_id';


or similar. Can't think offhand whether that would order it as necessary. Best to ask in the SQL section and get a definite response on the specifics from one of the SQL boffins. :D

Afro_Programmer
12-05-2010, 12:16 AM
The main idea is that your messages will be stored in one table and ALL comments (from every message O_o) will be stored in another table.

1. Get the main message ID.
2. Query your comments table for all comments with the same ID as the message.


The alignment thing is a matter of stying the data after it is retrieved. :thumbsup:

[vengeance]
12-05-2010, 12:23 AM
The main idea is that your messages will be stored in one table and ALL comments (from every message O_o) will be stored in another table.

1. Get the main message ID.
2. Query your comments table for all comments with the same ID as the message.


The alignment thing is a matter of stying the data after it is retrieved. :thumbsup:

Mind giving me an example of how I'd do this? And is your suggestion also based on the fact that people can actually add an reply onto another reply?



Comment: This is a comment!
-- Reply: This is a reply!
---- Reply: This is a reply to a reply!


Right now it's kinda late, and I'm too sleepy to focus properly, and tomorrow I've probably forgotten everything ... lol.

I believe the database part wouldn't be that much of a problem to me, but the PHP-part would. Of how to make it loop (?) through every reply correctly.

MattF
12-05-2010, 12:25 AM
The alignment thing is a matter of stying the data after it is retrieved. :thumbsup:

He's wanting a threaded output though, not just a standard listing. Rather than just id/date ordering:



comment1
comment2
comment3
comment4


it's along the lines of:



comment1
comment2
comment4
comment5
comment3


They also list and display in the relevant order and manner of which comment they were in response to.

Afro_Programmer
12-05-2010, 01:11 AM
Ok I read it wrong.

I'll add onto what I said earlier:


1. All articles stored in one table with a specific ID.
2. All comments stored in another table with the following fields: ID, username, reply to, message

ID - id to the parent message
username - whoever posted the message
reply_to - who's the reply to
message - (obvious)

So...




$article_id = $_POST["article"];
$result = use_db("SELECT * FROM messages where id = '$article_id'")

while ($get = mysqli_fetch_assoc($result) )
{
/*
Sort out the comments here.

If "reply to" is empty, no indent.
If "reply to" is not empty, indent appropriately.
*/
}


I know a problem might come when a single user posts multiple times and gets sub comments on each comment. For that, just put a number or something into the "reply_to" field after the name and edit it out whenever you need to (Ex. jim1, jim2,...) . Here's an example:



____________
Article Table
____________
ID - 25
Author - Bob johnson
Title - Global warming
Content - "bla bla"

____________
Comments
____________
//Jim's comments, no reply to
ID - 25
reply_to - null
username - jim
message - "I hate global warming"

ID - 25
reply_to - null
username - jim1
message - "And another point...."

//responses to jim's comments
ID -25
reply_to - jim
username - steve
message - "Yeah"

ID - 25
reply_to - jim1
username - shawn
message - "right!"


Just pull the numbers off the end whenever you need to. PHP has so many built in functions that this would be no problem.

-edit- actually...you would also need a "root_msg" field or something so sub comments don't get mistaken for main comments. I'm sure if you google you can find better ways to do this. ;o

[vengeance]
12-05-2010, 10:42 AM
@Afro_Programmer:

That's what I've already gotten so far. I can pull out a comment and check for replies to the comment only. I'm having trouble when people want to add another reply onto the reply (which is a reply to the comment..)

I've got this in my database right now


id | nid | reply | replyto | user | comment |
----------------------------------------------------------
1 | 1 | 0 | 0 | Bob | Comment |
----------------------------------------------------------
2 | 1 | 1 | 1 | Joe | Reply! |
----------------------------------------------------------
3 | 1 | 1 | 2 | Todd | Reply to a reply! |


I suppose this would be the most logic way to do this. But here's my PHP script



if(isset($_GET['nid'])){
$nid = $_GET['nid'];

$getComments = mysql_query("SELECT * FROM `news_comments` WHERE `nid` = '".$nid."' AND `reply` = '0' ORDER BY `id` ASC") or die(mysql_error());
if(mysql_num_rows($getComments) > 0){
while($comments = mysql_fetch_array($getComments)){
echo "<div style=\"border: 1px solid #000000;\">";
echo "<div style=\"padding: 3px;\">";
echo $comments['comment'];
echo "</div>";
echo "</div>";

$getReplies = mysql_query("SELECT * FROM `news_comments` WHERE `nid` = '".$nid."' AND `reply` = '1' AND `replyto` = '".$comments['id']."' ORDER BY `id` ASC") or die(mysql_error());
if(mysql_num_rows($getReplies) > 0){
while($replies = mysql_fetch_array($getReplies)){
echo "<div style=\"border: 1px solid #000000;\">";
echo "<div style=\"padding: 3px;\">";
echo $replies['comment'];
echo "</div>";
echo "</div>";
}
}
}
}
}


Obviously, there's still a lot of code missing, and it currently only checks for replies to the actual comment id within the first while().

And here's where I am getting lost on how to actually make a loop that checks for ALL the replies, and their children, and then post it in the correct order, like MattF wrote earlier.



Comment 1
Comment 4 (which is actually a reply to 1)
Comment 5 (reply to 4)
Comment 2
Comment 6 (reply to 2)
Comment 3

MattF
12-05-2010, 11:38 AM
What order are the rows returned in if you run this query?



SELECT id FROM tablename WHERE nid=1 ORDER BY id, reply_to;

[vengeance]
12-05-2010, 02:49 PM
What order are the rows returned in if you run this query?



SELECT id FROM tablename WHERE nid=1 ORDER BY id, reply_to;


Uhm .. I tried inserting random rows with comments/replies here and there, but it doesn't order it correctly.

Here's another site that uses kinda the same concept.
http://forums.creativecow.net/readpost/1/857891

MattF
12-05-2010, 04:22 PM
It's all in the query structure. Pop a thread in the MySQL section about it, including your table structure etc in the post. You'll get a definitive answer there as to best approach.

[vengeance]
12-05-2010, 06:28 PM
It's all in the query structure. Pop a thread in the MySQL section about it, including your table structure etc in the post. You'll get a definitive answer there as to best approach.

Okay, thanks for your time. I'll hop into the SQL section.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum