Databases
StackOverflow2013
Postsdbo
PONested comment system mysql ordering
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONested comment system mysql ordering
    primarykey
    data
    text
    <p>Ok, I realize there are many ways to accomplish comments. The way I have chosen is a single table set up like this.</p> <pre><code>id comment date time orig_comment 1 Hello 03-01-2013 10:10:10 0 2 Hello 03-02-2013 10:10:10 0 3 Hello 03-03-2013 10:10:10 1 </code></pre> <p>So to clearify there is a first level and then users can reply to that comment(these are the only two levels). I give each unique incrementing id, and specify orig_comment. If orig_comment is "0" then it is a base level comment, if it is nested then orig_comment will be the id of the original comment. Simple enough. So I need the comments to be ordered of course. Here is how I do this as of now.</p> <pre><code>mysql_query("SELECT Comments.* FROM Comments ORDER BY IF(Comments.orig_comment = 0, Comments.id, Comments.orig_comment)DESC,Comments.id ASC") </code></pre> <p>This works to get the newest comments retrieved first(which is what I want), but when it comes to nested comments it orders them by oldest first(not what I need). I need to order primary comments from newest to oldest, and any replies should be ordered also from newest to oldest. I have tried without success to modify my query to do this but cannot figure it out.</p> <p>As a side question does this system make sense from a usabality standpoint? Do you think ordering nested comments that way would be confusing? I wanted to do it this way because my reply button is located on the base comment, and once clicked adds a textarea right below the base comment. I just thought it would be easier to see your post if it too came right below the base comment instead of being thrown all the way to the bottom of the nested replies. Any thoughts on this?</p> <p>Also want to address another question I have using this to page comments. I can use a simple limit to get only the x number of comments like this:</p> <pre><code>mysql_query("SELECT Comments.* FROM Comments ORDER BY IF(Comments.orig_comment = 0, Comments.id, Comments.orig_comment)DESC,Comments.id ASC LIMIT 0, $page") </code></pre> <p>This method obviously does not pay attention to the number of replies to each main comment. So I end up cutting off replies to the very last main comment. I would like to enforce the limit only when orig_comment='0', so that no matter how many replies there are to a comment it will show all of them. I tried</p> <pre><code>mysql_query("SELECT Comments.* FROM Comments ORDER BY IF(Comments.orig_comment = 0, Comments.id, Comments.orig_comment)DESC,Comments.id ASC LIMIT 0, SELECT COUNT(id)FROM Comments WHERE orig_comment='0' LIMIT $page") </code></pre> <p>This throws a syntax error though.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USdminicrick1
    1. USdminicrick1
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COShouldn't it be Comments.id DESC if you want to sort with newest first? Doesn't the IF say sort descending by id for base level, and descending by origin_comment for others. You may use case statement instead: http://stackoverflow.com/questions/3550942/can-you-add-if-statement-in-php-mysql-order-by
      singulars
      1. PONested comment system mysql ordering
      1. USapelsinapa
    2. CODoing so ends up putting the newest reply at the top without any base comment before it. I also have tried to integrate the query from that very article but again I end up with a reply on the top without a base comment before it.
      singulars
      1. PONested comment system mysql ordering
      1. USdminicrick1
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload