Results 1 to 9 of 9

Thread: Order Mysql Results??

  1. #1
    Join Date
    Jan 2007
    Posts
    94
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Cool Order Mysql Results??

    Hey all,

    So here's the deal. I have a list of comments, and each comment gets an automatically generated commentid when it gets added to my mysql table. I just added the functionality to allow users to reply to other ppl's comments (with much difficulty and help!)... So I added another field to my table called replytoid. This field holds the commentid number of the comment being replied to.

    When someone views a blog, I want it to display all the associated comments in ASC order. If a comment is actually a reply to another comment, it should show up directly under it (despite the commentid number).

    I normally use ORDER by or Group by in my mysql queries to organize data for output. The pages are in php.


    PHP Code:
    $cmtqry="select * from ".BLOG_COMMENTS." where eparent='".$HTTP_GET_VARS['blogid']."' order by commentid, replytoid";
                    
    $cmtres=mysql_query($cmtqry);
                    
    $cmtrows=mysql_num_rows($cmtres);
                    if(
    $cmtrows 0){
                    while(
    $cmtarr=mysql_fetch_array($cmtres)){ 
    But this doesn't seem to work correctly. I also used the conjunction of the group by and order by... that didn't work either.

    Is there another way to do this?? Any help would be appreciated! You guys are a ton of help. THANKS IN ADVANCE!
    Last edited by jnscollier; 05-21-2007 at 05:21 AM.

  2. #2
    Join Date
    Jul 2006
    Location
    just north of Boston, MA
    Posts
    1,806
    Thanks
    13
    Thanked 72 Times in 72 Posts

    Default

    this sounds like a database structuring problem, possibly?

    do you have seperate tables for posts / replies?

    here is how I would do organize it.

    table 1: all posts (starter / reply)
    table 2: post_has_reply
    record 1: commentid
    record 2: parent post

    that way you can have an unlimited number of posts with as many replies to each as you wanted and you wont have to worry about redundant data being in a post table and a reply table all at the same time.

    ps. if this isnt what you were looking for sorry

  3. #3
    Join Date
    Jan 2007
    Posts
    94
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default

    Right now here's my table structure...

    Commentid - Unique generated num for each comment
    Comment
    Eparent - blog id number comment is associated with
    dtPostDate - date comment was posted
    intMemberid - id for the person who posted the comment
    replytoid - this defaults to zero if it's just a comment, if it's a reply to a comment this field holds the commentid the reply is to

    So, if i split the table up into replies and comments (posts), would that be easier for sorting the display??

    What I mean by that is... I'm having a problem displaying the comments in order (date posted, i just use the commentid for this because I don't track times comments are posted just date) and then the replies under the appropriate comments... Would this be easier to accomplish if I had two tables?

  4. #4
    Join Date
    Jul 2006
    Location
    just north of Boston, MA
    Posts
    1,806
    Thanks
    13
    Thanked 72 Times in 72 Posts

    Default

    Quote Originally Posted by jnscollier View Post
    Right now here's my table structure...

    Commentid - Unique generated num for each comment
    Comment
    Eparent - blog id number comment is associated with
    dtPostDate - date comment was posted
    intMemberid - id for the person who posted the comment
    replytoid - this defaults to zero if it's just a comment, if it's a reply to a comment this field holds the commentid the reply is to
    is this your only table?

    Quote Originally Posted by jnscollier
    What I mean by that is... I'm having a problem displaying the comments in order (date posted, i just use the commentid for this because I don't track times comments are posted just date) and then the replies under the appropriate comments... Would this be easier to accomplish if I had two tables?
    well if the commentid is auto incremented, then you can order by the date then the commentid... the comments would be sorted by the date, and since the commentid is auto incremented, the ones that are posted first will display first because they will have a lower comment id.


    Quote Originally Posted by jnscollier
    So, if i split the table up into replies and comments (posts), would that be easier for sorting the display??
    well that depends on how you set up your database and how well you know what you are doing. if you only have 1 table with the specs above, I can tell you right now that it is not optimized to full capacity. if you are trying to build like a message forum type thing then you would need alot more then just 1 table to be optimized. you would need at least 4 tables.

    user_info
    forum
    message
    msg_replies

    for your example i would go for splitting up that table you have into 5 tables
    user_info
    forum
    comment
    comment_forum
    comment_reply


    does that make any sense?

  5. #5
    Join Date
    Jan 2007
    Posts
    94
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default

    Thanks for your help, after reading your replies.... Everything just clicked!

    An if-while loop through the comments and then a if-while loop through the replies seems to do the trick

  6. #6
    Join Date
    Jul 2006
    Location
    just north of Boston, MA
    Posts
    1,806
    Thanks
    13
    Thanked 72 Times in 72 Posts

    Default

    Quote Originally Posted by jnscollier View Post
    Thanks for your help, after reading your replies.... Everything just clicked!

    An if-while loop through the comments and then a if-while loop through the replies seems to do the trick
    what do you mean an if-while loop?

  7. #7
    Join Date
    Jan 2007
    Posts
    94
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default

    well this is what i did, and it seems to work perfectly so i don't know (i'm definitely NOT a php expert)

    query the comments table by commentid ASC order
    if rows exist for the blog, while going through the array... display the results.

    query the replies
    if replies exist for the blog, while going through the array, if the reply = the commentid number... display when commentid = replytoid

    don't know if that makes much sense without posting all the code... care to see the page? if so, message me

  8. #8
    Join Date
    Jul 2006
    Location
    just north of Boston, MA
    Posts
    1,806
    Thanks
    13
    Thanked 72 Times in 72 Posts

    Default

    you could have just posted your query, but if its working for you then thats cool fine

  9. #9
    Join Date
    Jan 2007
    Posts
    94
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default

    lol yeah, that would've worked if i was at home and had the code at the time i wrote that post.

    thanks

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
  •