PHP - Recursively set each array element's key to the value of a child element when given the childs key name

Question

I'll start by showing a non-recursive example

Non- recursive example

$given_key_name = 'site_id';

$rows[] = array(
    'site_id' => '0',
    'language_id' => '1',
    'name' => 'sitename',
    'description' =>'site desc',
);

$results = array();
foreach($rows as $row){
    $key_value = $row[$given_key_name];
    unset($row[$given_key_name]);
    $results[$key_value] = $row;
}

//  OR This method is faster than the forloop

$results = array_combine(array_column($rows, $given_key_name),$rows);
foreach($results as &$row){
    unset($row[$given_key_name]); 
}

$results Equals

$results[0] = array( 
    'language_id' => '1',
    'name' => 'sitename',
    'description' =>'site desc',
);

Simple, the key name has been set to the value of the given child element. But I would like to be able to nest and unnest by using multiple key names.

Example

$given_key_names = array('site_id', 'language_id');

In this case the required result would be.

$results[0][1] = array( 'name' => 'sitename', 'description' =>'site desc', );

Explanation

The first keys value has been used as the first key in the $results array and a new empty array is created as its value. $results[0] = array();

As there is a second key, its value is set as a key to the newly created array and its value is also a new empty array. $results[0][1] = array();

As there are no more keys the empty array is populated with the remaining values

$results[0][1] = array( 'name' => 'sitename', 'description' =>'site desc', );

so i would like two functions nestByKeyNames and unNestByKeyName.

NestByKeyNames Function

Christians Answer solves this

function nestByKeyNames($arrayRows, $arrayKeyOrder){

    // Prepare resulting array
    $arrayResult = array();

    // Cycle the input array
    foreach($arrayRows as $someRow){
        // We will acomplish this using references
        $current = &$arrayResult;

        // get the current level
        foreach($arrayKeyOrder as $someKey){
            $someValue = $someRow[$someKey];
            if(isset($current[$someValue])){
                $current = &$current[$someValue];
            }else{
                $current[$someValue] = array();
                $current = &$current[$someValue];
            }
            unset($someRow[$someKey]);
        }
        $current = $someRow;
    }
    return $arrayResult;
}

I wonder whether array_combine(array_column($arrayRows, $key_name),$arrayRows); could be used instead of the first iteration to improve performance?

This represents the results from a mysql select statement.

$rows = array(
    array(
        'pri_id_1' =>1,
        'pri_id_2' =>1,
        'pri_id_3' =>1,
        'col_1' =>'col_value_1111',
        'col_2' =>'col_value_1112',
        'col_3' =>'col_value_1113',
    ),
    array(
        'pri_id_1' =>1,
        'pri_id_2' =>2,
        'pri_id_3' =>1,
        'col_1' =>'col_value_1211',
        'col_2' =>'col_value_1212',
        'col_3' =>'col_value_1213',
    ),
    array(
        'pri_id_1' =>1,
        'pri_id_2' =>3,
        'pri_id_3' =>1,
        'col_1' =>'col_value_1311',
        'col_2' =>'col_value_1312',
        'col_3' =>'col_value_1313',
    )
);

$keyNames = array('pri_id_1','pri_id_2','pri_id_3');
$results = nestByKeyNames($rows, $keyNames);

The following output is produced

Array
(
    [1] => Array
        (
            [1] => Array
                (
                    [1] => Array
                        (
                            [col_1] => col_value_1111
                            [col_2] => col_value_1112
                            [col_3] => col_value_1113
                        )

                )

            [2] => Array
                (
                    [1] => Array
                        (
                            [col_1] => col_value_1211
                            [col_2] => col_value_1212
                            [col_3] => col_value_1213
                        )

                )

            [3] => Array
                (
                    [1] => Array
                        (
                            [col_1] => col_value_1311
                            [col_2] => col_value_1312
                            [col_3] => col_value_1313
                        )

                )

        )

)

UnNestByKeyNames Function

unNestByKeyNames should be able to take this output and convert it back to the original array providing that it is given the key names. Christians Answer did not solves this as it doesnt work with a single key name but i can tell its very close.

function unNestByKeyNames($arrayRows, $arrayKeyOrder){


}

$keyNames = array('pri_id_1','pri_id_2','pri_id_3');
$rows = unNestKeyNames($results, $keyNames);

My true goal is to take the results from MYSQL SELECT statement and populate a form using the same naming convention by using nestByKeyNames.

e.g.

<input name="rows[1][1][1][col_1]" value="col_value_1" />

