I have database with structure of category and subcategory:
category
:
subcategory
:
I want JSON via PHP in required format. I have tried most time but it didn't work.
{
"success":"true",
"category_1":{
"cat_id":"1",
"cat_name":"menu1",
"cat_img":"cat1.jpg",
"sub_category":[
{
"sub_id":"1",
"sub_name":"sub name"
},
{
"sub_id1":"2",
"sub_name1":"sub name"
},
{
"sub_id2":"3",
"sub_name2":"sub name"
},
{
"sub_id3":"4",
"sub_name3":"sub name"
}
]
},
"category_2":{
"cat_id":"2",
"cat_name":"menu2",
"cat_img":"cat2.jpg",
"sub_category":[
{
"sub_id":"1",
"sub_name":"sub name"
},
{
"sub_id1":"2",
"sub_name1":"sub name"
}
]
},
"category_3":{
"cat_id":"3",
"cat_name":"menu3",
"cat_img":"cat3.jpg",
"sub_category":[
{
"sub_id":"1",
"sub_name":"sub name"
},
{
"sub_id1":"2",
"sub_name1":"sub name"
},
{
"sub_id3":"",
"sub_name1":"sub name"
}
]
}
}
I have tried this code, but it didn't work. I would prefer a PDO solution.
$servername = "localhost";
$username = "res_user";
$password = "Res@123";
$dbname = "res_db";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
$sql = "SELECT *, category.id AS catId FROM category INNER JOIN sub_category ON category.id = sub_category.category_id ORDER BY category.id, sub_category.category_id";
$res = mysqli_query($conn, $sql);
$categoryArray = array();
$oldCatId = 0;
while ($row = mysqli_fetch_assoc($res)) {
if ($row['catId'] != $oldCatId) {
$categoryArray[$row['catId']] = array(
'success' => true,
'category_' . $row['catId'] = array(
'cat_id' => $row["catId"],
'cat_name' => $row['cat_name'],
'cat_img' => $row['cat_img'],
'sub_category' => array(
'sub_id' => $row['category_id'],
'sub_name' => $row['sub_name']
)
)
);
$oldCatId = $row['catId'];
} else {
$categoryArray[$row['catId']]['category_' . $row['catId']]['sub_category'][] = array(
'sub_id' => $row['category_id'],
'sub_name' => $row['sub_name']
);
}
}
echo json_encode($categoryArray);