MySQL – retrieving rows with one column as key

Getting results back from MySQL as an array is of course easy enough, using fetch_all in MySQLi or fetchAll in PDO. You get back an array of rows, and you can loop through it as necessary.

This result array is numerically indexed, from element 0 upwards, reflecting the ORDER BY clause in the query.

Element 0 = array of columns from first row
Element 1 = array of columns from second row, and so on

Fair enough, and often that’s exactly what you want, but what if you want to use the array as a lookup? I’ve encountered that situation several times, where I have a table with key / value pairs, for example, and I want to bring it into memory so that I can use it in my PHP code. Something like this, for example:

PDO provides a really useful option to handle this. The code is something like this:

$sql = 'SELECT region_id, region_name FROM regions';
$result = $this->db->query($sql)->fetchAll(PDO::FETCH_KEY_PAIR);

Note the PDO::FETCH_KEY_PAIR option – it causes the array of rows to be a perfectly formed key/value structure:

[
“EU” => “Europe”
“US” => “United States of America”
“AP” => “Asia Pacific”
]

And now I can use the array as a lookup.

Just recently, though, I came up against a need to have several columns in the lookup array. The situation was a query using GROUP BY, returning counts and sums from a database table based on a number of criteria.

So I wanted a lookup array with a key (as above), but with several columns in each element. For simplicity, let’s say we have a third column (Manager) in the region table, so the query is this:

SELECT region_id, region_name, manager FROM regions

Now, FETCH_KEY_PAIR can’t do this. It will only handle a query with two columns – one to be the key and one to be the value. Well, maybe it could … you might use CONCAT_WS, so that there are only two fields:

SELECT region_id, CONCAT_WS(‘,’ region_name, manager) FROM regions

But let’s stick with the original query. I’d started out by looping through the result and building a second indexed array. It worked, but it looked untidy so I looked around for a more concise approach. The answer, it turns out, is PHP’s array_column() function, which includes an option to reindex an array by one of its columns. Perfect for this situation!

So we code things like this:

$sql = 'SELECT region_id, region_name, manager FROM regions';
$result = $this->db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
$regions = array_column($result, null, 'region_id');

And the resulting $regions array is structured like this:

[
“EU” => [“region_id”=>”EU”, “region_name”=>”Europe”, “manager”=>”Alicia”]
“US” => [“region_id”=>”US”, “region_name”=>”United States of America”, “manager”=>”Tony”]
“AP” => [“region_id”=>”AP”, “region_name”=>”Asia Pacific”, “manager”=>”Leanne”]
]

Note how the region id remains in the array of columns on each row, but that’s a minor inconvenience – the result is what I needed.