PHP PDO SELECT: Read Rows from MySQL

SELECT reads data. Use it with prepare() and execute(), then read the rows.

All rows

index.php

PHP

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

$statement = $pdo->prepare("SELECT id, name, price FROM products ORDER BY id");
$statement->execute();

foreach ($statement as $row) {
    echo $row["id"] . ". " . $row["name"] . " - " . number_format((float) $row["price"], 2) . "\n";
}

Output

OUTPUT

1. Notebook - 80.00
2. Pen - 12.00
3. Bag - 799.00
4. Marker - 45.00
5. Diary - 150.00

You can loop over the statement with foreach, or fetch rows yourself. Fetching Results covers all the ways.

Filter with WHERE

index.php

PHP

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

$statement = $pdo->prepare("SELECT name FROM products WHERE category = :category ORDER BY name");
$statement->execute(["category" => "paper"]);

echo implode(", ", array_column($statement->fetchAll(), "name"));

Output

OUTPUT

Diary, Notebook

Comparisons and ranges

index.php

PHP

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

$statement = $pdo->prepare("SELECT name FROM products WHERE price BETWEEN :low AND :high AND stock > 0 ORDER BY price");
$statement->execute(["low" => 40, "high" => 200]);

echo implode(", ", array_column($statement->fetchAll(), "name"));

Output

OUTPUT

Marker, Notebook, Diary

Sort and limit

index.php

PHP

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

$statement = $pdo->prepare("SELECT name, price FROM products ORDER BY price DESC LIMIT :limit");
$statement->bindValue(":limit", 2, PDO::PARAM_INT);
$statement->execute();

foreach ($statement->fetchAll() as $row) {
    echo $row["name"] . "\n";
}

Output

OUTPUT

1
2

Bag
Diary

The LIMIT value is bound as an integer so MySQL treats it as a number.

One row

index.php

PHP

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

$statement = $pdo->prepare("SELECT * FROM products WHERE id = :id");
$statement->execute(["id" => 3]);

$product = $statement->fetch();

if ($product === false) {
    echo "Product not found";
} else {
    echo $product["name"];
}

Output

OUTPUT

Bag

fetch() returns false when there is no row. Always check for it.

Count rows

index.php

PHP

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

$total = $pdo->query("SELECT COUNT(*) FROM products")->fetchColumn();
$inStock = $pdo->query("SELECT COUNT(*) FROM products WHERE stock > 0")->fetchColumn();

echo "$inStock of $total products are in stock";

Output

OUTPUT

4 of 5 products are in stock

(query() is fine here because the SQL contains no outside input.)

Show results in HTML safely

index.php

PHP

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

$products = $pdo->query("SELECT name, stock FROM products ORDER BY name LIMIT 3")->fetchAll();
?>
<ul>
<?php foreach ($products as $p): ?>
  <li><?= htmlspecialchars($p["name"]) ?> (<?= (int) $p["stock"] ?> in stock)</li>
<?php endforeach; ?>
</ul>

Output

OUTPUT

<ul>
  <li>Bag (0 in stock)</li>
  <li>Diary (4 in stock)</li>
  <li>Marker (30 in stock)</li>
</ul>

Database values are untrusted too: someone may have stored a script in a product name. Escape them when printing.

Select only what you need

SELECT * returns every column, including big ones you do not use. Name the columns you need. It is faster, and it protects you if someone later adds a sensitive column (like password_hash).

LIKE for searching

index.php

PHP

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

$term = "ar";

$statement = $pdo->prepare("SELECT name FROM products WHERE name LIKE :term ORDER BY name");
$statement->execute(["term" => "%" . $term . "%"]);

echo implode(", ", array_column($statement->fetchAll(), "name"));

Output

OUTPUT

Diary, Marker

Add the % wildcards to the value, not in the SQL string. Search is covered more in Searching Records.

lightbulb

One query, not many

Fetch what you need in one query. Running a query inside a loop (one per row) is slow, and a common source of performance problems. See Query Optimization Basics.