Generating an array from a pair of columns

By default, the PDO fetch and fetchAll methods return each row of a result set as an array indexed both by column name and number. But you can modify this behavior in interesting ways. This is pdo_array.php, which you can find in the chapter four, 04_01 folder of the exercise files. On line 4, there's a simple select query that selects name and meaning from the names table. The query is then executed on the following line and on line 6, the results are stored as an array called names using the fetchAll method.

Let's inspect the content of the names array with print_r to make it easier to read. We'll start off with a pre tag, then print_r. We'll pass it names, that array, and then a closing pre tag. So if we save that page and then load it into a browser, the result is a multidimensional array with each value listed twice. Let's say you wanted to turn this into an associative array using each name as the key and the meaning as the value.

One way to do that would be to loop through each sub-array and use the values to build a new array, but with PDO, there's a much, much simpler way. So let's see how that's done. You modify the behavior of the fetchAll method by passing it a PDO constant as an argument. So, we put the cursor between the parentheses of fetchAll and then PDO, all in uppercase, then the scope resolution operator, two colons, and what we need is fetch. And it's right down here. It's FETCH_KEY_PAIR. And if we save that, go back to the browser and refresh that page, we now have an associative array of the names and the meanings. And that's all there is to it. This isn't necessarily a feature you'll need to use everyday, but it's very useful to know that it exists. So let's just go back and see that again. To create an associative array from the values in two columns, submit the query and use the fetchAll method to get the result, and pass it the constant PDO, FETCH_KEY_PAIR as the argument.

The SELECT query must retrieve only two columns. The first one is used for the array keys and the second one is used for the values.