CODEWITHSUNDEEP
phpmysqlarraysmultidimensional-array
Louis Van Zyl
Louis Van Zyl

Reputation: 33

Multidimensional array merge-down

I am using a mysql table that stores stats, I retrieve the values for as specific period using the following query:

SELECT t2.NAME, COUNT(t1.TYPE) AS total, t1.DATE, t1.TYPE
        FROM stats AS t1
        LEFT JOIN agents AS t2 ON t1.AGENTID = t2.ID
        WHERE (DATE between  DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() ) 
        GROUP BY DATE, AGENT, TYPE

This returns the data in the following array:

Array
(
    [0] => Array
        (
            [NAME] => Jack Blake
            [total] => 15
            [DATE] => 2014-03-03
            [TYPE] => 1
        )

    [1] => Array
        (
            [NAME] => Jack Blake
            [total] => 13
            [DATE] => 2014-03-03
            [TYPE] => 4
        )

    [2] => Array
        (
            [NAME] => John Doe
            [total] => 5
            [DATE] => 2014-03-03
            [TYPE] => 1
        )

    [3] => Array
        (
            [NAME] => John Doe
            [total] => 2
            [DATE] => 2014-03-03
            [TYPE] => 3
        )

    [4] => Array
        (
            [NAME] => John Doe
            [total] => 2
            [DATE] => 2014-03-03
            [TYPE] => 4
        )

    [5] => Array
        (
            [NAME] => Jen Jester
            [total] => 8
            [DATE] => 2014-03-03
            [TYPE] => 1
        )

    [6] => Array
        (
            [NAME] => Jen Jester
            [total] => 1
            [DATE] => 2014-03-03
            [TYPE] => 3
        )

    [7] => Array
        (
            [NAME] => Jen Jester
            [total] => 3
            [DATE] => 2014-03-03
            [TYPE] => 4
        )

    [8] => Array
        (
            [NAME] => Jane Doe
            [total] => 7
            [DATE] => 2014-03-03
            [TYPE] => 1
        )

    [9] => Array
        (
            [NAME] => Jane Doe
            [total] => 4
            [DATE] => 2014-03-03
            [TYPE] => 4
        )

    [10] => Array
        (
            [NAME] => Jack Blake
            [total] => 51
            [DATE] => 2014-03-04
            [TYPE] => 1
        )

    [11] => Array
        (
            [NAME] => Jack Blake
            [total] => 14
            [DATE] => 2014-03-04
            [TYPE] => 4
        )

    [12] => Array
        (
            [NAME] => John Doe
            [total] => 5
            [DATE] => 2014-03-04
            [TYPE] => 1
        )

    [13] => Array
        (
            [NAME] => John Doe
            [total] => 3
            [DATE] => 2014-03-04
            [TYPE] => 4
        )

    [14] => Array
        (
            [NAME] => Jen Jester
            [total] => 3
            [DATE] => 2014-03-04
            [TYPE] => 1
        )

    [15] => Array
        (
            [NAME] => Jen Jester
            [total] => 19
            [DATE] => 2014-03-04
            [TYPE] => 4
        )
)

I have tried several methods but still seem to fail, I want to return the array in the following way, either by SQL STATEMENT or by PHP function:

Array 
(
    [2014-03-03] => Array
        (
            [0] => Array
                (
                    [NAME] => Jack Blake
                    [1] => 15
                    [4] => 13
                )
            [1] => Array
                (
                    [NAME] => John Doe
                    [1] => 5
                    [3] => 2
                    [4] => 2
                )
            [2] => Array
                (
                    [NAME] => Jen Jester
                    [1] => 8
                    [3] => 1
                    [4] => 3
                )
            [2] => Array
                (
                    [NAME] => Jane Doe
                    [1] => 7
                    [4] => 4
                )
        )
    [2014-03-04] => Array
        (
            [0] => Array
                (
                    [NAME] => Jack Blake
                    [1] => 51
                    [4] => 14
                )
            [1] => Array
                (
                    [NAME] => John Doe
                    [1] => 5
                    [4] => 3
                )
            [2] => Array
                (
                    [NAME] => Jen Jester
                    [1] => 3
                    [4] => 19
                )
        )
)

You will notice that each key is the DATE, each sub-key contains the TYPE as a key with the value associated with the same NAME key on the same DATE.

I hope this is clear enough for a solution to be provided. Thanks in advance.

Upvotes: 0

Views: 69

Answers (1)

mic
mic

Reputation: 1273

This will sort your list as you require

$arrays = array();
foreach ($results as $result) {
    $arrays[$result['DATE']][$result['NAME']]['NAME'] = $result['NAME'];
    $arrays[$result['DATE']][$result['NAME']][$result['TYPE']] = $result['total'];
}

$newArray = array();
foreach ($arrays as $key => $array) {
    $newArray[$key] = array_values($array);
}

The $newArray will contain all the formatted elements, there may be a simpler way but this does work

Upvotes: 1

Related Questions