PHP MySQL Select: A Comprehensive Guide

SELECT is the SQL statement that reads data out of a database — it's the one you'll use more than any other. This guide covers the SELECT syntax, the clauses that shape your results, and the two PHP extensions (mysqli and PDO) you use to run a query and loop over the rows it returns.

If you haven't connected to a database yet, start with PHP MySQL Connect; to put rows into a table first, see PHP MySQL Insert.

What the SELECT Statement Does

SELECT retrieves rows from one or more tables. At its simplest you name the columns you want and the table to read them from:

SELECT column1, column2, ...
FROM table_name;

Use * to select every column. It's convenient for quick exploration but you should name columns explicitly in application code — it's faster, makes your intent clear, and your code won't break when someone adds a column to the table.

-- All columns (fine for the mysql console, avoid in app code)
SELECT * FROM users;

-- Only what you need (preferred)
SELECT id, name, email FROM users;

A SELECT always returns a result set: zero or more rows, each made up of the columns you asked for. PHP gives you that result set as something you can loop over.

Shaping the Result Set

The columns-and-table form is just the start. Five clauses let you filter, sort, group, and combine data. They must appear in this order when you use more than one:

Filtering with WHERE

Return only the users named John:

SELECT name, email
FROM users
WHERE name = 'John';

Sorting with ORDER BY

Sort alphabetically by name. ASC is ascending (the default), DESC is descending:

SELECT name, email
FROM users
ORDER BY name ASC;

Limiting rows with LIMIT

Grab just the 10 newest users — essential for pagination and for not pulling a million rows into memory:

SELECT name, email
FROM users
ORDER BY created_at DESC
LIMIT 10;

Joining tables

JOIN stitches related tables together on a shared key. Here each order is matched to the user who placed it:

SELECT users.name, orders.order_id
FROM users
JOIN orders ON users.id = orders.user_id;

Running a SELECT from PHP

To get data into your PHP code, the flow is always the same:

1. Connect to the database.
2. Run the SELECT statement.
3. Fetch the rows from the result set.
4. Close the connection (PHP also closes it automatically at the end of the request).

Always use prepared statements for user input

Never paste a variable straight into a query string. If $name comes from a form, an attacker can inject SQL and read or destroy your whole database:

// DANGER: SQL injection. Never do this with untrusted input.
$result = $conn->query("SELECT name, email FROM users WHERE name = '$name'");

A prepared statement sends the query and the data separately, so the value can never be interpreted as SQL. This is the single most important habit when reading user-supplied data. See MySQL Prepared Statements for more.

With mysqli

$conn = mysqli_connect("localhost", "username", "password", "database");
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$stmt = $conn->prepare("SELECT name, email FROM users WHERE name = ?");
$name = "John";
$stmt->bind_param("s", $name);   // "s" = the parameter is a string
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "0 results";
}

$stmt->close();
mysqli_close($conn);

fetch_assoc() returns each row as an associative array keyed by column name ($row["name"]). Use fetch_array() if you also want numeric keys, or fetch_all(MYSQLI_ASSOC) to grab every row at once into an array.

With PDO

For new projects, PDO is generally the better choice: it has a consistent API across MySQL, PostgreSQL, SQLite and more, and its prepared statements are clean to read.

$pdo = new PDO("mysql:host=localhost;dbname=database;charset=utf8mb4", "username", "password");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare("SELECT name, email FROM users WHERE name = ?");
$stmt->execute(["John"]);

foreach ($stmt as $row) {
    echo "Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
}

Setting PDO::ERRMODE_EXCEPTION makes failed queries throw exceptions instead of failing silently — wrap database calls in try/catch so problems surface instead of disappearing.

Common Gotchas

• num_rows returns rows, not a boolean. Check > 0 (mysqli) before assuming you got data.
• Selecting * and then reading one column still transfers every column over the wire. Select only what you use.
• Forgetting LIMIT on large tables can pull huge result sets into memory. Paginate.
• String comparison in WHERE is case-insensitive by default in MySQL's common collations — 'john' matches 'John'.

Conclusion

SELECT is the workhorse of every PHP/MySQL application. Name your columns explicitly, shape results with WHERE, ORDER BY, LIMIT and JOIN, and always read user input through prepared statements (mysqli or PDO) to stay safe from SQL injection. From here, learn to change data with MySQL Insert and MySQL Update.

Practice