Update: Based on Mike's inputs, a better version of my code can be found below (search for "better version", to skip the ugly version)
The dataset I obtain after performing a table join in the database is this:
RestaurantID RestaurantName OutletID OutletName Address Phone Latitude Longitude 1 Awesome Bar 1 Awesome Bar Roop Nagar 9837464231 0 0 1 Awesome Bar 2 Aww Bar Prem Bhavan 8461232871 0 0 2 Disco Deewaane 3 Disco Deewaane Gali Number 420 8372313874 0 0 2 Disco Deewaane 4 Decibel Disc Excuse Me Please 9833346521 0 0 3 Decibel Disco 5 Disc Dec Roop Nagar 8375643111 0 0 4 Basswaala Bar 6 Bass Down Low Prem Bhavan 9988843511 0 0 4 Basswaala Bar 7 Baby ka Bass Bar Gali Number 420 8883741234 0 0 4 Basswaala Bar 8 Basswaala Bar Excuse Me Please 9993741236,8843621439 0 0
The JSON string that I would like to have is this:
{
"status": "Success",
"data": [
{
"RestaurantID": "1",
"RestaurantName": "Awesome Bar",
"OutletData": [
{
"OutletID": "1",
"OutletName": "Awesome Bar",
"Address": "Roop Nagar",
"Phone": "9837464231",
"Latitude": "0",
"Longitude": "0"
},
{
"OutletID": "2",
"OutletName": "Aww Bar",
"Address": "Prem Bhavan",
"Phone": "8461232871",
"Latitude": "0",
"Longitude": "0"
}
]
},
{
"RestaurantID": "2",
"RestaurantName": "Disco Deewaane",
"OutletData": [
{
"OutletID": "3",
"OutletName": "Disco Deewaane",
"Address": "Gali Number 420",
"Phone": "8372313874",
"Latitude": "0",
"Longitude": "0"
},
{
"OutletID": "4",
"OutletName": "Decibel Disc",
"Address": "Excuse Me Please",
"Phone": "9833346521",
"Latitude": "0",
"Longitude": "0"
}
]
},
{
"RestaurantID": "3",
"RestaurantName": "Decibel Disco",
"OutletData": [
{
"OutletID": "5",
"OutletName": "Disc Dec",
"Address": "Roop Nagar",
"Phone": "8375643111",
"Latitude": "0",
"Longitude": "0"
}
]
},
{
"RestaurantID": "4",
"RestaurantName": "Basswaala Bar",
"OutletData": [
{
"OutletID": "6",
"OutletName": "Bass Down Low",
"Address": "Prem Bhavan",
"Phone": "9988843511",
"Latitude": "0",
"Longitude": "0"
},
{
"OutletID": "7",
"OutletName": "Baby ka Bass Bar",
"Address": "Gali Number 420",
"Phone": "8883741234",
"Latitude": "0",
"Longitude": "0"
},
{
"OutletID": "8",
"OutletName": "Basswaala Bar",
"Address": "Excuse Me Please",
"Phone": "9993741236,8843621439",
"Latitude": "0",
"Longitude": "0"
}
]
}
]
}
A brief description of the strategy used to achieve this (actual code is after bullet point 3):
I need an array of objects, as you can see at the highest level. So, I start by getting unique
restaurantID
s and creating an associative array for eachRestaurantID
. The data structure I get is this:Array ( [0] => Array ( [RestaurantID] => 1 [RestaurantName] => Awesome Bar ) [1] => Array ( [RestaurantID] => 2 [RestaurantName] => Disco Deewaane ) [2] => Array ( [RestaurantID] => 3 [RestaurantName] => Decibel Disco ) [3] => Array ( [RestaurantID] => 4 [RestaurantName] => Basswaala Bar ) )
Next, I consolidate all Outlet specific information relevant to each Restaurant in another associative array. The key of the array elements are the
RestaurantID
to which the Outlet is mapped in the original data. I end up with the following structure:Array ( [1] => Array ( [0] => Array ( [OutletID] => 1 [OutletName] => Awesome Bar [Address] => Roop Nagar [Phone] => 9837464231 [Latitude] => 0 [Longitude] => 0 ) [1] => Array ( [OutletID] => 2 [OutletName] => Aww Bar [Address] => Prem Bhavan [Phone] => 8461232871 [Latitude] => 0 [Longitude] => 0 ) ) [2] => Array ( [0] => Array ( [OutletID] => 3 [OutletName] => Disco Deewaane [Address] => Gali Number 420 [Phone] => 8372313874 [Latitude] => 0 [Longitude] => 0 ) [1] => Array ( [OutletID] => 4 [OutletName] => Decibel Disc [Address] => Excuse Me Please [Phone] => 9833346521 [Latitude] => 0 [Longitude] => 0 ) ) [3] => Array ( [0] => Array ( [OutletID] => 5 [OutletName] => Disc Dec [Address] => Roop Nagar [Phone] => 8375643111 [Latitude] => 0 [Longitude] => 0 ) ) [4] => Array ( [0] => Array ( [OutletID] => 6 [OutletName] => Bass Down Low [Address] => Prem Bhavan [Phone] => 9988843511 [Latitude] => 0 [Longitude] => 0 ) [1] => Array ( [OutletID] => 7 [OutletName] => Baby ka Bass Bar [Address] => Gali Number 420 [Phone] => 8883741234 [Latitude] => 0 [Longitude] => 0 ) [2] => Array ( [OutletID] => 8 [OutletName] => Basswaala Bar [Address] => Excuse Me Please [Phone] => 9993741236,8843621439 [Latitude] => 0 [Longitude] => 0 ) ) )
Finally, I loop through both data structures, checking whether
RestaurantID
in 1 == key of associative array in 2, and if yes, I append the associative array of 2 to the array in 1. I end up with the following data structure:Array ( [0] => Array ( [RestaurantID] => 1 [RestaurantName] => Awesome Bar [OutletData] => Array ( [0] => Array ( [OutletID] => 1 [OutletName] => Awesome Bar [Address] => Roop Nagar [Phone] => 9837464231 [Latitude] => 0 [Longitude] => 0 ) [1] => Array ( [OutletID] => 2 [OutletName] => Aww Bar [Address] => Prem Bhavan [Phone] => 8461232871 [Latitude] => 0 [Longitude] => 0 ) ) ) [1] => Array ( [RestaurantID] => 2 [RestaurantName] => Disco Deewaane [OutletData] => Array ( [0] => Array ( [OutletID] => 3 [OutletName] => Disco Deewaane [Address] => Gali Number 420 [Phone] => 8372313874 [Latitude] => 0 [Longitude] => 0 ) [1] => Array ( [OutletID] => 4 [OutletName] => Decibel Disc [Address] => Excuse Me Please [Phone] => 9833346521 [Latitude] => 0 [Longitude] => 0 ) ) ) [2] => Array ( [RestaurantID] => 3 [RestaurantName] => Decibel Disco [OutletData] => Array ( [0] => Array ( [OutletID] => 5 [OutletName] => Disc Dec [Address] => Roop Nagar [Phone] => 8375643111 [Latitude] => 0 [Longitude] => 0 ) ) ) [3] => Array ( [RestaurantID] => 4 [RestaurantName] => Basswaala Bar [OutletData] => Array ( [0] => Array ( [OutletID] => 6 [OutletName] => Bass Down Low [Address] => Prem Bhavan [Phone] => 9988843511 [Latitude] => 0 [Longitude] => 0 ) [1] => Array ( [OutletID] => 7 [OutletName] => Baby ka Bass Bar [Address] => Gali Number 420 [Phone] => 8883741234 [Latitude] => 0 [Longitude] => 0 ) [2] => Array ( [OutletID] => 8 [OutletName] => Basswaala Bar [Address] => Excuse Me Please [Phone] => 9993741236,8843621439 [Latitude] => 0 [Longitude] => 0 ) ) ) )
I came up with this code to go from the flat, spreadsheet-like data to a "normalized" JSON string (pretty sure that isn't the correct word to describe it):
private function norm_json($dbData){
//Wrangle data to fit in normalized json format
$data = array();
//Get all RestaurantIDs and remove duplicates.
$resID = array();
foreach($dbData as $row){
$resID[] = $row['RestaurantID'];
}
$resID_unique = array_unique($resID);
//Capture restaurantID and restaurantName information now:
foreach($resID_unique as $key => $value){
$data[] = array(
'RestaurantID' => $value,
'RestaurantName'=> $dbData[$key]['RestaurantName'],
);
}
//Capture the outlet specific data. After the following code, $outletData will be an array with keys = restaurantIDs and values = an array of outlet-specific info, 1 outlet's entire info per array entry.
$outletData = array();
foreach($dbData as $row){
//Bundle up outlet information in a nice little array.
$outletinfo = array(
'OutletID' => $row['OutletID'],
'OutletName' => $row['OutletName'],
'Address' => $row['Address'],
'Phone' => $row['Phone'],
'Latitude' => $row['Latitude'],
'Longitude' => $row['Longitude']
);
if(! isset($outletData[$row['RestaurantID']])){
//First time encountering this particular RestaurantID.
$outletData[$row['RestaurantID']] = array($outletinfo);
}
else{
//RestaurantID already encountered before. Append additional Outlet information.
$outletData[$row['RestaurantID']][] = $outletinfo;
}
}
//return $outletData;
//Embed $outletData in $data, by matching $outletData's keys with $data[]['RestaurantID']
foreach($outletData as $key => $outletInfo){
foreach($data as $index => $resObj){
//print_r($key); echo "\n"; print_r($outletInfo); echo "\n"; print_r($index); echo "\n"; print_r($resObj); echo "\n";
if($resObj['RestaurantID'] == $key){
$data[$index]['OutletData'] = $outletInfo;
}
}
}
return $data;
}
This seems really really messy. Is there a better way to achieve my goal in PHP? It would be great if I could do something equivalent to the R programming language's "Split, Apply, Combine" format. R split function example here.
Based on Mike's extremely helpful answer, I have refined my code and tested that it works like before:
Better version:
private function outlet_nested_datastructure_procedural($dataAsRows){
$nestedResult = array();
foreach($dataAsRows as $key => $value){
//If $nestedResult does not contain a key == $value['RestaurantID'], then make a new entry.
if(! isset($nestedResult[$value['RestaurantID']])){
$nestedResult[$value['RestaurantID']] = array(
'RestaurantID' => $value['RestaurantID'],
'RestaurantName' => $value['RestaurantName'],
'OutletData' => array(array(
'OutletID' => $value['OutletID'],
'OutletName'=> $value['OutletName'],
'Address' => $value['Address'],
'Phone' => $value['Phone'],
'Latitude' => $value['Latitude'],
'Longitude' => $value['Longitude']))
);
}
else{
$nestedResult[$value['RestaurantID']]['OutletData'][] = array(
'OutletID' => $value['OutletID'],
'OutletName'=> $value['OutletName'],
'Address' => $value['Address'],
'Phone' => $value['Phone'],
'Latitude' => $value['Latitude'],
'Longitude' => $value['Longitude']);
}
}
return $nestedResult;
}
As can be seen, there is a marked improvement in readability, and efficiency. This is code you can take home to your mother. :)
What next?
Next steps... I discovered the functions array_map and array_reduce. I am trying to figure out how to use those functions to achieve the same result. Basically, looking to make the code functional in style, rather than procedural.