An easy way to turn MySQL data to PHP object/assoc array?

Question

Is there an easy way to query MySQL data and convert the data into a PHP data-only object or a PHP assoc array? Are there already exisiting frameworks/libraries that do so?

Before everyone jumps to the answer, I have to clarify - NO, I am not talking about MySQLi's fetch_object or fetch_assoc, or anything similar like those.

I am talking about querying multiple tables and having the data stored in objects/assoc arrays in a hierarchical way.

For example, if I have MySQL tables like the followings:

Table `Person`
id | name
1  | John
2  | Mary
3  | Sue
4  | Peter
5  | David

Table `IsMarriedTo`
personId1 | personId2
1         | 2
2         | 1
3         | 5
5         | 3

Table `HasChild`
personId1 | personId2
1         | 3
2         | 3
2         | 4

Well, let's say I want to query about John and all his related family members. Ultimately, I would like to have a PHP assoc array like this:

$person = array(
  'id' => 1,
  'name' => 'John',
  'IsMarriedTo' => array(
    array(
      'id' => 2,
      'name' => 'Mary',
      'HasChild' => array(
        array(
          'id' => 3,
          'name' => 'Sue',
          'IsMarriedTo' => array(
            array(
              array(
                'id' => 5,
                'name' => 'David',
                'IsMarriedTo' => array(),
                'HasChild' => array()
              )
            )
          ),
          'HasChild' => array()
        ),
        array(
          'id' => 4,
          'name' => 'Peter',
          'IsMarriedTo' => array(),
          'HasChild' => array()
        )
      )
    )
  ),
  'HasChild' => array(
    array(
      'id' => 3,
      'name' => 'Sue',
      'IsMarriedTo' => array(
        array(
          array(
            'id' => 5,
            'name' => 'David',
            'IsMarriedTo' => array(),
            'HasChild' => array()
          )
        )
      ),
      'HasChild' => array()
    )
  )
);

If you ask me to write SQL queries for this example, hmmm...yes, I konw how to write them. But I just want to know if there is an easier/non-manual way to query for the data and convert the data into a PHP data-only object or a PHP assoc array. It would be better if there are already exisiting frameworks/libraries that do so.

In real world applications, table relationships are not so simple like the example above. Well, database schemas may not be necessarily very complex either, but sometiems, querying for one simple thing will involve too many tables, and manually rebuilding the data object or array in PHP from database data is very repetitive and error-prone.

Even if there may not be simple non-manual alternative methods out there for the example above, but sometimes I wonder: do anyone feel and agree that getting database data and rebuilding the PHP objects/arrays is just a repetitive chore? Tens, if not hundreds, of new queries are written everyday. They may be different from one another but their pattern are more or less the same. With PHP and MySQL being such mature technologies for so long, surely there must be some kind of frameworks/libraries that do these kind of repetitive things already. Don't your think so?

EDIT

Some people has flagged this as duplicate. I am not sure if this question is really a duplicate. May be it is, but still... if you think it is, can you at least be kind and provide some links to other similar questions with answers?

Please note that I am not just talking about a problem easily solved by ORMs here though. I am talking about hierarchical data structure on PHP built from multiple database tables.... and there may be access loops involved if not carefully handled. Can ORMs easily tackle this? If so, please show me some light and show me links of other similar questions that have been answered.

I am not an expert in ORMs but I really doubt if they can easily handle such.

Have you looked into [ORMs](https://en.wikipedia.org/wiki/Object-relational_mapping)/[DALs](https://en.wikipedia.org/wiki/Data_access_layer)…? — deceze, May 13 '17 at 07:04
These days, most people seem to want json- so a little odd that you don't — Strawberry, May 13 '17 at 07:04
I can't add an answer, since this was flagged as a dupe, but would it not be a simple RIGHT JOIN on the query? — Joe, May 13 '17 at 08:31
@deceze Well, yes and no... I have read about ORMs, although I have not used it before. However... I don't know if you have thoroughly looked at my example above. I really doubt if ORMs can build a hierarchical data structure like that from multiple tables. May be ORMs can easy do it, but let's not forget there is a possiblity of looping, making ORMs accessng the same table again and again multiple times. I doubt if ORMs are smart enough to avoid that. See example above. — user2526586, May 19 '17 at 04:35
@Joe Yes, I am sorry. I would really want to hear from you too. I, too, want to know how you would do it simply with JOINs. From what I see, there are multiple obstacles with using JOINs. Just to list a few: 1 - For a tree data structure, when a child table joins to a parent table, you get a result of rows, with parent and child data on the same row, but the result consists of duplicated data on the parent fields if the parents have multiple children. You would need to go through the trouble to manually filter out when building the data structure on PHP; — user2526586, May 19 '17 at 04:41
@Joe 2 - For a tree data structure, after you use JOINs to get all parent-child fields on each row, you would need to use PHP loop and trace back all the parent-child relationships, child-grandchild relationships and so on... complexity wise, N^2 at least? or N^3? I don't know. May be more. I haven't thought deep enough; 3 - if I just want one tree structure starting from one particular node like the above example, should I still JOIN all parent-child relationship in the first place? all the nodes of that tree may consists of less than 1% of all the records. Isn't that too overkilled? — user2526586, May 19 '17 at 04:49

An easy way to turn MySQL data to PHP object/assoc array?

0 Answers0