获取属于多个类别及其子类别的帖子会带来重复记录

I am trying to fetch all posts belonging to multiple categories and their subcategories on a category page in PHP/MySQL. I have three tables involved in this operation with following structure/sample data:

tb_categories

tb_posts

tb_posts_to_categories

The PHP code along with the SQL query to fetch all the posts belonging to a main category and its subcategories is:

public function get_all_posts_by_category(&$output, $category_id)
{
    // Build database query
    $sql = "SELECT `p`.*, `ptc`.`post_id`, `ptc`.`category_id`, `c`.`category_id`, `c`.`category_name`, `c`.`category_url`  
FROM `tb_posts` AS `p` INNER JOIN `tb_posts_to_categories` AS `ptc` ON `p`.`post_id` = `ptc`.`post_id` INNER JOIN `tb_categories` AS `c` ON `ptc`.`category_id` = `c`.`category_id` 
WHERE `c`.`category_id` = $category_id AND `p`.`post_status` = 1 ORDER BY `p`.`post_id` DESC";

    // Execute database query
    $rows = $this->get_by_sql($sql);

    if(is_array($rows))
    {
        foreach($rows as $row)
        {
            $output[] = $row;
        }
    }

    // Build database query
    $sql = "SELECT * FROM `tb_categories` WHERE `category_parent` = $category_id";

    // Execute database query
    $rows = $this->get_by_sql($sql);

    if(is_array($rows))
    {
        foreach($rows as $row)
        {
            $category_id = $row->category_id;
            $this->get_all_posts_by_category($output, $category_id);
        }
    }
    return $output;
}

On a category page, I call above function as:

    // Get all posts of a category and its subcategories
    $posts = $post_obj->get_all_posts_by_category($output, $category_id);

Which yields me following result in an array:

Array
(
    [0] => stdClass Object
        (
            [post_id] => 5
            [post_name] => Post 5
            [post_url] => post-5
            [category_id] => 2
            [category_name] => Nature
            [category_url] => nature
        )

    [1] => stdClass Object
        (
            [post_id] => 2
            [post_name] => Post 2
            [post_url] => post-2
            [category_id] => 2
            [category_name] => Nature
            [category_url] => nature
        )

    [2] => stdClass Object
        (
            [post_id] => 1
            [post_name] => Post 1
            [post_url] => post-1
            [category_id] => 2
            [category_name] => Nature
            [category_url] => nature
        )

    [3] => stdClass Object
        (
            [post_id] => 5
            [post_name] => Post 5
            [post_url] => post-5
            [category_id] => 3
            [category_name] => Lakes
            [category_url] => lakes
        )

    [4] => stdClass Object
        (
            [post_id] => 4
            [post_name] => Post 4
            [post_url] => post-4
            [category_id] => 3
            [category_name] => Lakes
            [category_url] => lakes
        )

    [5] => stdClass Object
        (
            [post_id] => 3
            [post_name] => Post 3
            [post_url] => post-3
            [category_id] => 3
            [category_name] => Lakes
            [category_url] => lakes
        )

)

Clearly, we can see we get one duplicate records for [post_id] => 5 because post with id 5 was assigned to two different categories - category 2 (Parent category - Nature) and category 3 (Child category - Lakes). But on a main category page (nature), I have Post 5 twice in the foreach iteration.

How can I avoid these duplicate records? What will the correct SQL query or what adjustment I need to make in my PHP function to fetch only unique records? Please help me on this guys.

Just fetch all sub-categry-ids of parent-id and place them all in a simple array. Then use in() into query with distinct selection. Please have a look below:

public function get_all_posts_by_category(&$output, $category_id)
{
    //Array of categories with parent category at top
    $cats = array();
    $cats[] = $category_id;

    //Fetch all sub categories
    $sql = "SELECT * FROM `tb_categories` WHERE `category_parent` = $category_id";

    // Execute database query
    $rows = $this->get_by_sql($sql);

    if(is_array($rows))
    {
        foreach($rows as $row)
        {
            $cats[] = $row->category_id; //Push category id to array
        }
    }


    // Build database query to fetch posts
    $sql = "SELECT distinct `p`.*, `ptc`.`post_id`, `ptc`.`category_id`, `c`.`category_id`, `c`.`category_name`, `c`.`category_url`  
    FROM `tb_posts` AS `p` INNER JOIN `tb_posts_to_categories` AS `ptc` ON `p`.`post_id` = `ptc`.`post_id` INNER JOIN `tb_categories` AS `c` ON `ptc`.`category_id` = `c`.`category_id` 
    WHERE `c`.`category_id` in (".implode(',',$cats).") AND `p`.`post_status` = 1 ORDER BY `p`.`post_id` DESC";

    // Execute database query
    $rows = $this->get_by_sql($sql);

    if(is_array($rows))
    {
        foreach($rows as $row)
        {
            $output[] = $row;
        }
    }

    return $output;
}