Fetching objects with PDO

  1. Fetching a single object
  2. Fetching an array of objects
  3. Assigning class properties
  4. Passing constructor parameters to the object
  5. Setting class properties after calling a constructor
  6. Getting a class name from the database
  7. Updating an existing object
  8. 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:

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.


Related articles: