English
English
In daily development, we often encounter the need to paginate the database and return the results as objects. PDO extensions of PHP provide a very convenient way to do this, where the PDOStatement::fetchObject method is especially suitable for directly mapping query results into objects.
This article will demonstrate through examples how to implement paging query in combination with fetchObject and correctly process object data.
First, make sure that your environment has PDO extension enabled and the database is connected. For example:
<?php
$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8mb4';
$username = 'dbuser';
$password = 'dbpass';
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die('Database connection failed: ' . $e->getMessage());
}
?>
For paging, we need to set LIMIT and OFFSET . Suppose we want to query the data of a users table, and 5 records are displayed per page.
<?php
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$page = max($page, 1); // The smallest is the1Page
$pageSize = 5;
$offset = ($page - 1) * $pageSize;
$sql = 'SELECT id, username, email FROM users ORDER BY id ASC LIMIT :limit OFFSET :offset';
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':limit', $pageSize, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
?>
Next, we map the result into an object line by line through the fetchObject method. You can customize a class or use PHP's built-in stdClass directly.
Examples use stdClass :
<?php
$users = [];
while ($user = $stmt->fetchObject()) {
$users[] = $user;
}
// Print an array of objects
foreach ($users as $user) {
echo 'ID: ' . $user->id . '<br>';
echo 'username: ' . htmlspecialchars($user->username, ENT_QUOTES, 'UTF-8') . '<br>';
echo 'Mail: ' . htmlspecialchars($user->email, ENT_QUOTES, 'UTF-8') . '<br><br>';
}
?>
If you want to use a custom user class, you can define it like this and pass it in:
<?php
class User
{
public $id;
public $username;
public $email;
public function getProfileUrl()
{
return 'https://gitbox.net/user/profile.php?id=' . urlencode($this->id);
}
}
$users = [];
while ($user = $stmt->fetchObject(User::class)) {
$users[] = $user;
}
// Print an array of objects
foreach ($users as $user) {
echo 'ID: ' . $user->id . '<br>';
echo 'username: ' . htmlspecialchars($user->username, ENT_QUOTES, 'UTF-8') . '<br>';
echo 'Mail: ' . htmlspecialchars($user->email, ENT_QUOTES, 'UTF-8') . '<br>';
echo 'Profile link: <a href="' . htmlspecialchars($user->getProfileUrl(), ENT_QUOTES, 'UTF-8') . '">Check</a><br><br>';
}
?>
Note that the fetchObject method can directly instantiate the class you specified and automatically assign values to the attributes, which is very suitable for methods that require encapsulation behavior, such as the getProfileUrl above.
Using PDOStatement::fetchObject can map database records into objects very elegantly, especially suitable for object-oriented application development. When combining with paging query, you only need to add LIMIT and OFFSET to the SQL statement, and then use fetchObject to obtain it one by one.
If you are building an MVC framework or backend management system, this method will make your code more concise and maintainable.
