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:
|Region ID||Region name|
|US||United States of America|
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.