PHP MySQL Select Data: Fetch Records with MySQLi & PDO (Best Practices + Examples)
Last updated 3 months, 3 weeks ago | 319 views 75 5
Introduction: Why PHP MySQL Select Is Essential
When building dynamic websites or applications with PHP, you often need to retrieve data from a MySQL database. Whether you're:
-
Displaying blog posts
-
Loading user profiles
-
Rendering product catalogs
…you’ll need to perform a SELECT query.
In this guide, we’ll walk through how to select data from a MySQL database using MySQLi and PDO, the two main PHP database extensions. You’ll learn the secure, efficient, and best-practice way to fetch, loop through, and display data—perfect for both beginners and working developers.
The SQL SELECT Statement Basics
Before diving into PHP, here’s a simple MySQL query:
SELECT * FROM users;
This retrieves all columns and all rows from the users table.
You can refine it with:
SELECT name, email FROM users WHERE status = 'active';
Fetching Data Using MySQLi
✅ MySQLi Object-Oriented Example
<?php
$conn = new mysqli("localhost", "root", "", "test_db");
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Write SELECT query
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);
// Check if rows exist
if ($result->num_rows > 0) {
// Loop through results
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"] . " - Name: " . $row["name"] .
" - Email: " . $row["email"] . "<br>";
}
} else {
echo "No results found.";
}
$conn->close();
?>
MySQLi with Prepared Statement
<?php
$conn = new mysqli("localhost", "root", "", "test_db");
$stmt = $conn->prepare("SELECT id, name, email FROM users WHERE status = ?");
$stmt->bind_param("s", $status);
$status = "active";
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
echo "Name: " . $row['name'] . " | Email: " . $row['email'] . "<br>";
}
$stmt->close();
$conn->close();
?>
✅ Use prepared statements when filtering data based on user input. It prevents SQL injection.
Fetching Data Using PDO
✅ Basic PDO Example
<?php
try {
$pdo = new PDO("mysql:host=localhost;dbname=test_db", "root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->query("SELECT id, name, email FROM users");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "Name: {$row['name']} | Email: {$row['email']}<br>";
}
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>
PDO with Prepared Statement
<?php
try {
$pdo = new PDO("mysql:host=localhost;dbname=test_db", "root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$status = "active";
$stmt = $pdo->prepare("SELECT name, email FROM users WHERE status = :status");
$stmt->execute([':status' => $status]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as $row) {
echo "User: {$row['name']} | Email: {$row['email']}<br>";
}
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>
Complete Functional Example: Display User List
<?php
$conn = new mysqli("localhost", "root", "", "test_db");
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);
?>
<!DOCTYPE html>
<html>
<head>
<title>User List</title>
</head>
<body>
<h2>Registered Users</h2>
<ul>
<?php if ($result->num_rows > 0): ?>
<?php while($row = $result->fetch_assoc()): ?>
<li>
<?php echo $row["name"] . " (" . $row["email"] . ")"; ?>
</li>
<?php endwhile; ?>
<?php else: ?>
<li>No users found.</li>
<?php endif; ?>
</ul>
</body>
</html>
<?php $conn->close(); ?>
MySQLi vs PDO: SELECT Query Comparison
| Feature | MySQLi | PDO |
|---|---|---|
| Database Support | MySQL only | Multiple databases |
| Prepared Statements | Positional only (?) |
Named or positional |
| Fetch Modes | fetch_assoc(), etc. |
FETCH_ASSOC, FETCH_OBJ, etc. |
| Error Handling | Manual ($conn->error) |
Exception-based |
| Fetch All Rows | ❌ (requires loop) | ✅ fetchAll() |
Tips & Common Pitfalls
✅ Best Practices
-
Always sanitize user input and use prepared statements.
-
Use
PDO::FETCH_ASSOCorfetch_assoc()to avoid numeric indexes. -
Close your connections and free resources using
close()orunset().
❌ Common Mistakes
| Mistake | Solution |
|---|---|
Not checking num_rows or results |
Use conditional checks to prevent errors |
Mixing mysqli and mysql |
Use either MySQLi or PDO, not outdated mysql |
Not using try/catch in PDO |
Wrap code in try/catch to handle DB errors |
| Fetching outside the loop | Use while or foreach to iterate results |
Common Fetch Modes in PDO
| Fetch Mode | Description |
|---|---|
PDO::FETCH_ASSOC |
Returns array with column names only |
PDO::FETCH_NUM |
Returns array with numeric indexes only |
PDO::FETCH_BOTH |
Returns array with both keys (default) |
PDO::FETCH_OBJ |
Returns object instead of array |
Conclusion: Final Thoughts & Best Practices
The SELECT query is at the heart of all read operations in any PHP MySQL application. Knowing how to fetch data securely and efficiently using MySQLi or PDO is crucial for every backend developer.
Summary of Best Practices:
-
Use prepared statements for security.
-
Prefer PDO if you're working across multiple databases.
-
Choose the right fetch mode based on your output needs.
-
Always check if the query returned any rows before using results.
