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 | |
|---|---|---|
| 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)