Php Mysql Nested Category Subcategories Json Response
<p>I would like to display categories and subcategories as json response using php mysql. I have three level categories. I am running this query but results not coming as expected. please help me to solve this.</p>

<p>I need json response like below</p>

<pre><code>[{
    "id": "1",
    "name": "Electronics",
    "categorieslevelone": [{
        "id": "2",
        "name": "Mobiles",
        "categoriesleveltwo": [{
            "id": "3",
            "name": "Samsung",
            "parent_id": "2"
        }, {
            "id": "4",
            "name": "Nokia",
            "parent_id": "2"
        }]
    }]
</code></pre>

<p>My Code:</p>

<pre><code>header('Content-Type: application/json');
        $sql = "select * from category where category_id = 0";
        $q = $this-&gt;db-&gt;conn_id-&gt;prepare($sql);
        $q-&gt;execute();
        $json_response = array();
        while ($row = $q-&gt;fetch(PDO::FETCH_ASSOC)) {
            $row_array = array();
            $row_array['id'] = $row['id'];
            $row_array['name'] = $row['name'];
            $row_array['categorieslevelone'] = array();
            $id = $row['id'];
            $sqltwo = "select * from category where category_id = ? ";
            $r = $this-&gt;db-&gt;conn_id-&gt;prepare($sqltwo);
            $r-&gt;bindParam(1, $id);
            $r-&gt;execute();
            while ($data = $r-&gt;fetch(PDO::FETCH_ASSOC)) {
                $id2 = $data['id'];
                $row_array['categoriesleveltwo'] = array();
                $row_array['categorieslevelone'][] = array(
                    'id' =&gt; $data['id'],
                    'name' =&gt; $data['name'],
                );
                $sql3 = "select * from category where category_id = ? ";
                $s = $this-&gt;db-&gt;conn_id-&gt;prepare($sql3);
                $s-&gt;bindParam(1, $id2);
                $s-&gt;execute();
                while ($list = $s-&gt;fetch(PDO::FETCH_ASSOC)) {
                    $row_array['categoriesleveltwo'][] = array(
                        'id' =&gt; $list['id'],
                        'name' =&gt; $list['name'],
                    );
                }
            }
            array_push($json_response, $row_array); //push the values in the array
        }
        echo json_encode($json_response);
</code></pre>