Converting a query result into an key=>value pairs for a lookup table

Question

My app has a number of lookup tables stored in a MySQL database that are used for various purposes such as a select element.

Unfortunately, the query results aren't in an easily-digestible format and need some massaging to be more useful to me.

For example, when you query one of the lookup tables for all values, you get an array like this:

 0 => ['level_id' => 1, 'level' => 'Trivial'],
 1 => ['level_id' => 2, 'level' => 'Moderate']
 2 => ['level_id' => 3, 'level' => 'Challenging'],
 3 => ['level_id' => 4, 'level' => 'Formidable']

But for my purposes, this is what I want:

 1 => 'Trivial',
 2 => 'Moderate',
 3 => 'Challenging',
 4 => 'Formidable'

(Note that the array keys are the same as the record id).

If the array has more than one additional column, the result will be contained in sub-arrays:

 3 => ['level' => 'Challenging', 'description' => 'Foobar'],
 4 => ['level' => 'Formidable', 'description' => 'Bazbat']

This is all very trivial, but I wrote this function that I'd like you to review to know if there is a better way to do this in PHP.

The function needs to be generic so it can use any lookup array without having to know the column names.

<?php
// Turn an array into a key=>value pair. Assumes the key is the first item in the sub-array.
public function column_into_keys(array $array): array {

    // get the name of the column that contains the record id
    $key = key($array[0]);

    foreach($array as $row) {

        // pop the new key off the top of the array
        $id = array_shift($row);

        // is there only one item left in the array?
        if (count($row) == 1)
            // get the first value
            $result[$id] = current($row);
        else
            // get all of the values
            $result[$id] = $row;
    }

    return $result;
}

Answer 1

Given you are working with PHP, there is no reason to neglect such a feature that already exists in the language.

For example when you query one of the lookup tables for all values,

$data = $pdo->query("SELECT * FROM lookup")->fetchAll(PDO::FETCH_KEY_PAIR);

you get an array like this:

 1 => 'Trivial',
 2 => 'Moderate',
 3 => 'Challenging',
 4 => 'Formidable'

all thanks to PDO::FETCH_KEY_PAIR fetch mode.

Of course it works only with PDO, but you are supposed to use this driver anyway.

Answer 2

Try this:

$finalResult=[];
        array_map(function ($value) use (&$finalResult){
            $result = array_values($value);
            return $finalResult[$result[0]] = $result[1];
        }, $array);

        return $finalResult;

I try avoiding for as much as possible and rely on native functions. I started doing this since I read a book called Refactoring to Collections. This improved my code and the speed of my code.

Native functions are written in a memory effective way.

The function inside the array_map is called a closure. You can implement a closure anytime you need to do quick computations locally.

Your solution does the job right, but looks a little bit messy. I don't think there will be a speed issue with what you have though.

Edit:

 $array = [
            0 => ['level_id' => 1, 'level' => 'Trivial', 'description' => 'Foobar'],
            1 => ['level_id' => 2, 'level' => 'Moderate', 'description' => 'Foobar'],
            2 => ['level_id' => 3, 'level' => 'Challenging', 'description' => 'Foobar'],
            3 => ['level_id' => 4, 'level' => 'Formidable'],
        ];

Using this:

$finalResult = [];
        array_map(function ($value) use (&$finalResult) {
            $result = array_values($value);
            return sizeof($result) == 2 ?
                $finalResult[$result[0]] = $result[1]
                : $finalResult[$result[0]] = array_slice($value, 0, sizeof($value) - 1);
        }, $array);

You will always get the right answer no matter how many columns you have or how many combinations you have.

• sizeof($result) == 2 ? $finalResult[$result[0]] = $result[1] - if the array has 2 entries use the first as id and second as value.

• $finalResult[$result[0]] = array_slice($value, 0, sizeof($value) - 1); - if it has more than 2 entries then 2 entries remove the first (array_slice($value, 0, sizeof($value) - 1)). This does not cover the case where there is only one entry, but neither did yours. We should cover that as well like this:

  return sizeof($result) == 2 ?
                      $finalResult[$result[0]] = $result[1]
                      : sizeof($result) >  2 ? $finalResult[$result[0]] = array_slice($value, 0, sizeof($value) - 1) : [];
  

Now for the for issue:

What a for does is basically iterating through every item of the array (no matter how long) and do something with it. Ideally, native functions use memory addresses to find one element at the time, do something with it and free the memory. I said ideally, because sometimes whoever wrote the code threw a for behind it. I will try to write a blog post about this since it sounds like a great issue to raise.

Mostly, what I can tell you after 12 years of programming is that you learn by doing. And you usually learn when something does not work out. I learned my lesson with for after a server failed with 503 ( was having timeout issues) when my database reached a million entries or so.

It is great that you want to learn and improve and I think that if you stick to what you're doing you're gonna get there. Just don't be afraid to test and try other things. Whenever I have a similar issue I go to the manual and check what I can use. If no such function is in the manual I add it to my helpers file (that I import everywhere) and try it with lots of data. If it fails I go back and try other options.

Answer 3

Array combine takes Keys + Values as values and combines them. Make sure they are the same size.

$result = array_combine(
    array_column($array, 'level_id'),
    array_column($array, 'level')
);

And also a nice trick with array_map() and NOT using a callback.

$test1 = [
    'first-1',
    'first-2',
    'first-3',
    'first-4',
];
$test2 = [
    'second-1',
    'second-2',
    'second-3',
    'second-4',
];
$result = array_map(null, $test1, $test2);
[
    [
        'first-1',
        'second-1'
    ],
    [
        'first-2',
        'second-2'
    ],
    ......
]

Answer 4

Using the PDO functions (as pointed out here by @YourCommonSense) is the right approach when querying from a database.

But in cases where the array doesn't originate from a database, the following is an optimized approach to the original question using a generic function that can use any array without having to know the column names, as long as the "key" column is first.

<?php
// Turn an array into a key=>value or key=>array pair.
// Assumes the key is the first item in the sub-array.
public function columnIntoKeys(array $array): array {
    
    // skip empty arrays
    if (! count($array)) return [];
    
    // loop through each result row
    foreach($array as $row) {
        
        // pop the value of the row identifier off the top of the array
        $id = array_shift($row); // O(n)
        
        // if there is only one item left in the array...
        $result[$id] = (count($row) == 1)
            ? current($row) // ...get the first value
            : $row;         // ...get the rest of the array
    }
    return $result;
}