SQL Joined data into multidimensional array

Question

I have been on this problem for a while now an have a manual process that I do to build the data into the array format I want, but I'm finding it time consuming and very easy to break.

So I am trying to automate the following task as best I can.

As an example, I have 2 tables in my data base, a users table and an addresses table.

The users table has a one-to-many relationship with the addresses table.

I pull down my data using a query to join the tables and pull down every row, example query:

SELECT users.name AS 'users.name',
       users.age AS 'users.age',
       addresses.street AS 'users.addresses.street',
       addresses.suburb AS 'users.addresses.suburb'

    JOIN addresses ON 
    users.user_id = addresses.user_id

FROM users;

If I have 2 users one with 2 addresses and one with 3 it should return 5 lines like so:

|users.name|users.age|users.addresses.street|users.addresses.suburb|

Joshua | 28 | 3 Fake St | Sydney

Joshua | 28 | 16 New St | Sydney

Joshua | 28 | 140 Apple St | Sydney

Tim | 34 | 18 John St | Sydney

Tim | 34 | 143 Clark St | Sydney

Fetching the results as an associative array would return the results in an array like the following:

array( 
    'users.name' => 'Joshua',
    'users.age' => 28,
    'users.addresses.street' => '3 Fake St',
    'users.addresses.state' => 'Sydney'
),
array( 
    'users.name' => 'Joshua',
    'users.age' => 28,
    'users.addresses.street' => '16 New St',
    'users.addresses.state' => 'Sydney'
),
array( 
    'users.name' => 'Joshua',
    'users.age' => 28,
    'users.addresses.street' => '140 Apple St',
    'users.addresses.state' => 'Sydney'
),
array( 
    'users.name' => 'Tim',
    'users.age' => 34,
    'users.addresses.street' => '18 John St',
    'users.addresses.state' => 'Sydney'
),
array( 
    'users.name' => 'Tim',
    'users.age' => 34,
    'users.addresses.street' => '143 Clark St',
    'users.addresses.state' => 'Sydney'
)

What I then want to do with the above array is turn each result line into a multidimensional array like so:

array (
    'users' => array(
        'name' = > 'Joshua',
        'age' => 28,
        'addresses' => 
            array(
                'street' =>  '3 Fake St',
                'state' => 'Sydney'      
            ),
            array(
                'street' =>  '16 New St',
                'state' => 'Sydney'      
            ),
            array(
                'street' =>  '140 Apple St',
                'state' => 'Sydney'      
            )
    ),
),
array (
    'users' => array(
        'name' = > 'Tim',
        'age' => 44,
        'addresses'' => 
            array(
                'street' =>  '18 John St',
                'state' => 'Sydney'      
            ),
            array(
                'street' =>  '143 Clark St',
                'state' => 'Melborune'      
            )
    ),
)

As you can see each period in the array key of the database returns should be exploded, then turned into the multidimensional array, and the last exploded key should be the key value.

I have done this using array fetch and some crazy checking that gets only certain key names and puts them into an array then I'm using array_map to build the array structure... But its quite tedious for large data sets and having to write out every relationship manually and mapping it all is silly I think.

I have been attempting to build out something that fits my description above but with no luck, hence why I am now here.

NOTE

The above is an example for one set of a data, I am trying to create this so it can easily be done with multiple sets of data and not having to write a loop and manually create the multi dimensional array out.

Well I was playing around with it and trying to use the aliases to help with building the array associations. I'm totally open to suggestions. I just have a feeling I'm missing a vital part of the puzzle that I missing. Hoping someone can spark some ideas or clues that I'm missing or over/under thinking — Joshua Lawson
– Joshua Lawson, Commented Nov 4, 2016 at 13:02
Well - it's just a simple PHP loop. I'd write out a solution, but I'm actually terrible at manipulating arrays in PHP - but there are thousands of examples out there. But I wouldn't include '.' in an alias. I think it's just asking for trouble, — Strawberry
– Strawberry, Commented Nov 4, 2016 at 13:03
I have an issue with your edit... now... lets says I'm using this with some other data models... and for some reason... they have similar names for columns for different tables... also, now you have lost the associations because you have removed the aliases I was using... You have gone back to a MANUAL way of building this array from the data... I am trying to find a pattern to use this with different sets of data. So that I don't have to write a "Simple loop" every time for different sets of data... I want it to be extensible and easily swappable — Joshua Lawson
– Joshua Lawson, Commented Nov 4, 2016 at 13:05
I'm not best qualified to say, but I don't think any part of that argument holds water. — Strawberry
– Strawberry, Commented Nov 4, 2016 at 13:41

