Fetching objects with PDO

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

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 an object

Of course, for a newly created object we may want to supply constructor parameters. 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"

} */