Introduction

After inserting data into a MySQL database, the next step is retrieving it. Whether you’re displaying blog posts, customer details, products, or user accounts, PHP uses the SQL SELECT statement to fetch records from a database.

This tutorial explains how to retrieve and display data using PHP MySQLi.

What is the SELECT Statement?

The SQL SELECT statement retrieves data from one or more database tables.

Basic SQL syntax:

SELECT * FROM users;

This query returns all rows from the users table.

Sample Database Table

Assume the users table contains:

id	name	email
1	Malik	malik@example.com
2	Adil	adil@example.com
3	Ayesha	ayesha@example.com

Connecting to the Database

<?php

require_once "config.php";

?>

The config.php file contains the MySQL connection.

Retrieving All Records

<?php

$sql = "SELECT * FROM users";

$result = $conn->query($sql);

?>

The query result is stored in the $result variable.

Displaying Records Using a Loop

<?php

while($row = $result->fetch_assoc()){

    echo $row["name"];

    echo "<br>";

}

?>

Output

Malik

Adil

Ayesha

The fetch_assoc() method returns one row at a time as an associative array.

Displaying Multiple Columns

<?php

while($row = $result->fetch_assoc()){

    echo $row["id"] . " - ";

    echo $row["name"] . " - ";

    echo $row["email"];

    echo "<br>";

}

?>

Displaying Data in an HTML Table

<table border="1">

<tr>

<th>ID</th>

<th>Name</th>

<th>Email</th>

</tr>

<?php

while($row = $result->fetch_assoc()){

?>

<tr>

<td><?= $row["id"] ?></td>

<td><?= htmlspecialchars($row["name"]) ?></td>

<td><?= htmlspecialchars($row["email"]) ?></td>

</tr>

<?php

}

?>

</table>

Using htmlspecialchars() helps prevent Cross-Site Scripting (XSS).

Checking for No Records

<?php

if($result->num_rows > 0){

    while($row = $result->fetch_assoc()){

        echo $row["name"];

    }

}else{

    echo "No records found.";

}

?>

This ensures your application handles empty tables gracefully.

Retrieving Specific Columns

Instead of selecting every column:

SELECT name, email FROM users;

Selecting only required columns improves performance.

Real-World Example

A blog website:

Retrieves published posts.
Loops through each record.
Displays the title, featured image, author, and publication date.

The same concept is used for:

Product listings
Customer tables
Employee management systems
CRM dashboards

Best Practices

Select only the required columns.
Escape output using htmlspecialchars().
Check for empty results.
Use prepared statements when filtering data.
Separate database logic from presentation code.

Common Mistakes

❌ Using SELECT * when only one column is needed.

❌ Forgetting to check num_rows.

❌ Displaying raw user input without escaping.

❌ Mixing HTML and SQL logic excessively.

Frequently Asked Questions

What does `fetch_assoc()` do?

It retrieves one row as an associative array where column names become array keys.

Can SELECT retrieve only one record?

Yes.

Example:

SELECT * FROM users WHERE id = 1;

Why use `htmlspecialchars()`?

It prevents malicious HTML or JavaScript from being executed in the browser.

Conclusion

The SELECT statement is one of the most commonly used SQL operations in PHP applications. By combining MySQLi, loops, and secure output practices, you can efficiently display database records in professional websites and web applications.

Mastering data retrieval prepares you for advanced topics like search, filtering, pagination, and dashboards.

Next Tutorial

PHP UPDATE Data in MySQL (2026 Guide)

PHP SELECT Data from MySQL (Beginner Guide 2026)