I have 2 MySQL tables, where I extract data with following SQL statements:
Categories: SELECT cat_id, cat_name FROM categories
Products: SELECT pro_id, pro_name, pro_category FROM products
Of course, pro_category
is foreign key for cat_id
I'd like to reach the following JSON data:
"categories":[
{
"id": 1,
"name": "Guitars",
"products":[
{
"id": 1,
"name": "Fender Statocaster"
},
{
"id": 2,
"name": "Gibson Les Paul"
}
]
},
{
"id": 2,
"name": "Basses",
"products":[
{
"id": 3,
"name": "Fender Jazz Bass"
},
{
"id": 4,
"name": "MusicMan StingRay"
}
]
}
]
So I have to do an array of categories and, for each element, a sub-array of products (then I'll go with json_encode()
).
Looping through categories and create the first array is quite easy, but I'm totally stuck on how to create the products sub-array... Here's my code, and the question marks are the point where I stopped because I don't know what to do:
$categories = array();
while ($row_rsCategories = mysql_fetch_assoc($rsCategories)) {
$categories[] = array(
'id' => $row_rsCategories['cat_id'],
'name' => $row_rsCategories['cat_name'],
'products' => array(
???????
),
);
}
Please, can you help?
Thanks!