PDO
Fetching objects with PDO

Fetching objects with PDO

Fetching a single object
Fetching an array of objects
Assigning class properties
Passing constructor parameters to the object
Setting class properties after calling a constructor
Getting a class name from the database
Updating an existing object
Comments (11)

Like any other database extension, PDO can create instances of the existing classes right from the selected data. But, unlike other extensions, PDO offers many features for the powerful and flexible object manipulation.

Fetching a single object

To create a single object from the query results you have two options. You can use the either a familiar fetch() method:

class User {};
$stmt = $pdo->query('SELECT name FROM users LIMIT 1');
$stmt->setFetchMode(PDO::FETCH_CLASS, 'User');
$user = $stmt->fetch();

or a dedicated fetchObject() method:

class User {};
$user = $pdo->query('SELECT name FROM users LIMIT 1')->fetchObject('User');

Although both code snippets will give you the same instance of a User class,

/* object(User)#3 (1) {
  ["name"] => string(4) "John"
} */

the latter approach looks definitely cleaner. Besides, if fetch() method is used, but no class defined with such a name, an array will be silently returned, while with fetchObject() an appropriate error will be thrown.

Fetching an array of objects

Of course, both methods described above could be used with a familiar while statement to get consequent rows from database. Nonetheless, a handy fetchAll() method can be used to get all the returned records in the array of objects at once:

class User {};
$users = $pdo->query('SELECT name FROM users')->fetchAll(PDO::FETCH_CLASS, 'User');

will give you an array consists of objects of a User class, with properties filled from returned data:

/* array(2) {
  [0]=> object(User)#3 (1) {
    ["name"] => string(4) "John"
  }
  [1]=> object(User)#4 (1) {
    ["name"]=> string(4) "Mike"
  }
} */

Note that you can combine this mode with PDO::FETCH_UNIQUE and PDO::FETCH_GROUP, to get the resulting array indexed by an unique field or to make results grouped by a non-unique field respectively. For example, the following code will return an array, where a record id will be used as array index instead of consecutive numbers.

class User {};
$stmt  = $pdo->query('SELECT id, id, name, car FROM users');
$users = ->fetchAll(PDO::FETCH_CLASS|PDO::FETCH_UNIQUE, 'User');

Assigning class properties

No matter which method you choose, all the columns returned by the query will be assigned to the corresponding class' properties according to the following rules:

if there is a class property, which name is the same as a column name, the column value will be assigned to this property
if there is no such property, then a magic __set() method will be called
if __set() method is not defined for the class, then a public property will be created and a column value assigned to it.

For example, this code

class User
{
    public $name;
}
$user = $pdo->query('SELECT * FROM users LIMIT 1')->fetchObject('User');

will give you an object with all the properties automatically assigned, no matter, whether they exist in the class or not:

/* object(User)#3 (4) {
  ["id"]   => string(3) "104"
  ["name"] => string(4) "John"
  ["sex"]  => string(4) "male"
  ["car"]  => string(6) "Toyota"
} */

From this you can tell that to avoid an automated property creation you could to use the magic __set() method to filter the properties out. The simplest filtering technique would be just an empty __set() method. With it, only existing properties will be set:

class User
{
    private $name;
    public function __set($name, $value) {}
}
$user = $pdo->query('SELECT * FROM users LIMIT 1')->fetchObject('User');
/*
array(1) {
  [0]=> object(User)#3 (1) {
    ["name":"User":private]=> string(4) "John"
  }
} */

As you can see, PDO can assign values to private properties as well. Which is a bit unexpected but extremely useful.

Passing constructor parameters to the object

Unfortunately, there is NO fetch mode for the use case when your class is accepting values in the constructor. It's impossible to have such a class to be instantiated using PDO. You'll have to resort to an explicit loop like this:

class User {
    protected $name;
    protected $car;
    public function __construct($name, $car) {
        $this->name = $name;
        $this->car = $car;
    }
}

$users = $pdo->query('SELECT name, car FROM users')->fetchAll(PDO::FETCH_ROW);
foreach ($users as $i => $row) {
    $users[$i] = new User(...$row);
}

The only constructor parameters that can be used with PDO are outside parameters, not taken from the SQL result. For this purpose, both fetchObject() and fetchAll() methods has a dedicated parameter, which you can use to pass the constructor arguments in the form of array.

Let's say we have a class User, with a car property that can be set in a constructor from a supplied variable:

class User {
    public function __construct($car) {
        $this->car = $car;
    }
}

when fetching a record, we should add an array with constructor parameters:

$users = $pdo->query('SELECT name FROM users LIMIT 1')
             ->fetchAll(PDO::FETCH_CLASS, 'User', ['Caterpillar']);

