1
\$\begingroup\$

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):

  1. I need an array of objects, as you can see at the highest level. So, I start by getting unique restaurantIDs and creating an associative array for each RestaurantID. 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
    )
)

  2. 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
            )

    )
)

  3. 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.

Improve this question
\$\endgroup\$
5
  • \$\begingroup\$ Does code yield the expected output? We can't review broken code. See the How to ask good questions help page at codereview.stackexchange.com/help/how-to-ask \$\endgroup\$
    – pacmaninbw
    Aug 23, 2016 at 21:57
  • \$\begingroup\$ Yes, the code works. What I have done is convert the flat file into a nested array-object data structure (as it should be, to be represented as the desired JSON string). My question is whether my approach can be improved. Right now, the code seems really ugly. \$\endgroup\$
    – Chaos
    Aug 24, 2016 at 2:46
  • \$\begingroup\$ What database are you using? Good databases these days can just give you the result as JSON. \$\endgroup\$
    – 200_success
    Aug 24, 2016 at 5:58
  • \$\begingroup\$ I am using MySQL (version 14.14 Distribution 5.5.50), unfortunately. I searched around a bit and it seems that MySQL (from version 5.7) offers JSON as a column data-type, but the quick search that I did didn't show any way of returning query result as JSON. \$\endgroup\$
    – Chaos
    Aug 24, 2016 at 8:25
  • \$\begingroup\$ BTW, what is the technical term for what I am trying to achieve? I used the term "normalized JSON", but I don't think that's right. Maybe it's "converting flat data into nested JSON"? Seems a bit of a mouthful. \$\endgroup\$
    – Chaos
    Aug 24, 2016 at 8:26

1 Answer 1

Reset to default
1
\$\begingroup\$

My main suggestion would be to build your final data structure in a single loop. There is really no reason to have to iterate over the DB result set more than once. This might look something like this:

private function norm_json($dbData){
    $return_data = array();

    // variable to keep track of current restaurant id as we iterate result set
    $current_rest_id = null;
    $i = -1;

    // iterate through provided outlet records
    foreach($dbData as $outlet) {
        $outlet_rest_id = $outlet['RestaurantID'];
        if($outlet_rest_id !== $current_rest_id) {
            // we have a new restaurant
            $current_rest_id = $outlet_rest_id;
            $i++;
            // start new restaurant record
            $return_data[$i] = array(
                'RestaurantID' => $outlet_rest_id,
                'RestaurantName' => $outlet['RestaurantName']
                'OutletData' => array()
            );
        }

        // discard restaurant fields from record as we don't need them anymore
        unset($outlet['RestaurantID']);
        unset($outlet['RestaurantName'];

        // add remaining array to outlet data
        $return_data[$i]['OutletData'][] = $outlet;
    }

    return $return_data;
}

This sort of approach is very common when you need to read a "flattened" database result set into a multidimensional array or other hierarchical data structure. Note that this approach would require that your result set be sorted by the restaurant id (as is shown in your example).

I would also suggest that if memory usage is a concern for you (i.e. at some point you are going to be dealing with a large number of rows), that you may not want to read out the entire DB result set into an array before passing it to your function. You are, in essence, having to store all the relevant data in memory twice with this approach. Of course, this means you would need to restructure your function call a bit to take a single row at a time and return an appropriate outlet object/array. I won't digress any more into this approach here, but it is something to think about.

A few other code comments:

  • This method name seems odd. You don't do any JSON encoding in this method, so why call it something with "json" in the name? Perhaps something like map_db_result_to_multidimensional_array? It also seems odd to use snake case method name when you are using camel case throughout. Try to be consistent with style for your user-specified code elements.
  • $data is a pretty non-specific variable name here. Perhaps $return_data or similar could be used so that it is immediately clear to reader what this variable is for.
  • Consider using all lower-case snake-case for your DB field names (which are also reflected in your key names here). Typically database field names are not case-sensitive anyway so many just prefer to stick to lower case to avoid any potential confusion.
Improve this answer
\$\endgroup\$
1
  • \$\begingroup\$ Thank you for your help, Mike. :) Based on your advice, I was able to refactor my code better. Yes, if there are thousands of rows in the return data, then a better strategy would be to apply some "pagination" method. Get estimated number of rows from DB, then use LIMIT command to get data in chunks (example, 1000 rows can be broken into 50 chunks of 20 rows), all the while running the outlet_nested_datastructure_procedural function to normalize data. I don't need that yet, given the scale of data, but will need to write that once the dataset becomes bigger. \$\endgroup\$
    – Chaos
    Aug 30, 2016 at 6:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.