Convert an array from a MySQL JOIN (with duplicates) into a nested associative array

Question

I've got the following results from a MySQL JOIN:

+------------+----------------+-----------+---------+
| session_id | session_name   | user_name | user_id |
+------------+----------------+-----------+---------+
|         13 | Test session 1 | User 1    |      29 |
|         13 | Test session 1 | User 2    |      30 |
|         14 | test session 2 | User 1    |      31 |
|         14 | test session 2 | User 2    |      32 |
|         14 | test session 2 | User 3    |      33 |
|         14 | test session 2 | User 4    |      34 |
+------------+----------------+-----------+---------+

... that produces this PHP array:

Array
(
    [0] => Array
        (
            [session_id] => 13
            [session_name] => Test session 1
            [user_name] => User 1
            [user_id] => 29
        )

    [1] => Array
        (
            [session_id] => 13
            [session_name] => Test session 1
            [user_name] => User 2
            [user_id] => 30
        )

    [2] => Array
        (
            [session_id] => 14
            [session_name] => test session 2
            [user_name] => User 1
            [user_id] => 31
        )

    [3] => Array
        (
            [session_id] => 14
            [session_name] => test session 2
            [user_name] => User 2
            [user_id] => 32
        )

    [4] => Array
        (
            [session_id] => 14
            [session_name] => test session 2
            [user_name] => User 3
            [user_id] => 33
        )

    [5] => Array
        (
            [session_id] => 14
            [session_name] => test session 2
            [user_name] => User 4
            [user_id] => 34
        )
)

How can I process it into the following nested array by filtering the session_id duplicates?

Array
(
    [0] => Array
        (
            [session_id] => 13
            [session_name] => Test session 1
            [user] => Array
                (
                    [0] => Array
                        (
                            [user_name] => User 1
                            [user_id] => 29
                        )
                    [1] => Array
                        (
                            [user_name] => User 2
                            [user_id] => 30
                        )
                )
        )

    [1] => Array
        (
            [session_id] => 14
            [session_name] => test session 2
            [user] => Array
                (
                    [0] => Array
                        (
                            [user_name] => User 1
                            [user_id] => 31
                        )
                    [1] => Array
                        (
                            [user_name] => User 2
                            [user_id] => 32
                        )
                    [2] => Array
                        (
                            [user_name] => User 3
                            [user_id] => 33
                        )
                    [3] => Array
                        (
                            [user_name] => User 4
                            [user_id] => 34
                        )
                )
        )
)

Have you tried something? This is very straightforward.

Passerby
– Passerby

2014-03-25 04:16:32 +00:00
Commented Mar 25, 2014 at 4:16 — Passerby
– Passerby, Commented Mar 25, 2014 at 4:16

AyB · Accepted Answer · 2014-03-25 07:12:38Z

Try:

/* The first foreach stores the number of times each session_id is repeated */
$session_ids = array();
foreach($array as $k=>$v){

    if(!array_key_exists($v['session_id'], $session_ids)){
        $session_ids[$v['session_id']] = 1;
    }
    else
    {
        $session_ids[$v['session_id']] += 1;
    }
}


/*The second foreach will store each session_id once and loop through to
the original array to find user details of that session_id and store them
as nested arrays*/
$new_array = array();
$counter = 0;
foreach($session_ids as $k=>$v){
    $new_array[$counter]['session_id'] = $k;
    foreach($array as $k1=>$v1){
        if($v1['session_id'] == $k)
        {
            $new_array[$counter]['session_name'] = $v1['session_name'];
            $new_array[$counter]['user'][] = array('user_name'=>$v1['user_name'], 'user_id'=>$v1['user_id']);
        }
    }

    $counter++;
}

Demo

Collectives™ on Stack Overflow

Convert an array from a MySQL JOIN (with duplicates) into a nested associative array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related