MySQL Select Data

Reading Data From a Table

In simple terms: Most of what a website does is read data: listing products, showing a customer's orders, displaying a blog post. SQL's SELECT statement asks the database for rows, and PHP then turns those rows into arrays or objects to display.

Technically: SELECT columns FROM table returns a result set. In MySQLi, query() and execute_query() return a mysqli_result object for SELECT statements. Rows are fetched one at a time (fetch_assoc(), fetch_object(), fetch_row()), all at once (fetch_all()), as a single value (fetch_column()), or by iterating the result with foreach.

The examples use the five products inserted in MySQL Insert Multiple.

The SELECT Statement

SELECT sku, name, price, stock
FROM products
ORDER BY id;

Filtering with WHERE is covered in MySQL Where, limiting the number of rows in MySQL Limit Data.

Select Only What You Need

SELECT * returns every column. Listing columns explicitly is better:

• Less data travels from the database to PHP.
• Code does not break or change behavior when columns are added later.
• Sensitive columns (such as password hashes) are not fetched by accident.

Fetching Rows One by One: fetch_assoc()

<?php
require __DIR__ . '/src/db.php';

$result = db()->query('SELECT sku, name, price, stock FROM products ORDER BY id');

printf("%-9s %-15s %9s %5s\n", 'SKU', 'Name', 'Price', 'Stock');

while ($row = $result->fetch_assoc()) {
    printf("%-9s %-15s %9s %5d\n", $row['sku'], $row['name'], $row['price'], $row['stock']);
}

echo $result->num_rows . " products\n";

Output

SKU       Name                Price Stock
PEN-01    Gel Pen             20.00   120
NB-A5     Notebook A5         60.00    45
LAMP-01   Desk Lamp         1299.00     8
BAG-02    Backpack           899.00     0
MOUSE-01  Wireless Mouse     599.00    25
5 products

How It Works

1. query() sends the SQL (no user input is involved, so a prepared statement is not required) and returns a mysqli_result.
2. Each call to fetch_assoc() returns the next row as an associative array (['sku' => 'PEN-01', ...]). After the last row, it returns null, which ends the while loop.
3. num_rows tells how many rows the result contains.

By default, MySQLi buffers the whole result in PHP memory, which is why num_rows is available immediately.

Other Ways to Fetch

<?php
require __DIR__ . '/src/db.php';

$db = db();

// All rows at once
$products = $db->query('SELECT sku, name FROM products ORDER BY id')->fetch_all(MYSQLI_ASSOC);
echo count($products) . " rows; first: {$products[0]['name']}\n";

// Iterate the result directly
foreach ($db->query("SELECT name FROM products WHERE category = 'stationery' ORDER BY id") as $row) {
    echo "- {$row['name']}\n";
}

// A single value
echo $db->query('SELECT name FROM products WHERE id = 3')->fetch_column() . "\n";

// A row as an object
$product = $db->query('SELECT sku, name FROM products WHERE id = 5')->fetch_object();
echo "{$product->sku}: {$product->name}\n";

Output

5 rows; first: Gel Pen
- Gel Pen
- Notebook A5
Desk Lamp
MOUSE-01: Wireless Mouse

fetch_all() is convenient for small results; for large results, loop with fetch_assoc() or foreach to process one row at a time.

Data Types of Fetched Values

<?php
require __DIR__ . '/src/db.php';

$summary = db()->query(
    'SELECT COUNT(*) AS products, SUM(stock) AS units, SUM(price * stock) AS stock_value FROM products'
)->fetch_assoc();

var_dump($summary);

printf(
    "%d products, %d units, stock value ₹%s\n",
    $summary['products'],
    $summary['units'],
    number_format((float) $summary['stock_value'], 2)
);

Output

array(3) {
  ["products"]=>
  string(1) "5"
  ["units"]=>
  string(3) "198"
  ["stock_value"]=>
  string(8) "30467.00"
}
5 products, 198 units, stock value ₹30,467.00