and then convert the $_POST request back into an MYSQL INSERT statement by first using unNestByKeyNames.

From this i will create an INSERT statement.

function returnValues($rows, $column_names){

    //validation has been removed for clarity

    $implode_VALUES = array();

    foreach ($rows as $key => $row) {
        $implode_row_values = array();
        foreach ($column_names as $column_name) {
            $implode_row_values[$column_name] = $row[$column_name];
        }
        if($implode_row_values){
            $implode_VALUES[] = " ('" . implode("','", $implode_row_values) . "') ";
        }
    }
    return $implode_VALUES;
}

$implode_COLUMNS = array('pri_id_1','pri_id_2','pri_id_3','col_1','col_2','col_3');

$implode_VALUES = returnValues($rows, $implode_COLUMNS)

$sql = "INSERT INTO table_name (" . implode(',', $implode_COLUMNS) . ") VALUES " . implode(',', $implode_VALUES);

The final result should produce a sql statement like so

INSERT INTO table_name (pri_id_1,pri_id_2,pri_id_3,col_1,col_2,col_3) VALUES ('1','1','1','NEW_value_1111','NEW_value_1112','NEW_value_1113') , ('1','2','1','NEW_value_1211','NEW_value_1212','NEW_value_1213') , ('1','3','1','NEW_value_1311','NEW_value_1312','NEW_value_1313')

What I Would like

• Improvement suggestions on the 'nestByKeyNames' function (performance/ does it have bugs)
• help producing 'unNestByKeyNames' code
• Improvement suggestions on my '$rows to mysql INSERT' approach
• examples of how i could make any of my code perform better.

Answer 1

This was trickier than I first imagined but I believe I have a messy solution.

First of all, this is the data I am working with. dumpr is a custom function that formats var_dump better.

$arrayKeyOrder = array(
    'site_id',
    'language_id'
);

$original = array(
    array(
        'site_id' => '0',
        'language_id' => '1',
        'name' => 'sitename',
        'description' =>'site desc',
    ),

    array(
        'site_id' => '0',
        'language_id' => '2',
        'name' => 'sitename',
        'description' =>'site desc',
    ),

    array(
        'site_id' => '1',
        'language_id' => '1',
        'name' => 'sitename',
        'description' =>'site desc',
    ),

    array(
        'site_id' => '2',
        'language_id' => '1',
        'name' => 'sitename',
        'description' =>'site desc',
    ),
);

$zipped = doZip($original, $arrayKeyOrder);
$unzipped = unZip($zipped, $arrayKeyOrder);

dumpr($original);
dumpr($zipped);
dumpr($unzipped);

Here is the zip and unzip functions:

function doZip($arrayRows, $arrayKeyOrder){

    // Prepare resulting array
    $arrayResult = array();

    // Cycle the input array
    foreach($arrayRows as $someRow){
        // We will acomplish this using references
        $current = &$arrayResult;

        // get the current level
        foreach($arrayKeyOrder as $someKey){
            $someValue = $someRow[$someKey];
            if(isset($current[$someValue])){
                $current = &$current[$someValue];
            }else{
                $current[$someValue] = array();
                $current = &$current[$someValue];
            }
            unset($someRow[$someKey]);
        }

        $current = $someRow;
    }

    return $arrayResult;
}


function unZip($arrayRows, $arrayKeyOrder, $arrayValues = array(), $depth = 0){

    $arrayResults = array();

    if($depth < count($arrayKeyOrder)){
        foreach($arrayRows as $key => $value){
            $arrayValues[$depth] = $key;
            $arrayResults[] =  unZip($value, $arrayKeyOrder, $arrayValues, $depth + 1);
        }
    }else{
        $extra = array_combine($arrayKeyOrder, $arrayValues);
        $result = array_merge($extra, $arrayRows);
        return $result;
    }

    if($depth == 0){
        for($i = 1; $i < count($arrayKeyOrder); $i++){
            $arrayResults = call_user_func_array('array_merge', $arrayResults);
        }        
    }

    return $arrayResults;
}

And finally, here is the output. let me know if this is what you were asking for and if it worked OK on a larger data-set.

/vhost/virtual/sandbox/public/index.php:54
array(4) {
    [0] = array(4) {
        [site_id] = string(1) "0"
        [language_id] = string(1) "1"
        [name] = string(8) "sitename"
        [description] = string(9) "site desc"
    }
    [1] = array(4) {
        [site_id] = string(1) "0"
        [language_id] = string(1) "2"
        [name] = string(8) "sitename"
        [description] = string(9) "site desc"
    }
    [2] = array(4) {
        [site_id] = string(1) "1"
        [language_id] = string(1) "1"
        [name] = string(8) "sitename"
        [description] = string(9) "site desc"
    }
    [3] = array(4) {
        [site_id] = string(1) "2"
        [language_id] = string(1) "1"
        [name] = string(8) "sitename"
        [description] = string(9) "site desc"
    }
}

