CODEWITHSUNDEEP
phpmysql
jack brone
jack brone

Reputation: 205

Categories Multi Level display using php and mysql in json format

I need to display categories and its sub categories in json format, here problem is each category has unlimited levels, some categories has 4 levels and some 2 and some 3 levels. I have tried one solution it is showing only one under level, but I need to show all levels.

Table schema: enter image description here

Code Example:

function categories() {
        header('Content-Type: application/json');
        $sql = "select id,name,parent_id from categories where parent_id = 0";
        $q = $this->db->conn_id->prepare($sql);
        $q->execute();
        $main_cat = array();
        while ($row = $q->fetch(PDO::FETCH_ASSOC)) {
            $sql2 = "select id,name,parent_id from categories where parent_id = ?";
            $sub_cat = array();
            $r = $this->db->conn_id->prepare($sql2);
            $r->bindParam(1, $row['id']);
            $r->execute();
            while ($row1 = $r->fetch(PDO::FETCH_ASSOC)) {
                array_push($sub_cat, array_filter($row1));
            }
            $row['subcategories'] = $sub_cat;
            array_push($main_cat, array_filter($row));
        }
        echo json_encode(array("categories" => $main_cat));
    }

Above Code Json Response Example:

{"categories":[{"id":"1","name":"Development","subcategories":[{"id":"2","name":"All Development","parent_id":"1"},{"id":"3","name":"Web Development","parent_id":"1"},{"id":"12","name":"Mobile Apps","parent_id":"1"}]},{"id":"4","name":"Design","subcategories":[{"id":"5","name":"All Design","parent_id":"4"}]},{"id":"11","name":"IT & Software"}]}

Any one can help me how to solve this issue.

Upvotes: 0

Views: 804

Answers (1)

jack brone
jack brone

Reputation: 205

I have tried this solution works for me.

function categories() {
        $sql = "SELECT * FROM categories where parent_id = 0";
        $results = $this->db->conn_id->prepare($sql);
        $results->execute();
        while ($result = $results->fetch(PDO::FETCH_ASSOC)) {
            $subcat = array();
            $id = $result['id'];
            $childs = $this->hasChilds($id);
            $categories[] = array("id" => $result['id'], "name" => $result['name'], "parent_id" => $result['parent_id'], "subcats" => array_filter($childs));
        }
        echo json_encode($categories);
    }

    function hasChilds($id) {
        $sql = "SELECT * FROM categories where parent_id = ? ";
        $results = $this->db->conn_id->prepare($sql);
        $results->bindParam(1, $id);
        $results->execute();
        $count = $results->rowCount();
        $array = array();
        if ($count > 0) {
            while ($result = $results->fetch(PDO::FETCH_ASSOC)) {
                $array[] = array("id" => $result['id'], "name" => $result['name'], "parent_id" => $result['parent_id'], "subcats" => array_filter($this->hasChilds($result['id'])));
            }
        } else {
            $array[] = null;
        }
        return $array;
    }

Upvotes: 1

Related Questions