English
English
When using PDO for database operations, PDOStatement::fetchObject is a very convenient method that can directly map the query results into an object. But sometimes we encounter type mismatch problems, such as the field that was originally an integer ( int ) in the database is converted into a string ( string ). This situation is especially troublesome when dealing with large amounts of data or requiring strict type of data structures.
Let’s take a look at the reasons and solutions.
PDOStatement::fetchObject itself does not automatically infer the type of PHP based on the type of the database field. It simply fills the data into the object properties, so many times, numbers are processed as strings. This is because PDO returns all data as a string by default.
Let’s take a look at an example:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$stmt = $pdo->query('SELECT id, name FROM users');
$user = $stmt->fetchObject();
var_dump($user);
?>
The output may be:
object(stdClass)#1 (2) {
["id"]=>
string(1) "1"
["name"]=>
string(4) "John"
}
As you can see, even if the id is of INT type in the database, the result is a string in PHP.
There are several common methods to solve this problem:
PDO has a setting called PDO::ATTR_STRINGIFY_FETCHES , which can control whether to automatically convert numbers into strings. However, it should be noted that this option can only be effective in the fetch() series functions and has no direct effect on fetchObject , so it is not recommended.
The most practical method is to define a class and manually perform type conversion in the constructor or magic method __set .
for example:
<?php
class User {
public int $id;
public string $name;
public function __construct() {
// Type coercion can be performed here
}
public function __set($name, $value) {
if ($name === 'id') {
$this->id = (int) $value;
} elseif ($name === 'name') {
$this->name = (string) $value;
}
}
}
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$stmt = $pdo->query('SELECT id, name FROM users');
$user = $stmt->fetchObject(User::class);
var_dump($user);
?>
This way, when you fetch data from the database, you can make sure the type is correct.
In SQL statements, directly force the fields to the required type, such as:
SELECT id + 0 AS id, name FROM users
In this way, the id is already an integer when the database returns, but this method is not particularly elegant and has poor maintenance, so it is only suitable for temporary emergency rescue.
If there are many places in your project that require object mapping, consider writing a general Mapper tool. For example:
<?php
function mapUser($data) {
$user = new User();
$user->id = (int) $data->id;
$user->name = (string) $data->name;
return $user;
}
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$stmt = $pdo->query('SELECT id, name FROM users');
$rawUser = $stmt->fetchObject();
$user = mapUser($rawUser);
var_dump($user);
?>
This method is more controllable and is easy to manage and expand, such as adding verification logic in the later stage.
The data table field type is reasonably designed : try to make the fields in the database table clear as much as possible. For example, the id must be INT and the amount must be DECIMAL . Do not use strings instead of numeric fields.
Using strong type declarations : In PHP 7.4+, using type hints and attribute type declarations can detect errors earlier.
Unified Data Access Layer (DAL) Encapsulation : If the project is large, it is best to encapsulate database access and object mapping into unified modules to avoid repeated labor.
It is very convenient to use fetchObject , but in order to ensure the correct data type, it is usually recommended to define the class yourself, cooperate with manual conversion, or use mapping functions. This can make the code more robust and reduce the trouble of subsequent debugging.
If you want to know more detailed PDO usage and object mapping, you can refer to: https://gitbox.net/docs/pdo-tutorial .
