SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Dublin
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Help needed with SQL Query

    I have an article based website. I'm adding a new feature which will allow users to comment on an article.

    All main comments will have a parent_id of 0
    All replied coments will have a parent_id = to the comment_id of the comment it is replying to.

    Here are the fields of the 'Comment' table

    Comment_id, int
    Parent_id, int
    Article_id, int
    Comment_title, text
    Coment_text, text
    date_posted, timestamp

    This is how I want to display all comments
    (pid is parent_id)

    **Main Comment** (id =1, pid =0)
    **Replied Comment** (id =2, pid =1)

    **Main Comment** (id =3, pid =0)

    **Main Comment** (id =4, pid =0)
    **Replied Comment** (id =5, pid =4)
    **Replied Replied Comment** (id =7, pid =5)
    **Replied Comment**(id =6, pid =4)


    ~Here's my query~

    Initially I wanted to display all comments in order of date posted but this will not work because as shown above the **Replied Comment** with id=6 was posted before the **Replied Replied Comment** id= 7 but the latter comment must be dsiplayed but the former.

    In plain English my query is: Display all comments in order of date but sub comments should be displayed before other parent comments if their associated parent comment was posted earlier.

    I hope that makes sense.

    Any idea what query I can use here.
    Last edited by cosmic_bird; Apr 29, 2006 at 11:22. Reason: Bad title

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select main.Comment_id
         , main.Comment_title
         , main.date_posted
         , reply.Comment_id
         , reply.Comment_title
         , reply.date_posted
         , reply2.Comment_id
         , reply2.Comment_title
         , reply2.date_posted
      from Comment as main
    left outer
      join Comment as reply
        on reply.Parent_id = main.Comment_id
    left outer
      join Comment as reply2
        on reply2.Parent_id = reply.Comment_id
     where main.Parent_id = 0
    order
        by main.date_posted
         , reply.date_posted
         , reply2.date_posted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Feb 2000
    Location
    Vilnius, Lithuania
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In case you're willing to limit the depth of nesting you can do as r937 said. Otherwise you'll have to use recursion in the non-sql part of your code.
    SPAW Editor v.2 - web based wysiwyg editor for PHP and ASP.NET.
    Opera and Safari support, tabbed interface, floating toolbar...

  4. #4
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Dublin
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    Code:
    select main.Comment_id
         , main.Comment_title
         , main.date_posted
         , reply.Comment_id
         , reply.Comment_title
         , reply.date_posted
         , reply2.Comment_id
         , reply2.Comment_title
         , reply2.date_posted
      from Comment as main
    left outer
      join Comment as reply
        on reply.Parent_id = main.Comment_id
    left outer
      join Comment as reply2
        on reply2.Parent_id = reply.Comment_id
     where main.Parent_id = 0
    order
        by main.date_posted
         , reply.date_posted
         , reply2.date_posted
    I have all comments including replies in only 1 table (comment table).

    Maybe I've read the above code wrong but are you saying that there exists a reply tabe and a reply2 table?

    There is no way of telling how many replies there are........there could be any number, so this is why I put it all into one talbe and use a parent_id.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    main, reply and reply2 are table aliases, which are necessary in a self-join

    the query i've given you goes to the 3rd level of the hierarchy -- the main comment is at the top level, replies to main comments are at the 2nd level, and replies to replies are at the 3rd level

    if you want to go deeper, just add more self-joins using the pattern in the query

    if you want unlimited levels, you need a different data model (one that doesn't use parent_id) -- see Storing Hierarchical Data in a Database
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Dublin
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    main, reply and reply2 are table aliases, which are necessary in a self-join

    the query i've given you goes to the 3rd level of the hierarchy -- the main comment is at the top level, replies to main comments are at the 2nd level, and replies to replies are at the 3rd level

    if you want to go deeper, just add more self-joins using the pattern in the query

    if you want unlimited levels, you need a different data model (one that doesn't use parent_id) -- see Storing Hierarchical Data in a Database
    Can you explain what Table Aliases are. I googled 'Table Aliases' but cannot understand it. Are Table Aliases seperate tables?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cosmic_bird
    Are Table Aliases seperate tables?
    no , they are alternate names, like synonyms
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Dublin
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    no , they are alternate names, like synonyms
    I'm almost there. I get the correct results when I run the sql code in MyPHP Admin.

    However, when I try to display it in a website page I just keep on getting the last reply displayed. (in this case reply3)

    Here's the Code (If you think it's a PHP issue I wil then more this post.)
    *******************************

    $rs = mysql_query("SELECT main.comment_id, main.comment_text, main.comment_title, main.date_posted, reply.comment_id, reply.comment_text, reply.comment_title, reply.date_posted, reply2.comment_id, reply2.comment_text, reply2.comment_title, reply2.date_posted, reply3.comment_id, reply3.comment_title, reply3.comment_text, reply3.date_posted
    FROM comment AS main
    LEFT OUTER
    JOIN comment AS reply ON reply.Parent_id = main.comment_id
    LEFT OUTER
    JOIN comment AS reply2 ON reply2.Parent_id = reply.comment_id
    LEFT OUTER
    JOIN comment AS reply3 ON reply3.Parent_id = reply2.comment_id
    WHERE main.Parent_id =0
    ORDER
    BY main.comment_text, reply.comment_text, reply2.comment_text, reply3.comment_text DESC");



    ?>
    <?php while ($row2 = mysql_fetch_array($rs)){


    $datefromdb_ts = $row2["date_posted"];
    $comment_title = $row2["comment_title"];
    $comment_text = nl2br($row2["comment_text"]);
    ?>

    <b><br><br><?=$id?></a></b></font><br>

    <br><?=$comment_title?></a></b></font><br>
    <br><?=$comment_text?><br>
    <br>Posted on <?php echo date( "l, dS F Y", $datefromdb_ts ); ?>
    <br><a href = "reply_form.php?id=<?=$id?>&id2=<?=$i_d?>">Reply to this comment<a>

    <?}?>

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    moved (sorry, i don't do php)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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