jack brone jack brone - 5 months ago 197
MySQL Question

Php Mysql Nested Category Subcategories Json Response

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.

I need json response like below

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


My Code:

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

Answer

I'm getting a result like you mentioned above. Chenck this out. You have to convert procedural into Object oriented method.

$main_cat = array();
while($row = mysqli_fetch_assoc($query))
{
    $query2 = mysqli_query($con,'Select category_id as id,category_name as name from category where parent_id = '.$row['id'].'');
    $sub_cat = array();
    while($row1 = mysqli_fetch_assoc($query2))
    {
        $query3 = mysqli_query($con,'Select category_id as id,category_name as name from category where parent_id = '.$row1['id'].'');
        $sub_cat2 = array();
        while($row2 = mysqli_fetch_assoc($query3))
        {
            array_push($sub_cat2, $row2);   
        }
        $row1['categoriesleveltwo'] = $sub_cat2;
        array_push($sub_cat, $row1);    
    }

    $row['categorieslevelone'] = $sub_cat;
    array_push($main_cat, $row);
}

echo json_encode($main_cat);
?>

Hope its will satisfy your requirement