galchen · Accepted Answer · 2016-11-04 15:37:36Z

1

You can do something more generic

$records = array(
    array(
        'user.name' => 'Joshua',
        ....
    ),
);


$result = [
    'users' => []
];

foreach ( $records as $r ) {
    $new_record = [];
    foreach ( $r as $k => $v ) {
        $parsed = explode('.', $k);
        $cur    = &$new_record;
        for ( $i = 1, $n = count($parsed); $i < $n - 1; $i ++ ) {
            if ( ! isset($cur[ $parsed[ $i ] ]) ) {
                $cur[ $parsed[ $i ] ] = [ ];
            }
            $cur = &$cur[ $parsed[ $i ] ];
        }
        $cur[ $parsed[ $i ]] = $v;
    }
    $result['users'][] = $new_record;
}

print_r($result);

Here we are creating a new $new_record for each user record, and using a reference for the current nested level.

NOTE: In this case btw I put all the users in the same array. You can have the users index inside each record with a little modification:

$result = [];

foreach ( $records as $r ) {
    $new_record = [];
    foreach ( $r as $k => $v ) {
        $parsed = explode('.', $k);
        $cur    = &$new_record;
        for ( $i = 0, $n = count($parsed); $i < $n - 1; $i ++ ) {
            if ( ! isset($cur[ $parsed[ $i ] ]) ) {
                $cur[ $parsed[ $i ] ] = [ ];
            }
            $cur = &$cur[ $parsed[ $i ] ];
        }
        $cur[ $parsed[ $i ]] = $v;
    }
    $result[] = $new_record;
}

This will give you the exact structure that you've requested

answered Nov 4, 2016 at 15:37

galchen

5,3003 gold badges32 silver badges44 bronze badges

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

1 Comment

Joshua Lawson Over a year ago

This is almost there and on the right path for sure! I'm going to tinker with it a bit and post up my results later tonight. At the moment your examples are formatting them into a new array element for each user, rather than matching the user and adding the address to the addresses array. But its perfect!

repara2 · Accepted Answer · 2016-11-04 14:11:41Z

I think the worst problem with your output is that the array is not indexed, thus making very difficult or even impossible to detect and remove duplicates. Functions like array_unique() will not work properly. Here you have a foreach solution with an indexed output, hope it helps:

<?php
//given the result of the fetch()
$inputData = array(array(
    'users.name'             => 'Joshua',
    'users.age'              => 28,
    'users.addresses.street' => '3 Fake St',
    'users.addresses.state'  => 'Sydney'
),
    array(
        'users.name'             => 'Joshua',
        'users.age'              => 28,
        'users.addresses.street' => '16 New St',
        'users.addresses.state'  => 'Sydney'
    ),
    array(
        'users.name'             => 'Joshua',
        'users.age'              => 28,
        'users.addresses.street' => '140 Apple St',
        'users.addresses.state'  => 'Sydney'
    ),
    array(
        'users.name'             => 'Tim',
        'users.age'              => 34,
        'users.addresses.street' => '18 John St',
        'users.addresses.state'  => 'Sydney'
    ),
    array(
        'users.name'             => 'Tim',
        'users.age'              => 34,
        'users.addresses.street' => '143 Clark St',
        'users.addresses.state'  => 'Sydney'
    ));

//The result
$result = [];
//The first key
$result['users'] = [];
//Flatten the array with name and age
foreach ($inputData as $registries => $registry) {
    //add the data or overwritte it if exists
    $result['users'][$registry['users.name']] = ['name'      => $registry['users.name'],
                                                 'age'       => $registry['users.age'],
        //If you have any addresses info, keep it, otherwise declare an empty array
                                                 'addresses' => is_array($result['users'][$registry['users.name']]) && array_key_exists('addresses', $result['users'][$registry['users.name']]) ? $result['users'][$registry['users.name']]['addresses'] : []
    ];

    //if the current address is not in the list, add it
    if (!array_key_exists($registry['users.addresses.street'], $result['users'][$registry['users.name']]['addresses']))
        $result['users'][$registry['users.name']]['addresses'][$registry['users.addresses.street']] = ['street' => $registry['users.addresses.street'], 'state' => $registry['users.addresses.state']];

}
//Dump the result, move this inside the foreach() to get a dump for each user. Redeclare $result if every user has to be output separately
print_r($result);

Collectives™ on Stack Overflow

SQL Joined data into multidimensional array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related