Reading Rows and MySQL Types in PHP

<?php
$pdo = require __DIR__ . '/db.php';

$stmt = $pdo->query('SELECT id, name, price FROM products WHERE category = "writing" ORDER BY id');
print_r($stmt->fetch());                          // one row, as an array

$names = $pdo->query('SELECT name FROM products ORDER BY id LIMIT 3')->fetchAll(PDO::FETCH_COLUMN);
echo implode(', ', $names), "\n";                 // one column as a list

$prices = $pdo->query('SELECT name, price FROM products ORDER BY id LIMIT 3')->fetchAll(PDO::FETCH_KEY_PAIR);
print_r($prices);                                 // [name => price]

$count = $pdo->query('SELECT COUNT(*) FROM products')->fetchColumn();
var_dump($count);                                 // one value

Array
(
    [id] => 2
    [name] => Pen
    [price] => 12.50
)
Notebook, Pen, Backpack
Array
(
    [Notebook] => 80.00
    [Pen] => 12.50
    [Backpack] => 799.00
)
int(8)

<?php
$pdo = require __DIR__ . '/db.php';

$row = $pdo->query('SELECT 1 AS int_col, 1.5 AS decimal_col, CAST(1.5 AS DOUBLE) AS double_col, "text" AS text_col, NULL AS null_col, TRUE AS bool_col, NOW() IS NOT NULL AS flag')->fetch();
foreach ($row as $name => $value) {
    echo str_pad($name, 12), get_debug_type($value), "\n";
}

int_col     int
decimal_col string
double_col  float
text_col    string
null_col    null
bool_col    int
flag        int

<?php
$pdo = require __DIR__ . '/db.php';

$stmt = $pdo->prepare('SELECT id, price, stock, created_at FROM products JOIN (SELECT created_at FROM customers LIMIT 1) c WHERE products.id = ?');
$stmt->execute([1]);
foreach ($stmt->fetch() as $name => $value) {
    echo str_pad($name, 12), get_debug_type($value), ' ', var_export($value, true), "\n";
}

id          int 1
price       string '80.00'
stock       int 25
created_at  string '2026-01-01 10:00:00'

<?php
$pdo = require __DIR__ . '/db.php';

$sum = $pdo->query("SELECT SUM(price) FROM products WHERE category = 'writing'")->fetchColumn();
var_dump($sum);

$float = 0.0;
foreach ([12.50, 45.00, 5.00] as $price) { $float += $price; }
var_dump($float);
echo 0.1 + 0.2 == 0.3 ? "equal\n" : "0.1 + 0.2 is not 0.3 as a float\n";

// exact sum in whole paise, using string maths from the database (or the bcmath extension if installed)
$paise = (int) $pdo->query("SELECT CAST(SUM(price) * 100 AS UNSIGNED) FROM products WHERE category = 'writing'")->fetchColumn();
echo intdiv($paise, 100), '.', str_pad((string) ($paise % 100), 2, '0', STR_PAD_LEFT), "\n";

string(5) "62.50"
float(62.5)
0.1 + 0.2 is not 0.3 as a float
62.50

<?php
$pdo = require __DIR__ . '/db.php';

$value = $pdo->query('SELECT created_at FROM customers WHERE id = 1')->fetchColumn();
var_dump($value);

$dt = new DateTimeImmutable($value, new DateTimeZone('UTC'));       // you decide the zone
echo $dt->setTimezone(new DateTimeZone('Asia/Kolkata'))->format('d M Y, H:i'), "\n";

string(19) "2026-01-01 10:00:00"
01 Jan 2026, 15:30

<?php
$pdo = require __DIR__ . '/db.php';

foreach ($pdo->query('SELECT name, city FROM customers ORDER BY id') as $row) {
    echo $row['name'], ': ', $row['city'] ?? 'no city', "\n";
}

Maya: Pune
Rohan: Delhi
Isha: no city

<?php
$pdo = require __DIR__ . '/db.php';

$stmt = $pdo->query('SELECT name, attrs FROM products WHERE attrs IS NOT NULL ORDER BY id LIMIT 2');
foreach ($stmt as $row) {
    $attrs = json_decode($row['attrs'], true, 512, JSON_THROW_ON_ERROR);
    echo $row['name'], ': ', json_encode($attrs), "\n";
}

// let MySQL extract a single value
echo $pdo->query("SELECT attrs->>'$.color' FROM products WHERE name = 'Pen'")->fetchColumn(), "\n";

// write a JSON value from PHP
$pdo->prepare('UPDATE products SET attrs = ? WHERE name = ?')->execute([json_encode(['color' => 'red', 'tags' => ['school', 'office']]), 'Pen']);
echo $pdo->query("SELECT JSON_EXTRACT(attrs, '$.tags[1]') FROM products WHERE name = 'Pen'")->fetchColumn(), "\n";

Notebook: {"pages":200,"ruled":true}
Pen: {"color":"blue"}
blue
"office"

<?php
$pdo = require __DIR__ . '/db.php';
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$stmt = $pdo->query('SELECT id, name FROM products ORDER BY id');
$n = 0;
while ($row = $stmt->fetch()) {
    $n++;                       // process one row at a time, memory stays small
}
$stmt->closeCursor();           // you must finish or close it before the next query
echo "read $n rows\n";

read 8 rows