I have a MySQL table containing data in the following form:
set_id ID of a set of items
country country of origin of the set
year year of origin of the set
item_id ID of an item belonging to a set
price price of an item belonging to a set
color color of an item of a set
So imagine this sample data in MySQL:
SET ITEM
ID COUNTRY YEAR ID PRICE COLOR
-- ------- ---- ---- ----- -----
1 USA 1872 1 $10 blue
1 USA 1872 2 $20 red
1 USA 1872 3 $25 green
2 Mexico 1902 4 $1 yellow
2 Mexico 1902 5 $2 orange
3 Canada 1920 6 $5 violet
I wish to create JSON output (or output that can be converted to JSON using PHP) that looks like ...
In "natural" form, these three items would look like this:
SET 1 -> {"set_id":"1","country":"USA","year":"1872","items":[{"item_id":"1","price":"$10","color":"blue",},{"item_id":"2","price":"$20","color":"red",},{"item_id":"3","price":"$25","color":"green",}]}
SET 2 -> {"set_id":"2","country":"Mexico","year":"1902","items":[{"item_id":"4","price":"$1","color":"yellow",},{"item_id":"5","price":"$2","color":"orange",}]}
SET 3 -> {"set_id":"3","country":"Canada","year":"1920","items":[{"item_id":"6","price":"$5","color":"violet",}]}
But for readability, I present the JSON as this:
SET #1
{
"set_id":"1",
"country":"USA",
"year":"1872",
"items" : [
{
"item_id":"1",
"price":"$10",
"color":"blue",
},
{
"item_id":"2",
"price":"$20",
"color":"red",
},
{
"item_id":"3",
"price":"$25",
"color":"green",
}
]
}
SET #2
{
"set_id":"2",
"country":"Mexico",
"year":"1902",
"items" : [
{
"item_id":"4",
"price":"$1",
"color":"yellow",
},
{
"item_id":"5",
"price":"$2",
"color":"orange",
}
]
}
SET #3
{
"set_id":"3",
"country":"Canada",
"year":"1920",
"items" : [
{
"item_id":"6",
"price":"$5",
"color":"violet",
}
]
}
So ... how can I fetch from MySQL and produce this JSON output?
Constraints: - MySQL database fetch will be done in PHP using PDO methods. - Output will be one JSON text for each set_id.
