title: Php Mysql Nested Category Subcategories Json Response
body: <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>