Fetch Data from MySQL using PHP

Introduction

Fetching data from a database is one of the most common tasks in PHP applications. Once data is stored in MySQL, PHP can retrieve and display it on web pages. This is how websites show user profiles, blog posts, product lists, and any dynamic content.

In this tutorial, you’ll learn how to fetch data from MySQL using PHP with the mysqli extension and display it on a webpage.

Prerequisites

Before fetching data, make sure:

• Database connection is working
• A database and table already exist
• Data exists in the table
• You have a table like users

Example table:

id	name	email

Basic SQL SELECT Query

To retrieve data from a table, the SELECT statement is used.

SELECT * FROM users;

This query selects all rows and columns from the users table.

You can also select specific columns:

SELECT name, email FROM users;

This fetches only the required data instead of the entire table.

Fetch Data Using mysqli_query()

Example

<?php
include "db.php";

$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);
?>

• $result stores the fetched data
• We now need to loop through it

Fetch Data Using mysqli_fetch_assoc()

This is the most recommended method.

<?php
while ($row = mysqli_fetch_assoc($result)) {
    echo "Name: " . $row['name'] . "<br>";
    echo "Email: " . $row['email'] . "<br><br>";
}
?>

Explanation

• mysqli_fetch_assoc() returns one row at a time
• The while loop is used to display multiple records
• Data is returned as an associative array
• Column names are used as keys
• Each row is printed on the page

Fetch Data Using mysqli_fetch_array()

while ($row = mysqli_fetch_array($result)) {
    echo $row['name'];
}

This method supports both indexed and associative arrays, but is less preferred.

Fetch a Single Row

Sometimes you only need one row, such as fetching user details by ID.
You can use a WHERE clause in your SELECT query to retrieve a specific record.

$row = mysqli_fetch_assoc($result);
echo $row['name'];

Useful for:

• User profile pages
• Single record views

Check If Data Exists

Sometimes the table may not contain any data.
It’s a good practice to check whether the query returned any rows before displaying results.
This helps avoid showing empty output or confusing messages to users.

if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo $row['name'] . "<br>";
    }
} else {
    echo "No records found.";
}

Display Data in an HTML Table

• Fetched data is usually displayed inside HTML elements like tables or lists.
• You can loop through database results and show them inside an HTML table to create a user list, product list, or report page.

<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 $row['id']; ?></td>
    <td><?php echo $row['name']; ?></td>
    <td><?php echo $row['email']; ?></td>
</tr>
<?php } ?>
</table>

This is commonly used in admin panels.

Practical Use Cases

Fetching data is used in many real-world scenarios, such as:

• Displaying registered users
• Showing blog posts
• Listing products in an online store
• Displaying comments on an article
• Showing reports in an admin panel

This is one of the most important operations in any PHP-based application.

Common Mistakes to Avoid

• Forgetting to include database connection file
• Forgetting to check mysqli_num_rows()
• Using wrong table or column names
• Trying to fetch data before inserting any records
• Not handling empty results
• Mixing HTML and PHP in an unorganized way
• Closing connection too early
• Not handling query errors

Security Note ⚠️

• Do not display sensitive data directly
• Escape output if displaying user data
• Use prepared statements when filtering data

Practical Task

Fetch all records from a table and display them on a web page in a simple format.
Try modifying the query to fetch only selected columns or a single record.

Summary

• SELECT queries are used to fetch data
• mysqli_fetch_assoc() is the best method
• Loops are used to display multiple records
• Data can be displayed in tables or lists

In the next tutorial, you can cover Update and Delete Records in MySQL using PHP.