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

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; }  • array_column('level') will give you that php.net/manual/en/function.array-column.php. Check out array_keys also for reference – Indra Apr 5 '18 at 20:15 • @Indra, with array_column() I would need to explicitly indicate the column names, wouldn't I? – pbarney Apr 5 '18 at 20:25 • yeah, but that can be a variable. like in this case$column='level'. array_column($column) and you set that as needed – Indra Apr 5 '18 at 20:28 • 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. – pbarney Apr 5 '18 at 20:29 • 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. – Indra Apr 5 '18 at 20:33 ## 3 Answers 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. • 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. – pbarney Apr 6 '18 at 14:02 • One quick question: does this handle my second use case, where the array values could be arrays themselves? – pbarney Apr 6 '18 at 14:03 • yes of course, PDO has PDO::FETCH_UNIQUE mode as well – Your Common Sense Apr 6 '18 at 14:04 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 fors 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 teh 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 write the code through a foe 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 db got to ~1 mil entries. 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 functions 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. • 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 – Indra Apr 5 '18 at 20:47 • 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. – pbarney Apr 5 '18 at 20:55 • @pbarney you're right. let me check another solution – Indra Apr 5 '18 at 20:57 • 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. – pbarney Apr 5 '18 at 21:05 • check out my edit and feel free to give me feedback – Indra Apr 5 '18 at 21:24 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'
],
......
]