$user = $pdo->query('SELECT name FROM users LIMIT 1')
            ->fetchObject('User',['Caterpillar']);

which will will give us

/* object(User)#3 (2) {
    ["name"] => string(4) "John"
    ["car"]  => string(11) "Caterpillar"
} */

As you can see, a value from database has been overwritten, because by default PDO assigns class properties before calling a constructor. Which could be a problem, that, however, can be easily solved:

Setting class properties after calling a constructor

The most popular comment for the mysql_fetch_object() says:

If you're using mysql_fetch_object and specifying a class - the properties will be set BEFORE the constructor is executed. This is generally not an issue, but can cause some major problems if you're properties are set via the __set() magic method and constructor logic must be executed first.

Pity for mysql an mysqli extensions, but we are using a shiny PDO. So there is way to tell PDO to assign properties after the constructor execution. For this purpose a PDO::FETCH_PROPS_LATE constant have to be used.

With fetchAll() it will be plain and simple,

class User {
    public function __construct($car) {
        $this->car = $car;
    }
}
$stmt  = $pdo->query('SELECT name, car FROM users LIMIT 1');
$users = $stmt->fetchAll(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, 'User', ['Caterpillar']);

while to fetch a single row, we will need to call both setFetchMode() and fetchObject() which is not very convenient.

class User {
    public function __construct($car) {
        $this->car = $car;
    }
}
$stmt = $pdo->query('SELECT name, car FROM users LIMIT 1');
$stmt->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, 'User');
$user = $stmt->fetchObject('User', ['Caterpillar']);
/*
object(User)#3 (2) {
  ["car"]  => string(6) "Toyota"
  ["name"] => string(4) "John"
} */

as you can see, this code is not a summit of efficiency, as we have to write the class name twice. Alternatively, we can use fetch():

$stmt = $pdo->query('SELECT name, car FROM users LIMIT 1');
$stmt->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, 'User', ['Caterpillar']);
$user = $stmt->fetch();

but, as it was noted above, it won't help us with an error message if a class happen to be undefined.

Getting a class name from the database

There is one more interesting flag which tells PDO to get the class name from the first column's value. With this flag one can avoid using setFetchMode() with fetch():

$data = $pdo->query("SELECT 'User', name FROM users")
            ->fetch(PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE);
/*
object(User)#3 (1) {
  ["name"]=> string(4) "John"
} */

Besides, as it was noted in the comments to the main article, this mode can be useful if objects of different classes can be created from the same query

class Male {};
class Female {};
$stmt = $pdo->query('SELECT sex, name FROM users');
$users = $stmt->fetchAll(PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE);
/*
array(6) {
  [0]=> object(Male)#3 (1) {
    ["name"]=> string(4) "John"
  }
  [1]=> object(Male)#4 (1) {
    ["name"]=> string(4) "Mike"
  }
  [2]=> object(Female)#5 (1) {
    ["name"]=> string(4) "Mary"
  }
  [3]=> object(Female)#6 (1) {
    ["name"]=> string(5) "Kathy"
  }
}*/

However, it seems impossible to pass any arguments in the class constructor when using this mode.

Updating an existing object

Beside creating a new object, PDO can update an existing one. Works with setFetchMode() only, which takes the existing variable as a parameter. Obviously, useless with fetchAll().

class User
{
    public $name;
    public $state;

    public function __construct()
    {
        $this->name = NULL;
    }
}
$user = new User;
$user->state = "up'n'running";
var_dump($user);

$stmt = $pdo->query('SELECT name FROM users LIMIT 1');
$stmt->setFetchMode(PDO::FETCH_INTO, $user);
$data = $stmt->fetch();
var_dump($data, $user);
/*
object(Foo)#2 (2) {
  ["name"]  => NULL
  ["state"] => string(12) "up'n'running"
}
object(Foo)#2 (2) {
  ["name"]  => string(4) "John"
  ["state"] => string(12) "up'n'running"
}
object(Foo)#2 (2) {
  ["name"]  => string(4) "John"
  ["state"] => string(12) "up'n'running"
} */

As you can see, fetch() call is returning the same object, which seems redundant to me. Also note that unlike PDO::FETCH_CLASS, this mode doesn't assign private properties.

Latest comments:

17.12.24 07:19
Charles Roth for UPDATE query using PDO:
As described in one of your other excellent :-) phpdelusions pages, fetch() can do a (say)...
read more
16.12.24 20:08
Myles for (The only proper) PDO tutorial:
You only have silver on SQL-INJECTION. No one has gold.
read more
06.12.24 22:23
Charles Roth for (The only proper) PDO tutorial:
In your wonderful phpdelusions.net/pdo, you state that the types of results will always be...
read more
03.12.24 14:37
Adrian for Articles:
Have you written any books on PHP, or recommend any books? Thank you very much, and keep up the...
read more
26.11.24 18:45
Hans for INSERT query using PDO:
Excellent, very well explained and didactic Thank you
read more

