CODEWITHSUNDEEP
phphtmlmysqldatabasepdo
user_59910105
user_59910105

Reputation: 119

Display subcategory and its parent category in a table from single mysql database table using php

This is my database table

id category parent_id
1 category1
2 category2
3 category3
4 subcategory4 2
5 subcategory5 1
6 subcategory6 3
7 subcategory7 1

This is my code

$result = mysqli_query($con,"SELECT * FROM category_table ORDER BY parent_id");
    
    $category = array(
    'categories' => array(),
    'parent_cats' => array()
    );
    
    //build the array lists with data from the category table
    while ($row = mysqli_fetch_assoc($result)) {
    //creates entry into categories array with current category id ie. $categories['categories'][1]
    $category['categories'][$row['id']] = $row;
    //creates entry into parent_cats array. parent_cats array contains a list of all categories with children
    $category['parent_cats'][$row['parent_id']][] = $row['id'];
                                                                }
    
    function buildCategory($parent, $category) {
    $html = "";
    if (isset($category['parent_cats'][$parent])) {
        
        foreach ($category['parent_cats'][$parent] as $cat_id) {
            if (!isset($category['parent_cats'][$cat_id])) {
                $html .= "<tr>\n";
                $html .= "<td>\n  <a href=''>" . $category['categories'][$cat_id]['category'] . "</a>\n</td> \n";
                $html .= "</tr>\n";
            }
            if (isset($category['parent_cats'][$cat_id])) {
                $html .= "<tr>\n";
                $html .= "<td>\n  <a href=''>" . $category['categories'][$cat_id]['category'] . "</a> \n";
                $html .= buildCategory($cat_id, $category);
                $html .= "</td> \n";
                $html .= "</tr>\n";
            }
        }
        
    }
    return $html;
    }
    
    
    echo buildCategory('', $category);?>

Output for above code looks like:

category1
subcategory5
subcategory7
category2
subcategory4
category3
subcategory6

My expected output should look like this:

category Parent Category
category1
category2
category3
subcategory5 category1
subcategory7 category1
subcategory4 category2
subcategory6 category3

I have been working on this for a while now.. Can anyone please tell me how can I modify my code or use any other method to achieve my expected output?

Upvotes: 1

Views: 725

Answers (1)

Anil Parshi
Anil Parshi

Reputation: 913

Try this query instead inner join will give you only the required data,
then you can adjust the data as per your needs.

SELECT sub_category.id as s_id, sub_category.category as s_cat_name, p_category.category as p_cat_name 
FROM category_table sub_category 
INNER JOIN category_table p_category ON p_category.id = sub_category.parent_id 
ORDER BY sub_category.id

Update on how can this be used in code, Try this,

$result = mysqli_query($con,"SELECT sub_category.id as s_id, sub_category.category as s_cat_name, p_category.category as p_cat_name FROM category_table sub_category INNER JOIN category_table p_category ON p_category.id = sub_category.parent_id ORDER BY sub_category.id");
    
$html = "<tr><th>Category Id</th><th>Category Name</th><th>Parent Category</th></tr>";

while ($row = mysqli_fetch_assoc($result)) {

            $html .= "<tr>";
            $html .= "<td>".$row['s_id']."</td>";
            $html .= "<td>".$row['s_cat_name']."</td>";
            $html .= "<td>".$row['p_cat_name ']."</td>";
            $html .= "</tr>";          
}
    
echo $html;

Upvotes: 1

Related Questions