PHP Fetching Results

How it works

PDO::FETCH_ASSOC makes field names explicit in this example. fetch returns false when no next row is available; a returned row can still contain null fields according to the schema/query. Avoid loading an unbounded result set into memory simply because fetchAll is convenient. Select only the columns needed and enforce authorisation before exposing them in a response.

Run it locally

Run this complete source locally with PHP 8.2 or newer. Save it using the filename shown and run the command below. PHP is not currently available in Bhylu’s browser playground. Apache and MySQL are not needed for an ordinary CLI example.

php -l fetch-results.php
php fetch-results.php

Download this source as a local PHP lab file. Keep lab files separate from the Bhylu project and production data.

Example

<?php
declare(strict_types=1);

$db = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$db->exec('CREATE TABLE loan (id INTEGER PRIMARY KEY, item TEXT NOT NULL, quantity INTEGER NOT NULL CHECK(quantity > 0), returned INTEGER NOT NULL DEFAULT 0)');
$db->exec("INSERT INTO loan (id, item, quantity) VALUES (1, 'Spade', 2), (2, 'Drill', 1), (3, 'Spade', 3)");
$query = $db->prepare("SELECT id,item,quantity FROM loan WHERE id = ?");
$query->execute([2]);
$row = $query->fetch(PDO::FETCH_ASSOC);
if ($row === false) throw new RuntimeException("Fixture row missing");
echo $row["item"], ": ", $row["quantity"], "\n";
$query->execute([999]);
echo $query->fetch(PDO::FETCH_ASSOC) === false ? "No row\n" : "Found\n";

Expected result

Drill: 1
No row

The first result has a known associative shape and the second returns false. The program does not attempt to read item from that missing result. A real not-found response should also consider whether disclosing the record’s existence is appropriate for the current actor.