The simple PHP PDO MySQL fetch (SELECT) types

The basics for PHP MySQL PDO fetch types, whether it be returning just one row, one column or looping through a full table SELECT call.

The common 3 fetch types: fetch(), fetchAll() and fetchColumn().

Fetch and fetchAll have a fetch_style parameter which can ultimately determine the format of the returned data. The most basic of fetch_styles is PDO::FETCH_ASSOC which is an array indexed by column name.

fetchColumn has a parameter for column number returned which by default is 0 (first row).

The mock table structure:

| id | name       | score
|----|------------|------ |
|  1 |       Nick |    86 |
|  2 |       Ryan |    82 |
|  3 |        Ben |    95 |
|  4 |       Andy |    92 |
|  5 |        Rod |    87 |
|  6 |       Matt |    89 |
|  7 |        Tim |    86 |

$id = 4;

Selecting one column

$select = $db->prepare("SELECT `name` FROM `people` WHERE `id` = ? LIMIT 1;");
$select->execute([$id]);
$row = $select->fetchColumn();
echo $row;//Andy

Selecting one row

$select = $db->prepare("SELECT `name`, `score` FROM `people` WHERE `id` = ? LIMIT 1;");
$select->execute([$id]);
$row = $select->fetch(PDO::FETCH_ASSOC);
echo $row['name'];//Andy
echo $row['score'];//92

Selecting multiple rows

$select = $db->prepare("SELECT `name`, `score` FROM `people` WHERE `score` > 90 ORDER BY `score` DESC LIMIT 10;");
$select->execute();
$row = $select->fetchAll(PDO::FETCH_ASSOC);
echo $row[0]['name'];//Ben
echo $row[0]['score'];//95
echo $row[1]['name'];//Andy
echo $row[1]['score'];//92
....

Selecting multiple rows into a loop

$select = $db->prepare("SELECT `name`, `score` FROM `people` WHERE `score` > 90 ORDER BY `score` DESC LIMIT 10;");
$select->execute();
while ($row = $select->fetchAll(PDO::FETCH_ASSOC)) {
    echo "{$row['name']} {$row['score']}<br>";
}

fetch and fetchAll work in very similar ways, the main difference between them is fetchAll being suited to returning an array (large data) whilst fetch returns the next row as determined by the fetch_style parameter.

fetchColumn works well when doing a MySQL COUNT:

$select = $db->prepare("SELECT COUNT(*) FROM `people` WHERE `score` > 90;");
$select->execute();
$count = $select->fetchColumn();//2

Or an if exists type SELECT:

$select = $db->prepare("SELECT COUNT(*) FROM `people` WHERE `id` = ?;");
$select->execute([$id]);
$count = $select->fetchColumn();
if ($count > 0){
    echo "Exists";
} else {
    echo "Doesnt exist";
}