#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    6
    Rep Power
    0

    Exclamation How to get comment content and the post title


    In Question and Answer system I am trying to get comment for question and answer and the title of that comment.

    I have done so far and getting comment's content but now the issue is if the comment is on the answer it is not getting the question title. So how can I write query so it will get all comment from question and answer both and will get the title of the question.

    Here I have done so far.

    PHP Code:
    SELECT c.postidp.titlec.typec.useridc.contentc.parentidu.handleu.emailu.avatarblobidu.avatarwidthu.avatarheight FROM qa_posts p
    JOIN qa_posts c ON 
    (p.postid c.parentid)
    LEFT JOIN qa_users u ON c.userid u.userid
    WHERE c
    .type 'C'
    AND p.flagcount 0
    ORDER BY c
    .postid DESC
    LIMIT 5 
    I am unable to add image or even table image link so. no idea how to share that reference..
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    yeah, if you could go ahead and tell us which tables do what, that'd be great
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    yeah, if you could go ahead and tell us which tables do what, that'd be great
    There are two tables qa_posts and qa_users

    qa_posts having below columns

    postid, type, parentid, userid, title, content, format

    Where types are Q = Question, A = Answers and C = Comment

    Here question and answer both can have comment and parent id for appropriate type.

    qa_usres having below columns

    usreid, handle, email, avatarblogid, avatarwidth, avatarheight

    Now the issue is when any answer has comment then the comment have parent id of the answer, and that answer have parent id of the question which is having the title.

    So how I can get the question title for the comment made on the answer
  6. #4
  7. Turn left at the third duck
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Location
    Nelson, NZ
    Posts
    112
    Rep Power
    94
    Cross-posted on phpFreaks. Make up your mind.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by ragax
    Cross-posted on phpFreaks. Make up your mind.
    why? as long as he posts it only once in devshed forums, who cares?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. Turn left at the third duck
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Location
    Nelson, NZ
    Posts
    112
    Rep Power
    94
    I just assumed DevShed has the standard forum posting etiquette of "don't cross-post your question on other fora", but if that's not the case, then no worries!
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by ragax
    on phpFreaks. Make up your mind.
    This is just only to get help quickly to resolve the issue. Nothing else.
  14. #8
  15. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,730
    Rep Power
    1959
    You have question, answers and comments, but you are only getting information on the table using FROM and one JOIN.
    The JOINED table will contain information about the comments which will be connected to either a question or an answer, which explains why you don't have question title for answer comments.

    To get all information, you will have to some more joins, something like this: (not tested)
    sql Code:
     
    SELECT *
    FROM qa_posts p
    JOIN qa_posts ac ON (p.postid = ac.parentid) -- will have answers and comments for questions
    LEFT JOIN qa_users qu ON (ac.userid = qu.userid) -- user data for question comments
    LEFT JOIN qa_posts c ON (ac.postid = c.parentid) -- comments for answer
    LEFT JOIN qa_users au ON (c.userid = au.userid) -- user data for answer comments
    WHERE p.TYPE = 'Q'  -- first level (FROM table) should only be questions 
    AND p.flagcount = 0 -- not sure what this is for; not included in your table description
    ORDER BY p.postid, ac.TYPE DESC, ac.postid  -- sort by question id and then list the question comments before the answers
    LIMIT 5


    You will have to modify the SELECT * to specify which information you want.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by jsoni
    So how I can get the question title for the comment made on the answer
    probably by getting the parent for the answer...
    Code:
    SELECT ...
      FROM qa_posts AS com
    INNER   
      JOIN qa_posts AS ans
        ON ans.postid = com.parentid 
       AND ans.flagcount = 0 
    INNER   
      JOIN qa_posts AS que
        ON que.postid = ans.parentid 
    INNER
      JOIN qa_users AS usr 
        ON usr.userid = com.userid 
     WHERE com.type = 'C'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by MrFujin
    You have question, answers and comments, but you are only getting information on the table using FROM and one JOIN.
    The JOINED table will contain information about the comments which will be connected to either a question or an answer, which explains why you don't have question title for answer comments.

    To get all information, you will have to some more joins, something like this: (not tested)
    sql Code:
     
    SELECT *
    FROM qa_posts p
    JOIN qa_posts ac ON (p.postid = ac.parentid) -- will have answers and comments for questions
    LEFT JOIN qa_users qu ON (ac.userid = qu.userid) -- user data for question comments
    LEFT JOIN qa_posts c ON (ac.postid = c.parentid) -- comments for answer
    LEFT JOIN qa_users au ON (c.userid = au.userid) -- user data for answer comments
    WHERE p.TYPE = 'Q'  -- first level (FROM table) should only be questions 
    AND p.flagcount = 0 -- not sure what this is for; not included in your table description
    ORDER BY p.postid, ac.TYPE DESC, ac.postid  -- sort by question id and then list the question comments before the answers
    LIMIT 5


    You will have to modify the SELECT * to specify which information you want.
    Thanks a lot for your help...

    Unfortunately this code doesn't output the title. Also in LIMIT it is counting question and answer also so if I set it to 5 for instance than it considering recent 5 items including question and answer.

    Any idea? I would appreciate for your and everyone help resolving the issue.
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    probably by getting the parent for the answer...
    Code:
    SELECT ...
      FROM qa_posts AS com
    INNER   
      JOIN qa_posts AS ans
        ON ans.postid = com.parentid 
       AND ans.flagcount = 0 
    INNER   
      JOIN qa_posts AS que
        ON que.postid = ans.parentid 
    INNER
      JOIN qa_users AS usr 
        ON usr.userid = com.userid 
     WHERE com.type = 'C'
    Your code is getting title but it is repeating item I dont know the reason. I appreciate Your and MrFujin's help just trying to understand the code, it is very complex for me ...just jumped into PHP MYSQL..

    Could you please help to find what's wrong and why it is getting the same title as well as wrong id?
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    6
    Rep Power
    0
    I found some way to check conditionally something like here but this code also not working.. but I am sure you experts will guide me how to correct this code.

    PHP Code:
    JOIN qa_posts AS parentposts
    ON qa_posts
    .postid=(
            CASE
                
    LEFT(perentposts.type1)
                
    WHEN 
                    
    'A'
                
    THEN 
                    parentposts
    .parentid 
                
    ELSE 
                    
    parentposts.postid
            END
        
    )
    JOIN qa_posts AS cposts 
    ON parentposts
    .postid=cposts.parentid
    LEFT JOIN qa_users 
    AS cusers 
    ON cposts
    .userid=cusers.userid 

    JOIN 
    (SELECT postid FROM qa_posts 
    WHERE type
    =$
    ORDER BY qa_posts.created DESC 
    LIMIT 5

    y ON cposts.postid=y.postid 
    WHERE qa_posts
    .type='Q' 
    AND ((parentposts.type='Q') OR (parentposts.type='A')) 

IMN logo majestic logo threadwatch logo seochat tools logo