php JSON data structure for angular app

Question

I am trying to format the a mysql response into a array of objects so that Angular can easily traverse the data.

mysql result:

[{
    "FIAccountsID": "99",
    "AccountName": "",
    "AccountCustomName": "Brothers",
    "AccountNumber": "99-123123123",
    "AccountTypeName": "IRA",
    "FinancialInstName": "Testes",
    "FinancialInstID": "9",
    "UserID": "1",
    "ofxStatusCode": "500",
    "UserAccountID": "09128-vc-12",
    "FirstName": "Someones",
    "LastName": "Name",
    "Status": "1"
},
{
    "FIAccountsID": "99",
    "AccountName": "",
    "AccountCustomName": "Brothers",
    "AccountNumber": "99-123123123",
    "AccountTypeName": "IRA",
    "FinancialInstName": "Testes",
    "FinancialInstID": "9",
    "UserID": "1",
    "ofxStatusCode": "500",
    "UserAccountID": "09128-vc-12",
    "FirstName": "Someones",
    "LastName": "Name",
    "Status": "2"
},
{
    "FIAccountsID": "100",
    "AccountName": "",
    "AccountCustomName": "Brothers",
    "AccountNumber": "99-123123123",
    "AccountTypeName": "IRA",
    "FinancialInstName": "Testes",
    "FinancialInstID": "9",
    "UserID": "1",
    "ofxStatusCode": "500",
    "UserAccountID": "09128-vc-12",
    "FirstName": "Someones",
    "LastName": "Name",
    "Status": "1"
},
{
    "FIAccountsID": "100",
    "AccountName": "",
    "AccountCustomName": "Brothers",
    "AccountNumber": "99-123123123",
    "AccountTypeName": "IRA",
    "FinancialInstName": "Testes",
    "FinancialInstID": "9",
    "UserID": "1",
    "ofxStatusCode": "500",
    "UserAccountID": "09128-vc-12",
    "FirstName": "Someones",
    "LastName": "Name",
    "Status": "2"
},
{
    "FIAccountsID": "101",
    "AccountName": "",
    "AccountCustomName": "Brothers",
    "AccountNumber": "99-123123123",
    "AccountTypeName": "IRA",
    "FinancialInstName": "Testes",
    "FinancialInstID": "9",
    "UserID": "1",
    "ofxStatusCode": "500",
    "UserAccountID": "09128-vc-12",
    "FirstName": "Someones",
    "LastName": "Name",
    "Status": "1"
},
{
    "FIAccountsID": "101",
    "AccountName": "",
    "AccountCustomName": "Brothers",
    "AccountNumber": "99-123123123",
    "AccountTypeName": "IRA",
    "FinancialInstName": "Testes",
    "FinancialInstID": "9",
    "UserID": "1",
    "ofxStatusCode": "500",
    "UserAccountID": "09128-vc-12",
    "FirstName": "Someones",
    "LastName": "Name",
    "Status": "2"
},
{
    "FIAccountsID": "102",
    "AccountName": "",
    "AccountCustomName": "Brothers",
    "AccountNumber": "99-123123123",
    "AccountTypeName": "IRA",
    "FinancialInstName": "Testes",
    "FinancialInstID": "9",
    "UserID": "1",
    "ofxStatusCode": "500",
    "UserAccountID": "09128-vc-12",
    "FirstName": "Someones",
    "LastName": "Name",
    "Status": "1"
},
{
    "FIAccountsID": "102",
    "AccountName": "",
    "AccountCustomName": "Brothers",
    "AccountNumber": "99-123123123",
    "AccountTypeName": "IRA",
    "FinancialInstName": "Testes",
    "FinancialInstID": "9",
    "UserID": "1",
    "ofxStatusCode": "500",
    "UserAccountID": "09128-vc-12",
    "FirstName": "Someones",
    "LastName": "Name",
    "Status": "2"
},
{
    "FIAccountsID": "103",
    "AccountName": "",
    "AccountCustomName": "Brothers",
    "AccountNumber": "99-123123123",
    "AccountTypeName": "IRA",
    "FinancialInstName": "Testes",
    "FinancialInstID": "9",
    "UserID": "1",
    "ofxStatusCode": "500",
    "UserAccountID": "09128-vc-12",
    "FirstName": "Someones",
    "LastName": "Name",
    "Status": "1"
},
{
    "FIAccountsID": "103",
    "AccountName": "",
    "AccountCustomName": "Brothers",
    "AccountNumber": "99-123123123",
    "AccountTypeName": "IRA",
    "FinancialInstName": "Testes",
    "FinancialInstID": "9",
    "UserID": "1",
    "ofxStatusCode": "500",
    "UserAccountID": "09128-vc-12",
    "FirstName": "Someones",
    "LastName": "Name",
    "Status": "2"
}......

here it the PHP:

while($row = mysqli_fetch_assoc($result))   
{ 

  $uid = $row['UserID'];
  $name = $row['FirstName'].' '.$row['LastName'];
  $fiid = $row['FinancialInstID'];
  $fi = $row['FinancialInstName'];
  $acctID = $row['FIAccountsID'];


  $rows[$uid]['name'] = $name;
  $rows[$uid]['uid'] = $uid;
  $rows[$uid]['fi'][$fiid]['name'] = $fi;
  $rows[$uid]['fi'][$fiid]['acct'][$acctID]['name'] = $row['AccountCustomName'];

}

print json_encode($rows);

this is what i get:

{
"1": {
    "name": "Some Name",
    "uid": "1",
    "fi": {
        "9": {
            "name": "Testes",
            "accts": {
                "99": {
                    "name": "name 1"
                },
                "100": {
                    "name": "name 2"
                },
                "103": {
                    "name": "name 3"
                }
            }
        }
    }
},
"2": {
    "name": "Another Name",
    "uid": "2",
    "fi": {
        "7": {
            "name": "Trevor's Brokerage House",
            "accts": {
                "1": {
                    "name": "Sally's 401k Account"
                },
                "2": {
                    "name": "retirement"
                },
                "3": {
                    "name": "Some other account"
..........

desired result:

[
  {
    "users": [
        {
            "name": "Some Name",
            "uid": "1",
            "fi": [
                {
                    "name": "Testes",
                    "acct": [
                        {
                            "name": "name 1"
                        },
                        {
                            "name": "name 2"
                        },
                        {
                            "name": "name 3"
                        }
                    ]
                }
            ]
        },
        {
            "name": "Another Name",
            "uid": "2",
            "fi": [
                {
                    "name": "Trevor's Brokerage House",
                    "acct": [
                        {
                            "name": "Sally's 401k Account"
                        },
                        {
                            "name": "retirement"
                        },
                        {
                            "name": "Some other account"
..........

I want to know how to have the data structure without the unique keys prefixing the nested arrays

$json = array('users'=>array(array('name'=>'name one', ...), array('name'=> 'name two'), ...)); — random_user_name
– random_user_name, Commented Oct 4, 2013 at 2:43
the problem is that each row in the result set writes over the last if I do it that way? — user2762149
– user2762149, Commented Oct 4, 2013 at 2:47

George Marques · Accepted Answer · 2013-10-04 05:46:41Z

0

The thing is that if you define keys, json_encode will read them. If you don't want keys, declare the array without them. So, what you need to do is to structure your array according to you desired output.

But the way your data is arranged make it somewhat hard to format it properly. The solution I can see is to do what you're already doing:

while($row = mysqli_fetch_assoc($result))   
{ 

  $uid = $row['UserID'];
  $name = $row['FirstName'].' '.$row['LastName'];
  $fiid = $row['FinancialInstID'];
  $fi = $row['FinancialInstName'];
  $acctID = $row['FIAccountsID'];


  $rows[$uid]['name'] = $name;
  $rows[$uid]['uid'] = $uid;
  $rows[$uid]['fi'][$fiid]['name'] = $fi;
  $rows[$uid]['fi'][$fiid]['acct'][$acctID]['name'] = $row['AccountCustomName'];

}

But then you revisit the array and remove all the keys using array_values:

foreach ($rows as $uid => $data)
{
    foreach ($data['fi'] as $fiid => $fi)
    {
        $rows[$uid]['fi'][$fiid]['acct'] = array_values($fi['acct']);
    }
    $rows[$uid]['fi'] = array_values($rows[$uid]['fi']);
}
// Here you format the outer array:
$rows = array(array('users' => array_values($rows)));

Which gives a result like this:

[
    {
        "users": [
            {
                "name": "Someones Name",
                "uid": "1",
                "fi": [
                    {
                        "name": "Testes",
                        "acct": [
                            {
                                "name": "Brothers"
                            },
                            {
                                "name": "Brothers"
                            },
                            {
                                "name": "Brothers"
                            },
                            {
                                "name": "Brothers"
                            },
                            {
                                "name": "Brothers"
                            }
                        ]
                    }
                ]
            },
            {
                "name": "Someones Name",
                "uid": "2",
                "fi": [
                    {
                        "name": "Testes",
                        "acct": [
                            {
                                "name": "Brothers"
                            },
                            {
                                "name": "Brothers"
                            }
                        ]
                    }
                ]
            }
        ]
    }
]

edited Oct 4, 2013 at 5:46

answered Oct 4, 2013 at 3:29

George Marques

8757 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

user2762149 Over a year ago

doesn't allow for multiple users?

George Marques Over a year ago

What do you mean by "multiple users"? This way it'll have a "users" key which value is an array of users, like the example you gave.

user2762149 Over a year ago

i already have a complete working output with unique keys (it is in the original post). I want to know how to have the data structure without the unique keys prefixing the nested arays

George Marques Over a year ago

@user2762149 I edited the answer, see if it is what you want (also, a sample of your desired output would be good, I don't know why you removed from the question).

user2762149 Over a year ago

i felt like it was confusing the question. the format i have in the question is what I want EXCEPT i don't what the indexes prefixing the nested arrays. the code you posted is the same as before?

|

Collectives™ on Stack Overflow

php JSON data structure for angular app

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related