With query(), MySQLi uses MySQL's text protocol, and every value arrives as a string (and NULL as PHP null). Convert values where types matter. Two alternatives:

DECIMAL columns remain strings in all cases, preserving exact values.

Mapping Rows to Objects

Turning rows into objects of your own classes gives them types and behavior:

<?php
require __DIR__ . '/src/db.php';

final class Product
{
    public function __construct(
        public readonly string $sku,
        public readonly string $name,
        public readonly int $pricePaise,
        public readonly int $stock,
    ) {}

    public static function fromRow(array $row): self
    {
        return new self(
            $row['sku'],
            $row['name'],
            (int) round((float) $row['price'] * 100),
            (int) $row['stock'],
        );
    }

    public function isAvailable(): bool
    {
        return $this->stock > 0;
    }
}

$result = db()->query('SELECT sku, name, price, stock FROM products ORDER BY id');
$products = array_map(Product::fromRow(...), $result->fetch_all(MYSQLI_ASSOC));

$available = array_filter($products, fn(Product $p): bool => $p->isAvailable());

echo count($available) . " of " . count($products) . " products are available\n";

Output

4 of 5 products are available

The conversion from strings to proper types happens once, in fromRow(). The rest of the application works with typed Product objects (see PHP Static Methods). Repositories and ORMs automate this mapping.

Practical Example: A Product Listing Page

<?php
require __DIR__ . '/../src/db.php';

$result = db()->query("SELECT sku, name, price, stock FROM products WHERE stock > 0 ORDER BY name");

function e(string $value): string
{
    return htmlspecialchars($value, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}
?>
<table>
    <tr><th>Product</th><th>Price</th><th>Stock</th></tr>
    <?php foreach ($result as $row): ?>
        <tr>
            <td><?= e($row['name']) ?> <small>(<?= e($row['sku']) ?>)</small></td>
            <td>₹<?= e(number_format((float) $row['price'], 2)) ?></td>
            <td><?= (int) $row['stock'] ?></td>
        </tr>
    <?php endforeach; ?>
</table>

Output (first two rows of the generated HTML)

<tr>
    <td>Desk Lamp <small>(LAMP-01)</small></td>
    <td>₹1,299.00</td>
    <td>8</td>
</tr>
<tr>
    <td>Gel Pen <small>(PEN-01)</small></td>
    <td>₹20.00</td>
    <td>120</td>
</tr>

Why the Escaping Matters

Data from the database is still untrusted for HTML output: product names were entered by people and could contain <script> tags. Every value is escaped with htmlspecialchars() when printed (see Escaping Output and XSS Prevention).

Large Result Sets

For reports or exports with many rows, buffering the whole result in PHP memory is wasteful. An unbuffered query streams rows from the server:

<?php
$result = db()->query('SELECT id, email FROM customers', MYSQLI_USE_RESULT);

foreach ($result as $row) {
    // process one row at a time, e.g. write it to a CSV file
}

$result->free();

With MYSQLI_USE_RESULT, num_rows is not known in advance, and no other query can be run on the connection until all rows are read and the result is freed.

Common Mistakes

Using SELECT *

Effect: Unneeded data, accidental exposure of sensitive columns, and fragile code.

Fix: List the required columns.

Forgetting That Values Are Strings

Effect: Strict comparisons such as $row['stock'] === 0 fail with query() results.

Fix: Convert values ((int), (float)) or use prepared statements.

Fetching Everything With fetch_all() for Large Tables

Effect: High memory usage.

Fix: Iterate row by row, or use unbuffered queries.

Printing Database Values Without Escaping

Effect: Stored XSS attacks.

Fix: Escape every value in HTML output.

Best Practices

• Select only the columns you need, and always specify the order.
• Use query() for fixed SQL and prepared statements whenever values come from outside.
• Choose the fetch method that fits: fetch_assoc()/foreach for loops, fetch_column() for single values, fetch_all() for small sets.
• Convert rows into typed objects at the data-access boundary.
• Escape all database values when rendering HTML.

Official reference: https\://www.php.net/manual/en/mysqli-result.fetch-assoc.php