Handling Result Sets
Learning Objectives
- Master different fetch methods and modes
- Implement efficient pagination systems
- Build advanced search and filtering
- Create dynamic sorting mechanisms
- Handle large datasets efficiently
- Transform and format data for display
- Export data in various formats
- Optimize memory usage with result sets
Understanding Result Sets
Result sets are the data returned from database queries. Let's master how to efficiently process and display this data! 📊
Result Set Key Concepts
- Fetch Methods:Different ways to retrieve data from result sets
- Fetch Modes:Control the format of returned data
- Buffered vs Unbuffered:Memory management strategies
- Cursor Position:Track current position in result set
- Memory Efficiency:Handle large datasets without exhausting memory
Fetch Methods and Modes
graph TB
RESULT[Result Set] --> FETCH[Fetch Methods]
FETCH --> SINGLE[Single Row]
FETCH --> MULTI[Multiple Rows]
FETCH --> COLUMN[Single Column]
SINGLE --> |fetch| ASSOC[Associative Array]
SINGLE --> |fetch| NUM[Numeric Array]
SINGLE --> |fetch| OBJ[Object]
MULTI --> |fetchAll| ALL[All Rows]
COLUMN --> |fetchColumn| COL[Column Values]
style RESULT fill:#e9d5ff
style FETCH fill:#dbeafe
style SINGLE fill:#dcfce7
style MULTI fill:#fef3c7
Working with Different Fetch Methods
<?php
// ========================================
// FETCH METHODS COMPARISON
// ========================================
class ResultSetHandler {
private $pdo;
public function __construct(PDO $pdo) {
$this->pdo = $pdo;
}
/**
* Demonstrate different fetch methods
*/
public function demonstrateFetchMethods() {
$sql = "SELECT id, name, email, created_at FROM users LIMIT 5";
$stmt = $this->pdo->query($sql);
// ========================================
// FETCH() - Single row at a time
// ========================================
echo "=== FETCH() - One row at a time ===\n";
$stmt = $this->pdo->query($sql);
while ($row = $stmt->fetch()) {
echo "User: {$row['name']} ({$row['email']})\n";
}
// ========================================
// FETCHALL() - All rows at once
// ========================================
echo "\n=== FETCHALL() - All rows ===\n";
$stmt = $this->pdo->query($sql);
$allUsers = $stmt->fetchAll();
foreach ($allUsers as $user) {
echo "User: {$user['name']}\n";
}
// ========================================
// FETCHCOLUMN() - Single column value
// ========================================
echo "\n=== FETCHCOLUMN() - Single column ===\n";
$sql = "SELECT COUNT(*) FROM users WHERE status = 'active'";
$count = $this->pdo->query($sql)->fetchColumn();
echo "Active users: $count\n";
// Get specific column
$sql = "SELECT name FROM users WHERE id = 1";
$name = $this->pdo->query($sql)->fetchColumn();
echo "User name: $name\n";
}
/**
* Demonstrate different fetch modes
*/
public function demonstrateFetchModes() {
$sql = "SELECT id, name, email FROM users LIMIT 3";
// ========================================
// PDO::FETCH_ASSOC - Associative array
// ========================================
echo "=== FETCH_ASSOC ===\n";
$stmt = $this->pdo->query($sql);
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$row = $stmt->fetch();
print_r($row);
// Output: Array([id] => 1, [name] => John, [email] => john@example.com)
// ========================================
// PDO::FETCH_NUM - Numeric array
// ========================================
echo "\n=== FETCH_NUM ===\n";
$stmt = $this->pdo->query($sql);
$stmt->setFetchMode(PDO::FETCH_NUM);
$row = $stmt->fetch();
print_r($row);
// Output: Array([0] => 1, [1] => John, [2] => john@example.com)
// ========================================
// PDO::FETCH_BOTH - Both numeric and associative
// ========================================
echo "\n=== FETCH_BOTH ===\n";
$stmt = $this->pdo->query($sql);
$stmt->setFetchMode(PDO::FETCH_BOTH);
$row = $stmt->fetch();
print_r($row);
// Has both numeric and associative keys
// ========================================
// PDO::FETCH_OBJ - Anonymous object
// ========================================
echo "\n=== FETCH_OBJ ===\n";
$stmt = $this->pdo->query($sql);
$stmt->setFetchMode(PDO::FETCH_OBJ);
$user = $stmt->fetch();
echo "Name: {$user->name}, Email: {$user->email}\n";
// ========================================
// PDO::FETCH_CLASS - Into specific class
// ========================================
echo "\n=== FETCH_CLASS ===\n";
class User {
public $id;
public $name;
public $email;
public function getInfo() {
return "{$this->name} ({$this->email})";
}
}
$stmt = $this->pdo->query($sql);
$stmt->setFetchMode(PDO::FETCH_CLASS, 'User');
$user = $stmt->fetch();
echo $user->getInfo() . "\n";
// ========================================
// PDO::FETCH_KEY_PAIR - Key-value pairs
// ========================================
echo "\n=== FETCH_KEY_PAIR ===\n";
$sql = "SELECT id, name FROM users LIMIT 5";
$users = $this->pdo->query($sql)->fetchAll(PDO::FETCH_KEY_PAIR);
print_r($users);
// Output: Array([1] => John, [2] => Jane, ...)
// ========================================
// PDO::FETCH_GROUP - Group by first column
// ========================================
echo "\n=== FETCH_GROUP ===\n";
$sql = "SELECT category_id, name FROM products";
$stmt = $this->pdo->query($sql);
$products = $stmt->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_COLUMN);
// Groups products by category_id
// ========================================
// PDO::FETCH_UNIQUE - Indexed by unique column
// ========================================
echo "\n=== FETCH_UNIQUE ===\n";
$sql = "SELECT id, name, email FROM users";
$users = $this->pdo->query($sql)->fetchAll(PDO::FETCH_UNIQUE);
// Array indexed by ID
}
/**
* Memory-efficient processing of large result sets
*/
public function processLargeResultSet() {
// Use unbuffered query for large datasets
$this->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$sql = "SELECT * FROM large_table";
$stmt = $this->pdo->query($sql);
$processedCount = 0;
$batchSize = 1000;
$batch = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$batch[] = $row;
if (count($batch) >= $batchSize) {
$this->processBatch($batch);
$batch = [];
$processedCount += $batchSize;
echo "Processed: $processedCount rows\n";
// Free some memory
if ($processedCount % 10000 == 0) {
gc_collect_cycles();
}
}
}
// Process remaining batch
if (!empty($batch)) {
$this->processBatch($batch);
}
// Re-enable buffered queries
$this->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
}
private function processBatch($batch) {
// Process batch of records
foreach ($batch as $record) {
// Process each record
}
}
}Pagination Implementation
Advanced Pagination System
<?php
// ========================================
// PAGINATION CLASS
// ========================================
class Paginator {
private $pdo;
private $table;
private $perPage;
private $currentPage;
private $totalRecords;
private $totalPages;
public function __construct(PDO $pdo, $table, $perPage = 20) {
$this->pdo = $pdo;
$this->table = $table;
$this->perPage = $perPage;
}
/**
* Get paginated results
*/
public function paginate($page = 1, $conditions = [], $orderBy = 'id DESC') {
$this->currentPage = max(1, (int)$page);
// Build WHERE clause
$where = $this->buildWhereClause($conditions);
// Count total records
$countSql = "SELECT COUNT(*) FROM {$this->table} {$where}";
$this->totalRecords = $this->pdo->query($countSql)->fetchColumn();
$this->totalPages = ceil($this->totalRecords / $this->perPage);
// Adjust current page if out of range
if ($this->currentPage > $this->totalPages && $this->totalPages > 0) {
$this->currentPage = $this->totalPages;
}
// Calculate offset
$offset = ($this->currentPage - 1) * $this->perPage;
// Get records for current page
$sql = "SELECT * FROM {$this->table}
{$where}
ORDER BY {$orderBy}
LIMIT {$this->perPage} OFFSET {$offset}";
$stmt = $this->pdo->query($sql);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
return [
'data' => $data,
'pagination' => $this->getPaginationInfo()
];
}
/**
* Get pagination information
*/
public function getPaginationInfo() {
return [
'total' => $this->totalRecords,
'per_page' => $this->perPage,
'current_page' => $this->currentPage,
'last_page' => $this->totalPages,
'from' => ($this->currentPage - 1) * $this->perPage + 1,
'to' => min($this->currentPage * $this->perPage, $this->totalRecords),
'prev_page' => $this->currentPage > 1 ? $this->currentPage - 1 : null,
'next_page' => $this->currentPage < $this->totalPages ? $this->currentPage + 1 : null,
'links' => $this->generatePageLinks()
];
}
/**
* Generate page links for UI
*/
private function generatePageLinks($maxLinks = 7) {
$links = [];
if ($this->totalPages <= $maxLinks) {
// Show all pages
for ($i = 1; $i <= $this->totalPages; $i++) {
$links[] = [
'page' => $i,
'active' => $i === $this->currentPage
];
}
} else {
// Show limited pages with ellipsis
$halfLinks = floor($maxLinks / 2);
$start = max(1, $this->currentPage - $halfLinks);
$end = min($this->totalPages, $this->currentPage + $halfLinks);
// Always show first page
if ($start > 1) {
$links[] = ['page' => 1, 'active' => false];
if ($start > 2) {
$links[] = ['page' => '...', 'active' => false];
}
}
// Middle pages
for ($i = $start; $i <= $end; $i++) {
$links[] = [
'page' => $i,
'active' => $i === $this->currentPage
];
}
// Always show last page
if ($end < $this->totalPages) {
if ($end < $this->totalPages - 1) {
$links[] = ['page' => '...', 'active' => false];
}
$links[] = ['page' => $this->totalPages, 'active' => false];
}
}
return $links;
}
/**
* Build WHERE clause from conditions
*/
private function buildWhereClause($conditions) {
if (empty($conditions)) {
return '';
}
$clauses = [];
foreach ($conditions as $field => $value) {
if (is_array($value)) {
// IN clause
$placeholders = str_repeat('?,', count($value) - 1) . '?';
$clauses[] = "{$field} IN ({$placeholders})";
} else {
$clauses[] = "{$field} = ?";
}
}
return 'WHERE ' . implode(' AND ', $clauses);
}
/**
* Render pagination HTML
*/
public function renderPagination($baseUrl = '') {
$info = $this->getPaginationInfo();
$html = '<nav aria-label="Pagination">';
$html .= '<ul class="pagination">';
// Previous button
if ($info['prev_page']) {
$html .= '<li class="page-item">';
$html .= '<a class="page-link" href="' . $baseUrl . '?page=' . $info['prev_page'] . '">Previous</a>';
$html .= '</li>';
} else {
$html .= '<li class="page-item disabled">';
$html .= '<span class="page-link">Previous</span>';
$html .= '</li>';
}
// Page numbers
foreach ($info['links'] as $link) {
if ($link['page'] === '...') {
$html .= '<li class="page-item disabled">';
$html .= '<span class="page-link">...</span>';
$html .= '</li>';
} else {
$active = $link['active'] ? 'active' : '';
$html .= '<li class="page-item ' . $active . '">';
$html .= '<a class="page-link" href="' . $baseUrl . '?page=' . $link['page'] . '">';
$html .= $link['page'] . '</a>';
$html .= '</li>';
}
}
// Next button
if ($info['next_page']) {
$html .= '<li class="page-item">';
$html .= '<a class="page-link" href="' . $baseUrl . '?page=' . $info['next_page'] . '">Next</a>';
$html .= '</li>';
} else {
$html .= '<li class="page-item disabled">';
$html .= '<span class="page-link">Next</span>';
$html .= '</li>';
}
$html .= '</ul>';
$html .= '</nav>';
// Info text
$html .= '<p class="pagination-info">';
$html .= "Showing {$info['from']} to {$info['to']} of {$info['total']} entries";
$html .= '</p>';
return $html;
}
}
// Usage Example
$paginator = new Paginator($pdo, 'products', 20);
$result = $paginator->paginate($_GET['page'] ?? 1);
// Display data
foreach ($result['data'] as $product) {
echo $product['name'] . '<br>';
}
// Display pagination
echo $paginator->renderPagination('/products');Advanced Filtering and Searching
Dynamic Filter System
<?php
// ========================================
// ADVANCED FILTER CLASS
// ========================================
class DataFilter {
private $pdo;
private $query;
private $bindings = [];
private $table;
public function __construct(PDO $pdo, $table) {
$this->pdo = $pdo;
$this->table = $table;
$this->query = "SELECT * FROM {$table}";
}
/**
* Add search condition
*/
public function search($fields, $searchTerm) {
if (empty($searchTerm)) {
return $this;
}
$conditions = [];
foreach ($fields as $field) {
$conditions[] = "{$field} LIKE :search_{$field}";
$this->bindings[":search_{$field}"] = '%' . $searchTerm . '%';
}
$this->addWhere('(' . implode(' OR ', $conditions) . ')');
return $this;
}
/**
* Add exact match filter
*/
public function where($field, $operator, $value = null) {
if ($value === null) {
$value = $operator;
$operator = '=';
}
$placeholder = ':where_' . str_replace('.', '_', $field);
$this->addWhere("{$field} {$operator} {$placeholder}");
$this->bindings[$placeholder] = $value;
return $this;
}
/**
* Add IN condition
*/
public function whereIn($field, array $values) {
if (empty($values)) {
return $this;
}
$placeholders = [];
foreach ($values as $i => $value) {
$placeholder = ":in_{$field}_{$i}";
$placeholders[] = $placeholder;
$this->bindings[$placeholder] = $value;
}
$this->addWhere("{$field} IN (" . implode(', ', $placeholders) . ")");
return $this;
}
/**
* Add date range filter
*/
public function dateRange($field, $startDate, $endDate) {
if ($startDate) {
$this->where($field, '>=', $startDate);
}
if ($endDate) {
$this->where($field, '<=', $endDate);
}
return $this;
}
/**
* Add price/number range filter
*/
public function numberRange($field, $min, $max) {
if ($min !== null) {
$this->where($field, '>=', $min);
}
if ($max !== null) {
$this->where($field, '<=', $max);
}
return $this;
}
/**
* Add sorting
*/
public function orderBy($field, $direction = 'ASC') {
// Validate direction
$direction = strtoupper($direction) === 'DESC' ? 'DESC' : 'ASC';
// Validate field (whitelist approach)
$allowedFields = $this->getAllowedSortFields();
if (!in_array($field, $allowedFields)) {
$field = 'id'; // Default
}
$this->query .= " ORDER BY {$field} {$direction}";
return $this;
}
/**
* Execute query and get results
*/
public function get($limit = null, $offset = null) {
if ($limit !== null) {
$this->query .= " LIMIT " . (int)$limit;
if ($offset !== null) {
$this->query .= " OFFSET " . (int)$offset;
}
}
$stmt = $this->pdo->prepare($this->query);
$stmt->execute($this->bindings);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
/**
* Get count of filtered results
*/
public function count() {
$countQuery = str_replace('SELECT *', 'SELECT COUNT(*) as count', $this->query);
$stmt = $this->pdo->prepare($countQuery);
$stmt->execute($this->bindings);
return $stmt->fetchColumn();
}
/**
* Add WHERE clause
*/
private function addWhere($condition) {
if (strpos($this->query, 'WHERE') === false) {
$this->query .= " WHERE " . $condition;
} else {
$this->query .= " AND " . $condition;
}
}
/**
* Get allowed sort fields (security)
*/
private function getAllowedSortFields() {
// This should be customized per table
return ['id', 'name', 'price', 'created_at', 'updated_at'];
}
}
// ========================================
// USAGE EXAMPLE
// ========================================
// Build complex filter from user input
$filter = new DataFilter($pdo, 'products');
$results = $filter
->search(['name', 'description'], $_GET['q'] ?? '')
->whereIn('category_id', $_GET['categories'] ?? [])
->numberRange('price', $_GET['min_price'] ?? null, $_GET['max_price'] ?? null)
->dateRange('created_at', $_GET['from_date'] ?? null, $_GET['to_date'] ?? null)
->where('status', 'active')
->orderBy($_GET['sort'] ?? 'name', $_GET['order'] ?? 'ASC')
->get(20, (($_GET['page'] ?? 1) - 1) * 20);
// Get total count for pagination
$totalCount = $filter->count();Practice Exercise
Result Set Challenges
Implement these features:
- Build an infinite scroll pagination system
- Create a multi-column sortable table
- Implement faceted search with filters
- Build CSV/Excel export functionality
- Create a data grid with inline editing
- Implement real-time search suggestions
- Build a report generator with charts
- Create a batch processing system