Build a multidimensional PHP array from MySQL query

Question

I'm trying to create a multidimensional array from a MySQL query. This array is 3 layers deep. Each user has a membershipid, this membership id can have multiple characterid's. Each of those characterid's can have multiple properties.

membershipid -> characterid (can be up tot 3) -> light, racehash.

    $query = "SELECT p.membershipid, p.displayname, c.characterid, c.light, c.racehash
    FROM Players as p
    LEFT JOIN Characters as c
    ON c.membershipid = p.membershipid";
    $result = $mysqli->query($query) or die("SQL Error: Members konden niet worden opgehaald.");

    // Loop through the requested data, add to an array
    while ($data = mysqli_fetch_assoc($result)) {
        $categorylist[ $data['membershipid'] ] = array(
                    'displayname' => $data['displayname'],
              array('characterid' => $data['characterid'],
                    'light' => $data['light'],
                          'racehash' => $data['racehash']
            )
                    );

    } // end while statement

// A test to see how many items in array          
echo "<pre>\n";
print_r($categorylist);    
echo "</pre>\n";  

This works, but only displays only 1 character from each person while the query result shows each character id for each membership id.

    Array
(
    [4611686018428931875] => Array
        (
            [displayname] => White_Anomaly
            [0] => Array
                (
                    [characterid] => 2305843009264668680
                    [light] => 554
                    [racehash] => 3887404748
                )

        )

    [4611686018437972738] => Array
        (
            [displayname] => Bpunisher7
            [0] => Array
                (
                    [characterid] => 2305843009277456739
                    [light] => 392
                    [racehash] => 2803282938
                )

        )

The result from the SQL query is like:

I'm probably doing something wrong with my array definition and overwriting my characterid's each and every time since I only get the latest characterid displayed.

Preferred array:

Array
(
    [4611686018428931875] => Array
        (
            [displayname] => White_Anomaly
            [characters] => Array
                (
                    [0] => Array
                        (
                            [characterid] => 2305843009264668678
                            [light] => 370
                            [racehash] => 3887404748
                        )
                    [1] => Array
                        (
                            [characterid] => 2305843009264668680
                            [light] => 554
                            [racehash] => 3887404748
                        )
                )
        )

    [4611686018437972738] => Array
        (
            [displayname] => Bpunisher7
            [characters] => Array
                (
                    [0] => Array
                        (
                            [characterid] => 2305843009265241161
                            [light] => 534
                            [racehash] => 2803282938
                        )
                    [1] => Array
                        (
                            [characterid] => 2305843009265241163
                            [light] => 524
                            [racehash] => 3887404748
                        )
                    [2] => Array
                        (
                            [characterid] => 2305843009277456739
                            [light] => 392
                            [racehash] => 3887404748
                        )
                )
        )
)

Answer 1

If you allow me to restructure your result array slightly, this should be a better way to do it:

// Loop through the requested data, add to an array
while ($data = mysqli_fetch_assoc($result)) {
    extract($data,EXTR_PREFIX_ALL,'data');
    $categorylist[$data_membershipid]['displayname']     = $data_displayname;
    $categorylist[$data_membershipid][$data_characterid] = ['light'    => $data_light,
                                                            'racehash' => $data_racehash];

} // end while statement

As you can see, I have moved the 'characterid' to be the key of the sub-array. This is done so you will not overwrite another character.

I also used the newer notation for arrays. The extract() is there just to make the code slightly easier to read, but you can get rid of it.

Perhaps it is better to put the characters in their own array, like this:

// Loop through the requested data, add to an array
while ($data = mysqli_fetch_assoc($result)) {
    extract($data,EXTR_PREFIX_ALL,'data');
    $categorylist[$data_membershipid]['displayname'] = $data_displayname;
    $categorylist[$data_membershipid]['characters'][$data_characterid] = ['light'    => $data_light,
                                                                          'racehash' => $data_racehash];

} // end while statement

Answer 2

If $categorylist[ $data['membershipid'] ] exists it is overwritten by the next character.

You have to check if $categorylist[ $data['membershipid'] ] exists. In this case use array_push() to add a second character. Otherwise your code still works.