7
\$\begingroup\$

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;
}
\$\endgroup\$
9
  • \$\begingroup\$ array_column('level') will give you that php.net/manual/en/function.array-column.php. Check out array_keys also for reference \$\endgroup\$
    – Indra
    Commented Apr 5, 2018 at 20:15
  • \$\begingroup\$ @Indra, with array_column() I would need to explicitly indicate the column names, wouldn't I? \$\endgroup\$
    – pbarney
    Commented Apr 5, 2018 at 20:25
  • \$\begingroup\$ yeah, but that can be a variable. like in this case $column='level'. array_column($column) and you set that as needed \$\endgroup\$
    – Indra
    Commented Apr 5, 2018 at 20:28
  • \$\begingroup\$ Unfortunately, I would also need to specify the column that contains my row id's for the key. My method above doesn't care about the names of the columns. \$\endgroup\$
    – pbarney
    Commented Apr 5, 2018 at 20:29
  • \$\begingroup\$ array_combine(array_column($col1), array_column($col2)) ? Ah.. this would be so much easier using collections. Or a combo of array_keys and array_values or an array_map should work. \$\endgroup\$
    – Indra
    Commented Apr 5, 2018 at 20:33

4 Answers 4

6
\$\begingroup\$

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.

\$\endgroup\$
3
  • \$\begingroup\$ Thank you, this is exactly the kind of help I was hoping for. In my current case, I'm unfortunately working with a legacy CodeIgniter codebase that doesn't have that as part of it's database abstraction. But I now have a new useful tool in my belt. \$\endgroup\$
    – pbarney
    Commented Apr 6, 2018 at 14:02
  • \$\begingroup\$ One quick question: does this handle my second use case, where the array values could be arrays themselves? \$\endgroup\$
    – pbarney
    Commented Apr 6, 2018 at 14:03
  • 1
    \$\begingroup\$ yes of course, PDO has PDO::FETCH_UNIQUE mode as well \$\endgroup\$ Commented Apr 6, 2018 at 14:04
4
\$\begingroup\$

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.

\$\endgroup\$
7
  • \$\begingroup\$ I try avoiding fors as much as possible and rely on native functions. I think your result does the job nicely, these are just alternatives. I am pretty rusty with my php (using Laravel for 4 years), but when I did work in vanilla php I used to rely on these 4 functions like crazy: array_map, array_keys, array_values, array_column \$\endgroup\$
    – Indra
    Commented Apr 5, 2018 at 20:47
  • \$\begingroup\$ Thank you for responding. But this doesn't address the case where the array contains more than one additional element. See the 3rd code block in the question. \$\endgroup\$
    – pbarney
    Commented Apr 5, 2018 at 20:55
  • \$\begingroup\$ @pbarney you're right. let me check another solution \$\endgroup\$
    – Indra
    Commented Apr 5, 2018 at 20:57
  • 1
    \$\begingroup\$ Is your general thesis that using foreach loops are a bad idea? If so, why? I'm looking for wisdom to help me grow as a programmer. \$\endgroup\$
    – pbarney
    Commented Apr 5, 2018 at 21:05
  • \$\begingroup\$ check out my edit and feel free to give me feedback \$\endgroup\$
    – Indra
    Commented Apr 5, 2018 at 21:24
2
\$\begingroup\$

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'
    ],
    ......
]
\$\endgroup\$
1
0
\$\begingroup\$

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;
}
\$\endgroup\$
3
  • 2
    \$\begingroup\$ It appears that, other than differences in comments, the changes include 1. converting name from kabob_case into camelCase, 2. an early return statement when the array is empty and 3. a ternary operator inside the foreach instead of an if and else case. Is it "optimized" for those reasons? In what way(s) is it "a generic function that can use any array without having to know the column names, as long as the "key" column is first"? \$\endgroup\$ Commented Jan 18, 2023 at 19:28
  • 1
    \$\begingroup\$ What good is the count() check if the foreach() will already ignore an empty array and move straight to the return? It could simply return an empty array by null coalescing return $result ?? []; if you didn't want to declare $result = [] prior to looping. \$\endgroup\$ Commented Jan 19, 2023 at 4:15
  • \$\begingroup\$ A demo: 3v4l.org/vgP3V \$\endgroup\$ Commented Jan 19, 2023 at 22:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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