Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    Regular Coder
    Join Date
    Aug 2009
    Posts
    131
    Thanks
    28
    Thanked 7 Times in 7 Posts

    Creating an advanced comment/reply system

    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.
    Last edited by [vengeance]; 12-04-2010 at 09:06 PM.

  • #2
    Regular Coder
    Join Date
    Oct 2009
    Location
    Florida
    Posts
    155
    Thanks
    16
    Thanked 2 Times in 2 Posts
    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.

  • #3
    Regular Coder
    Join Date
    Aug 2009
    Posts
    131
    Thanks
    28
    Thanked 7 Times in 7 Posts
    Quote Originally Posted by The reaper View Post
    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..

  • #4
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    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.

  • #5
    Regular Coder
    Join Date
    Aug 2009
    Posts
    131
    Thanks
    28
    Thanked 7 Times in 7 Posts
    Quote Originally Posted by MattF View Post
    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).

    PHP Code:
    $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.

  • #6
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    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:

    Code:
    $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.

  • #7
    Regular Coder
    Join Date
    Sep 2010
    Location
    Virginia
    Posts
    112
    Thanks
    11
    Thanked 7 Times in 7 Posts
    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.

  • #8
    Regular Coder
    Join Date
    Aug 2009
    Posts
    131
    Thanks
    28
    Thanked 7 Times in 7 Posts
    Quote Originally Posted by Afro_Programmer View Post
    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.
    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.

  • #9
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Quote Originally Posted by Afro_Programmer View Post
    The alignment thing is a matter of stying the data after it is retrieved.
    He's wanting a threaded output though, not just a standard listing. Rather than just id/date ordering:

    Code:
    comment1
    comment2
    comment3
    comment4
    it's along the lines of:

    Code:
    comment1
    comment2
        comment4
            comment5
    comment3
    They also list and display in the relevant order and manner of which comment they were in response to.

  • #10
    Regular Coder
    Join Date
    Sep 2010
    Location
    Virginia
    Posts
    112
    Thanks
    11
    Thanked 7 Times in 7 Posts
    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...

    PHP Code:

    $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:

    Code:
    ____________
    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
    Last edited by Afro_Programmer; 12-05-2010 at 02:34 AM.

  • #11
    Regular Coder
    Join Date
    Aug 2009
    Posts
    131
    Thanks
    28
    Thanked 7 Times in 7 Posts
    @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
    Code:
    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

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

    Code:
    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

  • #12
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    What order are the rows returned in if you run this query?

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

  • #13
    Regular Coder
    Join Date
    Aug 2009
    Posts
    131
    Thanks
    28
    Thanked 7 Times in 7 Posts
    Quote Originally Posted by MattF View Post
    What order are the rows returned in if you run this query?

    Code:
    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

  • #14
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    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.

  • Users who have thanked MattF for this post:

    [vengeance] (12-05-2010)

  • #15
    Regular Coder
    Join Date
    Aug 2009
    Posts
    131
    Thanks
    28
    Thanked 7 Times in 7 Posts
    Quote Originally Posted by MattF View Post
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •