English
English
When using PDO for database operations, PDOStatement::fetchObject is a very convenient method, which can directly map a row of data from the query result into an object. However, by default, if the database returns fields with underscores or named in different styles, using fetchObject directly may not correctly map these fields to the object's properties. This article will explain in detail how to handle this situation gracefully.
Suppose your database table users have the following fields:
id
user_name
email_address
Generally we can query this:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'root', 'password');
$stmt = $pdo->query('SELECT id, user_name, email_address FROM users WHERE id = 1');
$user = $stmt->fetchObject();
echo $user->id; // Normal output
echo $user->user_name; // Normal output
echo $user->email_address; // Normal output
?>
Everything looks very smooth here. But if your entity class (such as User ) uses camel naming, such as userName and emailAddress , then using fetchObject('User') directly will not assign the value correctly.
Because fetchObject is assigned directly to the object attributes according to the column names returned by the database by default. The fields in the database are underlined ( user_name ), while the objects are camel ( userName ), which naturally does not correspond.
For example, the following User class:
<?php
class User {
public int $id;
public string $userName;
public string $emailAddress;
}
?>
Use $stmt->fetchObject('User') directly, and userName and emailAddress cannot get the value.
The simplest and most crude way is to give the field an alias that matches the object attribute name when querying SQL:
<?php
$stmt = $pdo->query('
SELECT
id,
user_name AS userName,
email_address AS emailAddress
FROM users
WHERE id = 1
');
$user = $stmt->fetchObject('User');
echo $user->userName; // Correct output
echo $user->emailAddress; // Correct output
?>
This method is simple and direct, without modifying the entity class. However, if there are many query fields and complex tables, it is easy to maintain difficult.
If you want to keep the field names in SQL unmoved, you can manually take out the array and assign it to the object:
<?php
$stmt = $pdo->query('SELECT id, user_name, email_address FROM users WHERE id = 1');
$data = $stmt->fetch(PDO::FETCH_ASSOC);
$user = new User();
$user->id = $data['id'];
$user->userName = $data['user_name'];
$user->emailAddress = $data['email_address'];
echo $user->userName; // Correct output
?>
This is flexible, but it is troublesome to write. You have to manually assign values every time, which is not elegant enough.
If you want to be more automatic, you can write a small function that automatically converts the database field into camel name, and then assigns the value to the object.
<?php
function snakeToCamel(string $string): string {
return lcfirst(str_replace(' ', '', ucwords(str_replace('_', ' ', $string))));
}
function mapToEntity(array $data, string $className) {
$object = new $className();
foreach ($data as $key => $value) {
$property = snakeToCamel($key);
if (property_exists($object, $property)) {
$object->$property = $value;
}
}
return $object;
}
// Example of usage
$stmt = $pdo->query('SELECT id, user_name, email_address FROM users WHERE id = 1');
$data = $stmt->fetch(PDO::FETCH_ASSOC);
$user = mapToEntity($data, 'User');
echo $user->userName; // Correct output
?>
In this way, you only need to write the conversion logic once, and all subsequent queries can be processed automatically.
fetchObject is suitable for simple and fast data acquisition. If complex logic is required, it is recommended to use an ORM framework (such as Laravel's Eloquent, Symfony's Doctrine, etc.).
Make sure that the attributes of the entity class are public , otherwise fetchObject cannot be assigned directly.
If you need to protect attributes, you should cooperate with the construction method or magic method __set() to handle the assignment.
PDOStatement::fetchObject is a convenient but careful feature. If the field naming style is not synchronized, it is recommended to use alias in SQL, or write your own mapping method, so that the code can be kept clean and robust. For medium and large projects, it is also recommended to directly use mature ORM libraries to manage entity mapping to reduce the risk of errors.
If you would like to see more advanced tips on PDO, database optimization, or entity mapping, you can visit our technology blog https://gitbox.net/blog for more articles!
