PHP Result Sets
Introduction
When you query a database using PHP, the database returns your data in what's called a result set. Think of a result set as a container that holds all the records matching your query. Working with these result sets effectively is essential for building dynamic, data-driven websites and applications.
In this tutorial, we'll explore how to:
- Retrieve data from databases using PHP
- Navigate through result sets
- Extract and process data from result sets
- Use different result handling methods for various scenarios
What Is a Result Set?
A result set is a collection of rows returned by a database in response to a SELECT query. When you execute a query that retrieves data from a database table, the database management system (DBMS) gathers all matching rows and sends them back to your PHP script as a result set.
Here's a visualization of how result sets work:
Fetching Data with MySQLi
PHP offers several ways to work with databases. We'll focus on the MySQLi extension, which is a commonly used method for connecting to MySQL databases.
Basic Steps to Retrieve Data
Let's break down the process of working with result sets:
<?php
// 1. Connect to the database
$conn = new mysqli("localhost", "username", "password", "database_name");
// 2. Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// 3. Execute a query
$sql = "SELECT id, firstname, lastname FROM users";
$result = $conn->query($sql);
// 4. Process the result set
if ($result->num_rows > 0) {
// Loop through each row
while($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"] . " - Name: " . $row["firstname"] . " " . $row["lastname"] . "<br>";
}
} else {
echo "0 results";
}
// 5. Close the connection
$conn->close();
?>
Understanding the Result Object
When you execute a query with $conn->query(), it returns a result object that contains all the data retrieved from the database. This object provides several methods for working with the data:
num_rows: Returns the number of rows in the result setfetch_assoc(): Returns the current row as an associative arrayfetch_row(): Returns the current row as a numeric arrayfetch_array(): Returns the current row as both numeric and associative arraysfetch_object(): Returns the current row as an object
Different Methods for Fetching Results
Let's explore the different ways to extract data from a result set:
1. fetch_assoc()
The fetch_assoc() method returns the current row as an associative array, where column names are used as keys.
<?php
$result = $conn->query("SELECT id, username, email FROM users");
while($row = $result->fetch_assoc()) {
echo "Username: " . $row["username"] . "<br>";
echo "Email: " . $row["email"] . "<br><br>";
}
?>
Output:
Username: john_doe
Email: [email protected]
Username: jane_smith
Email: [email protected]
2. fetch_row()
The fetch_row() method returns the current row as a numeric array, where columns are indexed starting from 0.
<?php
$result = $conn->query("SELECT id, username, email FROM users");
while($row = $result->fetch_row()) {
echo "ID: " . $row[0] . "<br>";
echo "Username: " . $row[1] . "<br>";
echo "Email: " . $row[2] . "<br><br>";
}
?>
Output:
ID: 1
Username: john_doe
Email: [email protected]
ID: 2
Username: jane_smith
Email: [email protected]
3. fetch_array()
The fetch_array() method is versatile as it allows you to access row data using both numeric indices and column names.
<?php
$result = $conn->query("SELECT id, username, email FROM users");
while($row = $result->fetch_array(MYSQLI_BOTH)) {
echo "Using column name: " . $row["username"] . "<br>";
echo "Using numeric index: " . $row[1] . "<br><br>";
}
?>
Output:
Using column name: john_doe
Using numeric index: john_doe
Using column name: jane_smith
Using numeric index: jane_smith
The fetch_array() method accepts these constants:
MYSQLI_ASSOC: Same as fetch_assoc()MYSQLI_NUM: Same as fetch_row()MYSQLI_BOTH: Both numeric and associative (default)
4. fetch_object()
The fetch_object() method returns the current row as an object, where column names become properties.
<?php
$result = $conn->query("SELECT id, username, email FROM users");
while($row = $result->fetch_object()) {
echo "Username: " . $row->username . "<br>";
echo "Email: " . $row->email . "<br><br>";
}
?>
Output:
Username: john_doe
Email: [email protected]
Username: jane_smith
Email: [email protected]
Practical Example: Building a User Dashboard
Let's create a practical example of a user dashboard that displays information from a database.
<?php
// Database connection
$conn = new mysqli("localhost", "username", "password", "userdb");
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Retrieve users with active status
$sql = "SELECT id, username, email, registration_date,
last_login FROM users WHERE status='active' ORDER BY last_login DESC";
$result = $conn->query($sql);
?>
<!DOCTYPE html>
<html>
<head>
<title>User Dashboard</title>
<style>
table { border-collapse: collapse; width: 100%; }
th, td { padding: 8px; text-align: left; border-bottom: 1px solid #ddd; }
tr:hover { background-color: #f5f5f5; }
</style>
</head>
<body>
<h1>Active Users Dashboard</h1>
<p>Total active users: <?php echo $result->num_rows; ?></p>
<?php if($result->num_rows > 0): ?>
<table>
<tr>
<th>ID</th>
<th>Username</th>
<th>Email</th>
<th>Registered</th>
<th>Last Login</th>
</tr>
<?php while($user = $result->fetch_assoc()): ?>
<tr>
<td><?php echo $user["id"]; ?></td>
<td><?php echo $user["username"]; ?></td>
<td><?php echo $user["email"]; ?></td>
<td><?php echo date("M d, Y", strtotime($user["registration_date"])); ?></td>
<td><?php echo date("M d, Y H:i", strtotime($user["last_login"])); ?></td>
</tr>
<?php endwhile; ?>
</table>
<?php else: ?>
<p>No active users found.</p>
<?php endif; ?>
<?php $conn->close(); ?>
</body>
</html>
Working with Large Result Sets
When dealing with large amounts of data, it's important to consider performance. Here are some techniques:
1. Limiting Results
// Retrieve only 10 records
$sql = "SELECT * FROM products ORDER BY price DESC LIMIT 10";
$result = $conn->query($sql);
2. Pagination
<?php
// Set up pagination
$results_per_page = 10;
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $results_per_page;
// Get paginated results
$sql = "SELECT * FROM products ORDER BY name LIMIT $offset, $results_per_page";
$result = $conn->query($sql);
// Get total count for pagination links
$total_sql = "SELECT COUNT(*) AS count FROM products";
$total_result = $conn->query($total_sql);
$total_row = $total_result->fetch_assoc();
$total_pages = ceil($total_row['count'] / $results_per_page);
// Display results
while($row = $result->fetch_assoc()) {
echo $row["name"] . " - $" . $row["price"] . "<br>";
}
// Display pagination links
echo "<div class='pagination'>";
for($i = 1; $i <= $total_pages; $i++) {
echo "<a href='?page=$i'" . ($page == $i ? " class='active'" : "") . ">$i</a> ";
}
echo "</div>";
?>
3. Using Free Result
For very large result sets, it's good practice to free the result when you're done with it:
<?php
$result = $conn->query("SELECT * FROM large_table");
// Process data
while($row = $result->fetch_assoc()) {
// Do something with the row
}
// Free the result set
$result->free();
// Continue with other queries
$another_result = $conn->query("SELECT * FROM another_table");
?>
PDO: An Alternative Approach
While we've focused on MySQLi, another popular method is PHP Data Objects (PDO). Here's how you can work with result sets using PDO:
<?php
try {
// Connect to database
$pdo = new PDO("mysql:host=localhost;dbname=testdb", "username", "password");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Execute query
$stmt = $pdo->query("SELECT id, name, email FROM users");
// Fetch all results at once
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Loop through results
foreach($users as $user) {
echo "Name: " . $user["name"] . ", Email: " . $user["email"] . "<br>";
}
// Or fetch one row at a time
$stmt = $pdo->query("SELECT id, name FROM products");
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "Product: " . $row["name"] . "<br>";
}
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>
PDO offers some advantages:
- Works with multiple database types
- Provides prepared statements for better security
- Offers various fetch modes
Common Mistakes and How to Avoid Them
When working with PHP result sets, beginners often encounter these issues:
1. Not Checking if Results Exist
<?php
// Wrong approach (may cause errors)
$result = $conn->query("SELECT * FROM users WHERE username='john'");
$user = $result->fetch_assoc();
echo $user["email"]; // Error if no user found!
// Better approach
$result = $conn->query("SELECT * FROM users WHERE username='john'");
if($result->num_rows > 0) {
$user = $result->fetch_assoc();
echo $user["email"];
} else {
echo "User not found";
}
?>
2. Trying to Loop Through Empty Results
<?php
// This can cause unexpected behavior if no results
$result = $conn->query("SELECT * FROM empty_table");
while($row = $result->fetch_assoc()) {
// Code here won't run if no results
}
// Better approach:
if($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
// Process rows
}
} else {
echo "No results found";
}
?>
3. Not Sanitizing User Input
Always use prepared statements when working with user input:
<?php
// Unsafe way
$username = $_GET['username'];
$result = $conn->query("SELECT * FROM users WHERE username='$username'");
// Safe way using prepared statements
$stmt = $conn->prepare("SELECT * FROM users WHERE username=?");
$stmt->bind_param("s", $_GET['username']);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_assoc()) {
// Process data safely
}
?>
Summary
Working with result sets in PHP is fundamental to building dynamic, data-driven applications. In this tutorial, we've covered:
- What result sets are and how they work
- Different methods for fetching data from result sets
- Practical examples showing real-world applications
- How to handle large result sets efficiently
- Common mistakes and best practices
By mastering result sets, you'll be able to build more powerful PHP applications that effectively manage and display database data.
Exercises
To reinforce your understanding, try these exercises:
-
Basic Query: Create a script that retrieves and displays all records from a "products" table showing the product name, price, and category.
-
Filtered Results: Modify your script to only show products with a price greater than $20.
-
Sorting: Add functionality to let users sort the results by price (ascending or descending).
-
Pagination: Implement a pagination system to display 5 products per page.
-
Advanced: Create a product search form that allows users to search by name and filter by category, displaying results in a formatted table.
Additional Resources
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)