/vhost/virtual/sandbox/public/index.php:55
array(3) {
    [0] = array(2) {
        [1] = array(2) {
            [name] = string(8) "sitename"
            [description] = string(9) "site desc"
        }
        [2] = array(2) {
            [name] = string(8) "sitename"
            [description] = string(9) "site desc"
        }
    }
    [1] = array(1) {
        [1] = array(2) {
            [name] = string(8) "sitename"
            [description] = string(9) "site desc"
        }
    }
    [2] = array(1) {
        [1] = array(2) {
            [name] = string(8) "sitename"
            [description] = string(9) "site desc"
        }
    }
}

/vhost/virtual/sandbox/public/index.php:56
array(4) {
    [0] = array(4) {
        [site_id] = int(1) 0
        [language_id] = int(1) 1
        [name] = string(8) "sitename"
        [description] = string(9) "site desc"
    }
    [1] = array(4) {
        [site_id] = int(1) 0
        [language_id] = int(1) 2
        [name] = string(8) "sitename"
        [description] = string(9) "site desc"
    }
    [2] = array(4) {
        [site_id] = int(1) 1
        [language_id] = int(1) 1
        [name] = string(8) "sitename"
        [description] = string(9) "site desc"
    }
    [3] = array(4) {
        [site_id] = int(1) 2
        [language_id] = int(1) 1
        [name] = string(8) "sitename"
        [description] = string(9) "site desc"
    }
}

Answer 2

Here are two simple functions to solve your problem. I don't put any example as I have used your data and the same function name and arguments.

The first one takes profit of pointers to solve the first step of the problem:

function nestByKeyNames($rows, $aKeys) {
    $tab=Array();
    foreach ($rows as &$v) {
            // calculate the pointer position
            $t=&$tab;
            foreach ($aKeys as $v1) {
                    $t=&$t[$v[$v1]];
                    unset($v[$v1]);
            }
            // save the value
            $t=$v;
    }
    return $tab;
}

This one uses a recursive algorithm and give the reverse output

function unNestByKeyNames($arrayRows, $aKeys){
    $t=Array();
    if (!count($aKeys)) return Array($arrayRows);
    foreach ($arrayRows as $k=>&$v) {
            $res=unNestByKeyNames($v, array_slice($aKeys,1));
            foreach ($res as $k1=>$v1) $t[]=array_merge(Array($aKeys[0]=>$k), $v1);
    }
    return $t;
 }

I have no suggestion about your SQL INSERT approach as long as you take care of sql injection, which I suppose might be the reason of your comment "validation has been removed for clarity"

Answer 3

There is no real method to what you wanting if you want to use the primary key you have to know the column name of the primary key hell you should not the columns your querying for. the best way to do it would be to use the AS keyword in the MySQL Query

SELECT primary as ID, ... where primary is the column name of your primary key and now ID is your primary key in the result set.

You can then just do the standard

$sortedResults = array();
while($row = $queryResult->fetch_assoc()){
    $rowId = $row["ID"];
    $sortedResults[$rowId] = $row;
}

If you don't know what the primary key is there i no reasonable way to obtain it there is a method to get the table columns and then you could go though them find the primary key save it then you have the primary key to do your while on but this would be one hell of an overhead on every query you make.

Answer 4

Is there a reason the below wouldn't work?

$results = array();
while($row = $qry->fetch_assoc()){

    $results[$row['site_id']][$row['language_id']] = array(

        'name'  =>  $row['name'],
        'description' => $row['description']

    );

}

Answer 5

Try this:

// initialize your array
$all_rows = array();

// loop through query results
while( $row = $qry->fetch_assoc() )
{
    // temporarily store these vars for easy use later
    $s_id = $row['site_id'];
    $l_id = $row['language_id'];

    // create an empty array based on site_id and language_id
    $all_rows[ $s_id ][ $l_id ] = array();

    // loop through all columns returned from query
    foreach ( $row as $key => $val )
    {
        // if it's not one of the two primary keys, push it to the array
        if ( ! in_array($key, $all_primary_keys) )
        {
            $all_rows[ $s_id ][ $l_id ][ $key ] = $val;
        }
    }
}