PHP MySQL Select Data

Reading data back out of a database is at least as common as writing it in -- displaying a list of products, showing a user's profile, populating a search results page. A SELECT statement, run through mysqli and looped over with a fetch function, retrieves rows matching whatever criteria you specify.

In this page:

Basic SELECT and Fetching Results
Checking How Many Rows Were Returned
Fetching a Single Row
Fetching Results as Objects
Fetching All Rows into an Array

Basic SELECT and Fetching Results

mysqli_query($conn, 'SELECT * FROM users') runs the query and returns a result set object, which mysqli_fetch_assoc() then converts one row at a time into an associative array, keyed by each column's name -- looping this fetch call processes every matching row.

Note: Loop with while ($row = mysqli_fetch_assoc($result)) as the standard, idiomatic pattern for processing every row a SELECT query returns.

Warning: A SELECT that matches zero rows is not an error -- mysqli_query() still succeeds, it simply returns a result set with no rows to fetch.

Example: Basic SELECT and Fetching Results

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
$db->exec("INSERT INTO users (name) VALUES ('Alice'), ('Bob')");
$result = $db->query("SELECT * FROM users");
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    echo $row['name'] . "\n";
}
?>

Checking How Many Rows Were Returned

mysqli_num_rows($result) returns the total count of rows a SELECT query matched, which is useful for showing a "no results found" message, displaying a result count, or deciding whether it is worth looping at all.

Note: Check mysqli_num_rows() before looping when you specifically want to handle the zero-results case with a distinct message.

Warning: mysqli_num_rows() requires the entire result set to be buffered first, which is the mysqli default -- for very large result sets, an unbuffered query may be more appropriate.

Example: Checking How Many Rows Were Returned

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
$db->exec("INSERT INTO users (name) VALUES ('Alice'), ('Bob')");
$result = $db->query("SELECT COUNT(*) as total FROM users"); // mysqli_num_rows() equivalent
print_r($result->fetchArray(SQLITE3_ASSOC));
?>

Fetching a Single Row

When a query is expected to return at most one row -- like looking up a specific user by their unique ID -- a single mysqli_fetch_assoc() call, without a loop, is enough to retrieve that one row, or null if nothing matched.

Note: For single-row lookups, skip the while loop entirely and just call fetch_assoc() once, checking whether the result is null.

Warning: Calling fetch_assoc() once on a query that actually matched multiple rows silently retrieves only the first one, ignoring the rest.

Example: Fetching a Single Row

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER, name TEXT)");
$db->exec("INSERT INTO users VALUES (1, 'Alice')");
$result = $db->query("SELECT * FROM users WHERE id = 1");
print_r($result->fetchArray(SQLITE3_ASSOC));
?>

Fetching Results as Objects

mysqli_fetch_object($result) works like mysqli_fetch_assoc() but returns each row as a plain object instead of an associative array -- so instead of $row["name"], you access $row->name, which some developers find reads more naturally.

Note: Choose fetch_assoc() or fetch_object() based on team/project convention and consistency -- both provide the same underlying data, just in a different access style.

Warning: Column names that are not valid PHP property names (like ones with spaces, though rare) can make fetch_object() awkward to use for those specific columns.

Example: Fetching Results as Objects

php

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (name TEXT)");
$pdo->exec("INSERT INTO users (name) VALUES ('Alice')");
$stmt = $pdo->query("SELECT * FROM users");
$row = $stmt->fetchObject(); // mysqli_fetch_object() equivalent
echo $row->name;
?>

Fetching All Rows into an Array

mysqli_fetch_all($result, MYSQLI_ASSOC) retrieves every remaining row from a result set all at once, as an array of associative arrays -- convenient when you want the whole dataset in memory as a single PHP array rather than looping and fetching one row at a time.

Note: Use fetch_all() when you need the entire result as one array anyway (like for json_encode()), rather than a manual loop building up an array yourself.

Warning: fetch_all() loads every matching row into memory at once, which is fine for reasonably sized result sets but can use significant memory for very large ones.

Example: Fetching All Rows into an Array

php

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (name TEXT)");
$pdo->exec("INSERT INTO users (name) VALUES ('Alice'), ('Bob')");
$stmt = $pdo->query("SELECT * FROM users");
print_r($stmt->fetchAll(PDO::FETCH_ASSOC)); // mysqli_fetch_all($result, MYSQLI_ASSOC) equivalent
?>

Common Mistakes

Forgetting to check mysqli_num_rows() before assuming a query returned at least one row, causing an error when trying to fetch from an empty result.
Selecting every column with SELECT * when only one or two specific columns are actually needed, retrieving and transferring more data than necessary.
Mixing up mysqli_fetch_assoc() (returns an associative array keyed by column name) with mysqli_fetch_row() (returns a plain indexed array), and accessing results the wrong way for the fetch style used.

Chapter Summary

SELECT columns FROM table retrieves matching rows, run through mysqli_query() and looped over with a fetch function.
mysqli_fetch_assoc() returns each row as an associative array keyed by column name, the most commonly used fetch style.
mysqli_num_rows() tells you how many rows a query returned, useful for checking before looping or displaying a "no results" message.

Browser Support

SELECT is standard SQL, and running it through mysqli_query() works identically across all MySQL and MariaDB versions PHP supports.

🔒

Chapter Quiz — Complete all 21 topics to unlock

0/21 topics done

Complete these topics first:

← Previous Lesson PHP MySQL Prepared Statements Next Lesson → PHP MySQL Where