Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I've been racking my head on this one and am hoping someone has a creative solution for this. In short, we are pulling info from a database and would like to create a nested array using some of the result columns, sorted and grouped by their values;

A result may look something like this, where it is always an array of single-level arrays:

Array (
    [0] => Array (
        ['day'] => 'a',
        ['stuff'] => 'b',
        ['nested3'] => 'c',
        ['data1'] => 'park',
        ['data2'] => 'sunny'
    ),
    [1] => Array (
        ['day'] => 'a',
        ['stuff'] => 'b',
        ['nested3'] => 'c',
        ['data1'] => 'store',
        ['data2'] => 'sunny'
    ),
    [2] => Array (
        ['day'] => 'a',
        ['stuff'] => 'z',
        ['nested3'] => 'c',
        ['data1'] => 'home',
        ['data2'] => 'rainy'
    ),
)

Here, I'd like to create a new nested array, grouped X levels deep dictated by the number of 'nested' named type columns. Note here that 'nested' naming scheme is only for example, the actual column name could be anything AND could be 3 columns deep to sort by, or more or less - so hard coding the sorting loops is not an option.

Array (
    'a' => Array (
        'b' => Array ( 
            'c' => Array (
                [0] => Array (
                    'data1' => 'park',
                    'data2' => 'sunny'
                ),
                [1] => Array (
                    'data1' => 'store',
                    'data2' => 'sunny'
                )
            )
        ),
        'z' => Array (
            'c' => Array (
                'data1' => 'home',
                'data2' => 'rainy'
            )
        )
    )
)

This will be used to generate tree style html tables. Unfortunately, it does not appear that PDO's fetch supports grouping by more than one columns so I need cook up my own.

You will notice the data is originally associative - since Im pulling it from PDO it can be numerically indexed as well, this part doesnt matter as I will know ahead of time what order the data is in.

I will know ahead of time how many columns deep the nesting columns will go, so something like this would work:

function nest_sort(&arr, $sort_col_count) {
    ...
}

or a function I can just specify the names of the columns to use for sorting, in order of nesting

function nest_sort(&arr, $sort_columns) // Sorts $arr by $sort_columns values // in nesting order of $sort_columns } nest_sort($array, array('nested1', 'nested2'))

Its possible to be working with decently large data sets, 1000s of rows of data. So resources are of concern.

Ive made many attempts at this and my largest hangup is the fact that the sort depth is not hard-predictable, and writing a recursive function to create nested arrays from its self has been a PITA.

I can't help but think there is an easier and more performant way to accomplish this using array_walk_recursive and/or array_merge recursive.

Appreciate it everyone.

share|improve this question
 
Will your nestedXX keys be always in 'correct' order? (i.e. number will always indicate correct nesting level) –  Alma Do Nov 19 at 8:51
 
I don't see why recursion should be required here. You iterate through the array you get from the database query and 'pick' the values of the 'nesting columns' specified as a configuration array. Then you save the 'data values' into a result array using the picked values as keys. That should give a complexity of O(n), not more. –  arkascha Nov 19 at 8:53
 
Surely your example of what you want has an error in it, in terms of having nested2, nested3 still showing? –  pebbl Nov 19 at 8:54
 
Commens: 1) Nested keys always in order? YES they will be. This accepts a variable amount of select values from a form to sort them by. The beginning X (x as column count) is predictable as I can pass how many values they chose to sort by. 2: Im under the impression recursion is required as we cant hard code how many levels deep to go. Also, it needs to create an array with a key using the value, then a sub array key by next column value.. and so on until the end of sorting columns. 3: Yep! I corrected the example.. Sorry –  user3007854 Nov 19 at 19:44

2 Answers

up vote 0 down vote

If your nestedXX keys are indicating valid structure, you can use just simple loop:

$array = Array (
    0 => Array (
        'nested1' => 'a',
        'nested2' => 'b',
        'nested3' => 'c',
        'data1' => 'park',
        'data2' => 'sunny'
    ),
    1 => Array (
        'nested1' => 'a',
        'nested2' => 'b',
        'nested3' => 'c',
        'data1' => 'store',
        'data2' => 'sunny'
    ),
    2 => Array (
        'nested1' => 'a',
        'nested2' => 'z',
        'nested3' => 'c',
        'data1' => 'home',
        'data2' => 'rainy'
    )
);

$result = [];

foreach($array as $item)
{
   $nested = array_filter(array_keys($item), function($key)
   {
      return preg_match('/^nested\d+$/', $key);
   });
   $keys   = array_diff_key($item, array_flip($nested));
   $link = &$result;
   foreach($nested as $key)
   {
      $link = &$link[$item[$key]];
   }
   $link[] = $keys;
}

//var_dump($result);
share|improve this answer
 
I appreciate it. Unfortunately that will not work as the 'nested' naming convention was just an example. The actual key values will vary. They are predictable at call time in order (through the options used for the mysql query) but I will not know ahead of time for coding what format, or how many options will be used for sorting. This is why I need a recursive function to go X columns deep for nesting, or be able to specify the specific columns in order to sort by. Example could be sorted by location, weather as above. Next it could be weather, week_day, activity.. etc. –  user3007854 Nov 19 at 19:35
up vote 0 down vote

Well, I finally have a functioning piece together after more testing. Now ill just have to finish the table generation. It will pass by reference the array to be sorting as to free the memory as it goes.

$a[] = array('one', 'two', 'three', 'win', 'ner');
$a[] = array('one', 'two', 'three', 'win', 'dog');
$a[] = array('one', 'two', 'three', 'lost', 'cat');
$a[] = array('one', 'two', 'five', 'win', 'ner');
$a[] = array('one', 'two', 'five', 'lost', 'ner');

function dep(&$a, $depth) {

    $count = count($a);
    for($z = 0; $z < $count; $z++) {
        unset($tmp);
        $ptr = &$tmp;

        for($x = 0; $x <= $depth; $x++) {   
            $old = $new; 
            $new = array_shift($a[$z]);
            if($x == 0) continue;

            $ptr = &$ptr[$old];
            if($x == $depth) {
                array_unshift($a[$z], $new);
                $ptr['stats'][] = $a[$z];
            }
        }
        $array = array_merge_recursive((array)$array, $tmp);
        unset($a[$z]);
    }
    return $array;
}

Sample result..

Array
(
    [one] => Array
        (
            [two] => Array
                (
                    [three] => Array
                        (
                            [stats] => Array
                                (
                                    [0] => Array
                                        (
                                            [0] => win
                                            [1] => ner
                                        )

                                    [1] => Array
                                        (
                                            [0] => win
                                            [1] => dog
                                        )

                                    [2] => Array
                                        (
                                            [0] => lost
                                            [1] => cat
                                        )

                                )

                        )

                    [five] => Array
                        (
                            [stats] => Array
                                (
                                    [0] => Array
                                        (
                                            [0] => win
                                            [1] => ner
                                        )

                                    [1] => Array
                                        (
                                            [0] => lost
                                            [1] => ner
                                        )

                                )

                        )

                )

        )

)

This way each array level above the stats key will be used as a title 'branch' in the tree-table style tables this will be used to generate.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.