PDO FETCH_KEY_PAIR
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:
$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">
foreach ($publishers as $publisher_id => $name):
<option value="<?php echo $publisher_id ?>"> echo $name </option>
endforeach
</select>
</body>
</html>
Code language: PHP (php)
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_PAIRmode to fetch the two-column result in an array where the first column is the key and the second column is the value.