English
English
In daily development, multi-table joint query (such as JOIN ) is a very common scenario. However, if you use PDOStatement::fetchObject to get the results directly, it is easy to encounter problems (for example, different tables have the same name field).
To avoid these conflicts and correctly map to object properties, we can combine field alias and reasonable object design to achieve elegant data extraction.
The following explains practical techniques in detail and attaches sample code:
Suppose we have two tables:
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50)
);
CREATE TABLE profiles (
id INT PRIMARY KEY,
user_id INT,
bio TEXT
);
In order to map multi-table query results, we can create a special class, such as:
<?php
class UserProfile
{
public $user_id;
public $username;
public $profile_id;
public $bio;
}
Note the field names here, and the distinction has been made, such as user_id and profile_id .
The key is to set appropriate alias for the query fields to ensure that fetchObject can be assigned successfully directly.
<?php
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'root', 'password');
// Multi-table joint query，and alias the field
$sql = "
SELECT
u.id AS user_id,
u.username,
p.id AS profile_id,
p.bio
FROM
users u
INNER JOIN
profiles p ON u.id = p.user_id
";
$stmt = $pdo->query($sql);
// Use directly fetchObject Map into UserProfile Object
while ($userProfile = $stmt->fetchObject('UserProfile')) {
echo "userID: {$userProfile->user_id}\n";
echo "user名: {$userProfile->username}\n";
echo "Introduction: {$userProfile->bio}\n";
echo "---\n";
}
The advantage of this is that it avoids field conflicts, and at the same time the object structure is clear, which is easy to use later, such as passing it to the front-end API interface or data processing.
If you want to be more flexible, for example, encountering dynamic structures, you can use stdClass or trait prepared in advance to cooperate with mapping logic.
Example:
<?php
class DynamicUserProfile
{
public function __construct(array $data)
{
foreach ($data as $key => $value) {
$this->$key = $value;
}
}
}
$stmt = $pdo->query($sql);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$dynamicProfile = new DynamicUserProfile($row);
echo "user名: {$dynamicProfile->username}, Introduction: {$dynamicProfile->bio}\n";
}
Although this method is more flexible, it has a slightly lower performance than fetchObject 's native support, so it should be chosen according to actual needs.
Field aliasing must be standardized , especially multi-table query.
The object attribute name must strictly correspond to the field alias in SQL.
If it is a large project, it is recommended to write a Mapper tool class to automatically handle mapping details.
When connecting to external interfaces or platforms, such as sending object data to interfaces such as https://gitbox.net/api/user/profile , using a clear object structure will greatly reduce the error rate.
