I have an issue in getting products on parent-category page

My database table structure

CREATE TABLE `tblcategory` (
  `id` int(11) NOT NULL,
  `CategoryName` varchar(200) DEFAULT NULL,
  `Description` mediumtext DEFAULT NULL,
  `Is_Active` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

category.php

$pid=intval($_GET['catid']);
$query=mysqli_query($con,
        "select tblposts.id as pid,tblposts.PostTitle as posttitle,
                tblposts.PostImage,tblcategory.CategoryName as category,
                tblsubcategory.Subcategory as subcategory,
                tblposts.PostDetails as postdetails,tblposts.PostingDate as postingdate,
                tblposts.PostUrl as url 
        from tblposts 
            left join tblcategory on tblcategory.id=tblposts.CategoryId 
            left join  tblsubcategory on  tblsubcategory.SubCategoryId=tblposts.SubCategoryId 
        where tblposts.CategoryId='".$_SESSION['catid']."' 
        and tblposts.Is_Active=1 
        order by tblposts.id desc 
        LIMIT $offset, $no_of_records_per_page");

how to remove subcategory

add parent_id

need me multi category

Could anyone suggest something. Thanks in advance.

Using php code how to go to the products page after the n level categories

CodePudding user response：

SELECT * FROM products LEFT JOIN categories ON products.category = categories.category_id GROUP BY (SELECT parent_id FROM categories WHERE parent_id = 1
GROUP BY parent_id)

CodePudding user response：

Make some adjustments to the structure. Explore the Adjacency List Model and Nested Set Model. Choose the right one according to your needs. Both models have pros and cons. The topic is not short, and your choice depends on many points.

There is a similar topic at the following address: Adjacency list vs. nested set model

I hope I have guided you and that my answer will help you to approach it more correctly.