How to conduct advanced query with external libraries through PDOStatement::fetchObject

In daily development, we often use PDO to operate databases, and PDOStatement::fetchObject provides a very elegant way to map query results directly to an object. However, if we want to further improve the flexibility of query, such as supporting complex data processing or mapping to custom data structures, we can combine external libraries to achieve more advanced query methods.

This article will introduce how to create a more flexible and powerful query system through PDOStatement::fetchObject with external libraries.

Basic Review: What is fetchObject?

The fetchObject method allows you to encapsulate each row of data into an object instance when extracting data from the database. The basic usage is as follows:

 <?php
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$stmt = $pdo->query('SELECT * FROM users');

while ($user = $stmt->fetchObject()) {
    echo $user->name . "\n";
}
?>

Although it is simple and easy to use, if you encounter more complex data mapping needs, such as property renaming, automatic type conversion or relationship mapping, fetchObject alone will be a bit thin.

External library selection: Use a custom entity mapper

For flexibility, libraries like Atlas\Mapper or lightweight php-data-mapper can be used. But here, we mainly use simple examples and quickly write a basic entity mapper ourselves.

Example: Create a simple mapper

Suppose we want to query the users table and map each record into a User class instance, and can automatically handle small differences between fields and attributes.

First, define a simple entity class:

 <?php
// src/Entity/User.php
class User
{
    public int $id;
    public string $username;
    public string $email;
}
?>

Then, write a basic mapper:

 <?php
// src/Mapper/UserMapper.php
class UserMapper
{
    public static function map(PDOStatement $stmt): array
    {
        $results = [];
        
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $user = new User();
            $user->id = (int)$row['id'];
            $user->username = $row['name']; // The database field is name，Map to username
            $user->email = $row['email'];
            $results[] = $user;
        }

        return $results;
    }
}
?>

Call it in the application:

 <?php
require 'src/Entity/User.php';
require 'src/Mapper/UserMapper.php';

$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$stmt = $pdo->query('SELECT id, name, email FROM users');

$users = UserMapper::map($stmt);

foreach ($users as $user) {
    echo $user->username . " <" . $user->email . ">\n";
}
?>

In this way, even if the database fields are different from the class attribute names, they can flexibly correspond to them, and more advanced logic can be easily added (such as type conversion, nested relationship processing, etc.).

Tips: With automatic loader

If your project is relatively large, it is recommended to introduce an automatic loader of PSR-4 standard, such as using Composer to manage these classes, so that you don’t need to require files every time and improve development efficiency.

Add in composer.json :

 {
    "autoload": {
        "psr-4": {
            "App\\": "src/"
        }
    }
}

Then run:

 composer dump-autoload

Then, you can use it in the PHP file like this:

 <?php
require 'vendor/autoload.php';

use App\Entity\User;
use App\Mapper\UserMapper;

// ...The same code as above
?>

Advanced applications: Combined with remote interfaces (such as gitbox.net)

If part of the data that needs to be queried comes from the database and part of it needs to be supplemented remotely (such as calling gitbox.net 's API), it can also be used in combination:

 <?php
function fetchAdditionalData(int $userId): array
{
    $json = file_get_contents("https://gitbox.net/api/userinfo/$userId");
    return json_decode($json, true);
}

foreach ($users as $user) {
    $extraData = fetchAdditionalData($user->id);
    $user->profile_picture = $extraData['profile_picture'] ?? null;
}
?>

In this way, the data integration of the database + external interface becomes very natural and powerful!