PDO FETCH_KEY

Summary: in this tutorial, you’ll learn how to use the PDO FETCH_KEY_PAIR mode to select data from a table.

Introduction to the PDO FETCH_KEY_PAIR mode

Both fetch() and fetchAll() methods accept a very useful fetch mode called PDO::FETCH_KEY_PAIR.

The PDO::FETCH_KEY_PAIR mode allows you to retrieve a two-column result in an array where the first column is the key and the second column is the value.

In practice, you’ll use the PDO::FETCH_KEY_PAIR to fetch data for constructing a <select> element with data that comes from the database.

For example, you can create a <select> element with the values are publisher id and texts are publisher names:

<?php

$pdo = require 'connect.php';

$sql = 'SELECT publisher_id, name 
        FROM publishers';

$statement = $pdo->query($sql);
$publishers = $statement->fetchAll(PDO::FETCH_KEY_PAIR);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Publishers</title>
</head>
<body>
    <label for="publisher">Select a pulisher</label>
    <select name="publisher" id="publisher">
        <?php foreach ($publishers as $publisher_id => $name): ?>
        <option value="<?php echo $publisher_id ?>"><?php echo $name ?></option>
        <?php endforeach ?>
    </select>
</body>
</html>Code language: PHP (php)

Select a pulisher

How it works.

First, connect to the bookdb database.

Second, execute a query that selects the publisher id and name from the publishers table using the query() function.

Third, fetch all rows from the result set using the PDO::FETCH_KEY_PAIR mode. The $publishers array will look like this:

Array
(
    [1] => McGraw-Hill Education
    [2] => Penguin/Random House 
    [3] => Hachette Book Group
    [4] => Harper Collins
    [5] => Simon and Schuster
)Code language: PHP (php)

Finally, iterate over the result set and create the option elements.

Summary

Use the PDO::FETCH_KEY_PAIR mode to fetch the two-column result in an array where the first column is the key and the second column is the value.

Did you find this tutorial useful?