How to Select and Display Data From MySQL With PHP

Want to know how to select data from a MySQL database with PHP? Here is the short answer: you connect to the database with mysqli_connect(), run a SELECT query with mysqli_query(), then loop over the results with mysqli_fetch_assoc() to print each row. This guide walks through every step with small, copy-pasteable examples, so you can go from an empty page to a working table of data in a few minutes.

This post assumes you already have a MySQL table with some data in it. If you have not connected PHP to MySQL yet, start with our guide on how to connect PHP to a MySQL database, then come back here.

What you need before you start

• PHP installed and running (locally with XAMPP/MAMP, or on a server).
• A MySQL database with at least one table that has some rows in it. This guide uses a table named students with columns id, name, and email.
• The mysqli extension, which comes built in with most PHP installations.
• A text editor and basic comfort saving .php files.

Step 1: Connect to your MySQL database

Every SELECT query starts with a connection. Save this as connect.php:

<?php
$host = "localhost";
$user = "root";
$password = "";
$database = "school";

$conn = mysqli_connect($host, $user, $password, $database);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
?>

$host, $user, $password, and $database are the details MySQL needs to let you in. mysqli_connect() uses them to open a connection and returns a connection object, or false if it fails. The if (!$conn) check catches a failed connection, and die() stops the script immediately while printing the reason with mysqli_connect_error(). This stops the rest of the page from running with no working database link.

Step 2: Write a SELECT query in PHP

With a connection open, you can send a SQL query as a plain string:

<?php
$sql = "SELECT id, name, email FROM students";
$result = mysqli_query($conn, $sql);
?>

$sql is just a text string holding a normal SQL SELECT statement. mysqli_query($conn, $sql) sends that string to MySQL over the connection you opened in Step 1. If the query succeeds, $result holds a result set: an object you can loop through to read each row. If the query has a mistake, mysqli_query() returns false instead.

Step 3: Fetch rows with mysqli_fetch_assoc()

A result set on its own is not readable text yet. You pull rows out of it one at a time in a loop:

<?php
while ($row = mysqli_fetch_assoc($result)) {
    echo $row["name"] . " - " . $row["email"] . "\n";
}
?>

mysqli_fetch_assoc($result) returns the next row as an associative array, meaning you can read each value by column name, like $row["name"]. It returns null once there are no rows left, which is exactly the condition a while loop needs to stop on its own. Each time through the loop, $row holds one record from your table.

Step 4: Display the data in an HTML table

Mixing that loop with HTML is how you turn raw rows into a table a visitor can actually read:

<?php
$sql = "SELECT id, name, email FROM students";
$result = mysqli_query($conn, $sql);
?>
<table border="1">
  <tr>
    <th>ID</th>
    <th>Name</th>
    <th>Email</th>
  </tr>
  <?php while ($row = mysqli_fetch_assoc($result)): ?>
  <tr>
    <td><?php echo htmlspecialchars($row["id"]); ?></td>
    <td><?php echo htmlspecialchars($row["name"]); ?></td>
    <td><?php echo htmlspecialchars($row["email"]); ?></td>
  </tr>
  <?php endwhile; ?>
</table>

The while (...): ... endwhile; syntax is an alternative loop style that reads more cleanly inside HTML than curly braces do. Each pass prints one table row. htmlspecialchars() converts any special characters (like < or &) in the data into safe text, which stops stored data from accidentally breaking your page or running as code in the browser.

Selecting specific rows with WHERE

Often you do not want every row, just one that matches something, like a specific ID. Never paste a variable straight into the SQL string for this. Use a prepared statement instead, which keeps user input separate from the query:

<?php
$studentId = 5;

$sql = "SELECT id, name, email FROM students WHERE id = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "i", $studentId);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
?>

The ? in the query is a placeholder for a value that gets filled in safely. mysqli_prepare() builds the query without the value yet. mysqli_stmt_bind_param($stmt, "i", $studentId) attaches $studentId to that placeholder, where "i" tells MySQL to treat it as an integer. mysqli_stmt_execute() runs the query, and mysqli_stmt_get_result() converts the result into the same kind of result set you already know how to loop through with mysqli_fetch_assoc().

Handling no results

A query can succeed but still return zero rows, for example searching for an ID that does not exist. Check for that before looping:

<?php
if (mysqli_num_rows($result) === 0) {
    echo "No students found.";
} else {
    while ($row = mysqli_fetch_assoc($result)) {
        echo $row["name"] . "\n";
    }
}

mysqli_close($conn);
?>

mysqli_num_rows($result) counts how many rows came back, so you can show a friendly message instead of an empty table. mysqli_close($conn) closes the database connection once you are done with it, which is good practice at the end of a script.

Common mistakes to avoid

• Putting a variable directly inside the SQL string, like "WHERE id = $id" — this opens the door to SQL injection. Use a prepared statement instead.
• Forgetting to check if mysqli_query() returned false before looping over $result.
• Skipping htmlspecialchars() when printing data that came from users, which can let malicious code run in the browser.
• Not closing the connection with mysqli_close() at the end of long-running scripts.
• Selecting every column with SELECT * when you only need a few — naming columns explicitly is clearer and often faster.