0

I want to return the loop of all the comments where a comment is a comment-of an especific post.

For example in the next page post?id=30 I want to show all the loop of comment till getting to the main post...does it makes sense?

Posts

id_post     |       post          | comment_of 
    25          main post               0 
    26          comment 1st level       25 
    27          comment 2nd level       26 
    28          another post            0 
    29          comment not related     14 
    30          comment 3rd level       27 
    31          comment not related     13

Query 

    SELECT id_post, 
           post, 
           comment_of 
    FROM (SELECT * FROM posts 
    ORDER BY comment_of, id_post) posts_sorted, 
            (SELECT @pv := '25') initialisation 
    WHERE find_in_set(comment_of, @pv) 
    AND length(@pv := concat(@pv, ',', id_post)) /*key line*/
    OR id_post=@pv

Result 

id_post     |       post          | comment_of 
    25          main post               0 
    26          comment 1st level       25 
    27          comment 2nd level       26 
    30          comment 3rd level       27

working fiddle http://sqlfiddle.com/#!9/c07667/2

PROBLEM

With this fiddle I go from the main post to the last comment....What I want is starting with the last comment (id_post=30) to get to the main post (id unknown) in page post=?id=30

joe
  • 219
  • 2
  • 11
  • "I want to get the results by the last comment, Not by the main post....in this case I want to get the same result by choosing 30 (last comment). Not vice versa" posting a expected result as ascii data table says more then these words and also makes it more clear to understand. – Raymond Nijland Mar 19 '18 at 19:20
  • I took the idea from @trincot [link](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) but he retrieves the results starting from the main post id...I want the opossite (starting from the last_comment id) – joe Mar 19 '18 at 19:51

0 Answers0