Add a comment

Please refrain from sending spam or advertising of any sort.
Messages with hyperlinks will be pending for moderator's review.

Markdown is now supported:

> before and an empty line after for a quote
to mark a block of code add four spaces before each line and empty lines before and after

Comments:

Hendra Gunawan, 17.10.21 02:20

Hello again. PHP now has property readonly feature. Please explain what will happen if my property is readonly? From the RFC, setting readonly properties only works on the same scope (i.e. constructor or class method). Is there any exception for PDO?
Hendra Gunawan, 17.10.21 01:40

Hello again. Related to my previous question, please explain what will happen if my class property is nullable and my query return nullable column. Is null is preserved or converted?

Hendra Gunawan, 17.10.21 01:28

Hello again. Related to my previous question, Is it possible to prevent dynamic properties in order to prevent typo? Is it enought to code like this (note: $weight <=> height):

class User {
    public int $id;
    public string $name;
    public float $weight;
    public function __get($prop) {throw new Exception("Undefined prop: ".$prop);}
    public function __set($prop, val) {throw new Exception("Undefined prop: ".$prop);}
};
$users = $pdo->query('SELECT id, name, height FROM users')->fetchAll(PDO::FETCH_CLASS, 'User');

Hendra Gunawan, 17.10.21 01:02

Hello. Can you update this article to be conformed to php 7.4 (typed properties)? As far as i know, PDO always assign a string to property. What if my property type is int/float?
Dave, 13.05.20 04:07

I am trying to use fetchAll(PDO::FETCH_CLASS, 'xxxx'); but I am confused what is returned. I have had a look at the data using serialize. Is this an array with each element itsself an object? how would while work to get each object in turn
Justin, 15.02.20 11:45

I love your content. Many thanks. It seems to me the only thing I have wanted for is a concise example of using PHP to handle objects from a database from multiple tables. (E.g. Where a user object owns blogposts objects.) Perhaps this is out of your purview for the website, but I feel at this point your content is so robust it the only missing piece. It also seems the next logical step after "Fetching objects with PDO".

Cheers, Justin

passenger, 18.02.19 14:23

There are some errors.

class User {};
$stmt  = $pdo->query('SELECT id, id, name, car FROM users');
$users = ->fetchAll(PDO::FETCH_CLASS|PDO::FETCH_UNIQUE, 'User');

error: $users = ->fetchAll" part.

$stmt = $pdo->query('SELECT name, car FROM users LIMIT 1');
$stmt->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, 'User');
$user = $stmt->fetchObject('User', ['Caterpillar']);

it's not working. the Constructor is done before the assignment by PDO.

Thanks in advance, Can you check these errors?

Atul, 24.05.17 09:29

if we fetch database as object setFetchMode(PDO::FETCH_OBJ). then how can we store those values in a variable
Egdoc, 20.05.17 19:47

Really nice piece!
Chris G, 04.02.17 00:20

Sorry, I wanted to comment under other article.

Reply:
No problem! Here it suits quite well :)
Chris G, 04.02.17 00:19

Is there currently any technology that would separate storage from business logic AND allow me to easily switch from MySQL do MongoDB when I decide I need that kind of change? I would only like to update one field "driver" => "mysql" -> "driver" => "mongodb" (and db host, port etc.), and my application starts writing to and reading from my mongodb. Is it possible with Doctrine? Is it as easy as described? (I'm not talking about super complex projects databases)

Reply:
Hello Chris!

Not one I am aware of.

I mean, there is definitely no technology that will let you to write just a single set of code and use it with either Mongo or Mysql - these two DBMS are way too different. With some reservations it is possible to interchange between SQL-driven relational databases like Mysql, Sqlite or PostgreSQL, but Mongo is something entirely different, it's essentially a non-relational NoSQL database intended to store a data of arbitrary structure.

To be able to switch, you will have to write two complete sets of handling code, which is not, I believe, your intent. And even in such a case it would be quite a challenge to accomplish, as, like I mentioned above, these two are so much different: Mysql is a traditional relational database that operates, so to say, rectangular tables of predefined structure consists of columns and rows, commanded by SQL language. While MongoDB is operating a tree-like data of undetermined structure, with its own JSON-like language.

Even Doctrine, which is very good with abstracting things, have two different facilities to handle relational databases and Mongo - ORM and ODM respectively. Means inevitably you will be dragged into writing a duplicated code for any operation that is different from simple CRUD.

May I ask, what is the task behind this request? May be I will be able to suggest some plausible solution.

� phpdelusions.net, 2024