Using PDO To Create A Data Transfer Object

4th August 2020 - 14 minutes read time

A Data Transfer Object (DTO) is a way of taking data out of a database or API and present it in a unified way across your application. As a design pattern this has uses in standardising how a particular bit of data is passed around, without having to resort to using arrays to accomplish the same job.

I was doing some work with PHP's PDO library the other day when I noticed that I could fetch data out of a database using the fetchAll() method with the PDO::FETCH_CLASS flag. This flag automatically returns the data in an object. This got me thinking more about how this worked, but I couldn't find many articles talking about this. Hence this post.

Setting Things Up

Let's take a class called SpecialText that will store an ID and some text, along with a __toString() method to allow the printing of those values. We will use this class throughout this post to store data from the database.

class SpecialText {
  protected $id;
  protected $specialtext;

  public function __toString() {
    $output = self::CLASS . PHP_EOL;
    $output .= 'ID: ' . $this->id . PHP_EOL;
    $output .= 'Special Text: ' . $this->specialtext . PHP_EOL;
    return $output;
  }
}

We will also need a database. The very simplest database to create is with sqlite. The following will create the sqlite database file in the same directory as the PHP script and create a single table called specialtexttable.

$databaseFile = realpath(__DIR__) . '/testdatabase.sqlite';
$databaseHandle = new \PDO('sqlite:' . $databaseFile, '', '', array(\PDO::ATTR_PERSISTENT => false));
$sql = 'DROP TABLE IF EXISTS "specialtexttable";
  CREATE TABLE "specialtext" (
      "id"	INTEGER,
      "specialtext"	TEXT NOT NULL,
      PRIMARY KEY("id" AUTOINCREMENT)
  );';
$databaseHandle->exec($sql);

Next, we need to insert some data into the table. A simple insert takes care of that.

$sql = 'INSERT INTO specialtexttable(specialtext) VALUES (:specialtext);';
$query = $databaseHandle->prepare($sql);
$query->execute([':specialtext' => 'text text text']);

Now we are ready to extract data from the database.

Fetch By Indexed Column

By default, the fetchAll() method will return an array containing a combination of indexed and associative items. This means that our table of two values will be wrapped in an array with four values. It is more usual to pass in \PDO::FETCH_ASSOC so that PDO will create an associative array of values from the results. The following example returns an associative array from the database using the fetchAll() method.

$sql = "SELECT * FROM specialtexttable;";
$query = $databaseHandle->prepare($sql);
$query->execute();
$result = $query->fetchAll(\PDO::FETCH_ASSOC);

print_r($result);

Note that the fetch() method is essentially equivalent to the fetchAll() method. The difference being that the fetch() method will return a single result, or false if no more results are present and fetchAll() will always return an array of items.

The above code prints out the following.

Array
(
    [0] => Array
        (
            [id] => 1
            [specialtext] => text text text
        )

)

Fetch By stdClass

If you pass the flag \PDO::FETCH_OBJ or \PDO::FETCH_CLASS without any other parameters to the fetchAll() method then you will get back an instance of the stdClass object that maps the column names to the properties in the class.

$sql = "SELECT * FROM specialtexttable;";
$query = $databaseHandle->prepare($sql);
$query->execute();
$objects = $query->fetchAll(\PDO::FETCH_CLASS);

print_r($objects);

This will print out the following.

Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [specialtext] => text text text
        )

)

Whilst this is possible, what we are actually getting is little more than an array. It's best not to use the stdClass object unless you really have to as you can't benefit from any of the normal controls or functionality that you would get with a full PHP class. Let's make this more useful by using a class.

Fetch By Class

The \PDO::FETCH_CLASS flag will allow the fetchAll() method to inject values into a class that we specify, calling the constructor afterwards. The second parameter of fetchAll() is the name of the class that you want to use. You may have noticed that the SpecialText class contains protected properties, but this does not matter as PDO will still return an object that maps the column names to your property names. You can even set the class properties as private and PDO would still return a valid object. PDO will essentially bypass the constructor and directly inject properties into your object.

Here is how to use the SpecialText class with fetchAll(). The second parameter must be a string so you could also pass in 'SpecialText', but I have found that using the special ::class constant allows for better code readability when namespacing is involved.

$sql = "SELECT * FROM specialtexttable;";
$query = $databaseHandle->prepare($sql);
$query->execute();
$objects = $query->fetchAll(\PDO::FETCH_CLASS, SpecialText::class);

foreach ($objects as $object) {
  echo $object;
}

This will print out the following.

SpecialText
ID: 1
Special Text: text text text

That SpecialText object can now be used as any other object.

It's important to remember that the property names of your class must exactly match the column names of your table in order for this to work. If your class property differs from the table name (even by the capitalisation) then PDO will create an object with an additional property that contains that value. For example, if I changed the specialtext property to be specialText then the resulting object would contain a null property called specialText and a property called specialtext that contained the value from the database.

It is possible to override this functionality by adding a __set() method to your class. This magic method is always called if a non-existent property of a class is set. You can then intercept the call and act accordingly.

Constructor Order

If the \PDO::FETCH_CLASS flag is combined with \PDO::FETCH_PROPS_LATE then the constructor is called before the properties are added to the class. This makes the object instantiation behave more like it normally does. In isolation isn't not really clear what changes here, so let's modify the SpecialText class to include a flag for when \PDO::FETCH_PROPS_LATE is used. This is a property that is populated with a string if the specialtext property is null when the constructor is run.

class SpecialText {
  protected $id;
  protected $specialtext;
  protected $propsLate;

  public function __construct() {
    if (is_null($this->specialtext)) {
      $this->propsLate = 'late props used';
    }
  }

  public function __toString() {
    $output = self::CLASS . PHP_EOL;
    $output .= 'ID: ' . $this->id . PHP_EOL;
    $output .= 'Special Text: ' . $this->specialtext . PHP_EOL;
    $output .= 'FETCH_PROPS_LATE: ' . $this->propsLate . PHP_EOL;
    return $output;
  }
}

We then run the two different variants fetchAll() side by side.

$sql = "SELECT * FROM specialtexttable;";
$query = $databaseHandle->prepare($sql);
$query->execute();
$objects = $query->fetchAll(\PDO::FETCH_CLASS, SpecialText::class);

foreach ($objects as $object) {
  echo $object;
}

$query = $databaseHandle->prepare($sql);
$query->execute();
$objects = $query->fetchAll(\PDO::FETCH_CLASS|\PDO::FETCH_PROPS_LATE, SpecialText::class);

foreach ($objects as $object) {
  echo $object;
}

Comparing the output of the above it's clear to see that without \PDO::FETCH_PROPS_LATE the propsLate property is not filled in, but with that flag present the property contains the string.

SpecialText
ID: 1
Special Text: text text text
FETCH_PROPS_LATE:

SpecialText
ID: 1
Special Text: text text text
FETCH_PROPS_LATE: late props used

Specifying Constructor Arguments

Finally, it is also possible to provide the arguments to the constructor. Let's modify the SpecialText class to include a constructor.

class SpecialText {
  private $id;
  protected $specialtext;

  public function __construct($id, $specialtext) {
    $this->id = $id;
    $this->specialtext = $specialtext;
  }

  public function __toString() {
    $output = self::CLASS . PHP_EOL;
    $output .= 'ID: ' . $this->id . PHP_EOL;
    $output .= 'Special Text: ' . $this->specialtext . PHP_EOL;
    return $output;
  }
}

Applying the same fetchAll() code as we have used above will present us with the following error.

PHP Fatal error:  Uncaught ArgumentCountError: Too few arguments to function SpecialText::__construct(), 0 passed and exactly 2 expected in pdo.php:27
Stack trace:
#0 [internal function]: SpecialText->__construct()
#1 pdo.php(106): PDOStatement->fetchAll(1048584, 'SpecialText')
#2 {main}
  thrown in pdo.php on line 27

This is because the constructor is still being run and because it is not being passed any parameters the result is a fatal error.

To get around this we can add a third parameter which is the default values that are to be sent to the constructor. The example below sets both of the constructor values to NULL, but as the \PDO::FETCH_PROPS_LATE value is in use these soon get overwritten with the actual values.

$sql = "SELECT * FROM specialtexttable;";
$query = $databaseHandle->prepare($sql);
$query->execute();
$objects = $query->fetchAll(\PDO::FETCH_CLASS|\PDO::FETCH_PROPS_LATE, SpecialText::class, [NULL, NULL]);

foreach ($objects as $object) {
  echo $object;
}

This prints the same as the output of the two \PDO::FETCH_CLASS calls above.

I have seen people state that the parameters of the array are the field names in the database, this is incorrect as the values of the array are passed directly to the constructor. This is absolutely not an array of field names to map to the constructor.

Set Fetch Mode

Sending the full list of parameters to fetchAll() is always necessary. An alternative approach is to set the fetch mode before running the fetchAll() method.

Here is an equivalent to the previous example, but in this instance we are setting up the fetchAll() operation using the

$sql = "SELECT * FROM specialtexttable;";
$query = $databaseHandle->prepare($sql);
$query->setFetchMode(\PDO::FETCH_CLASS|\PDO::FETCH_PROPS_LATE, SpecialText::class, [null, null]);
$query->execute();
$objects = $query->fetchAll();

Conclusion

Whilst it is possible to use \PDO::FETCH_CLASS in this way, I probably won't be using it on any project work. The work I was talking about at the beginning of the post was just a little hobby project that I was playing around with and needed a quick way of storing data in a database. I've had a look online and can't see this technique being used much in open source projects.

A bit of a problem here is that the property names in the class must be exactly the same as the field names in the database table. This means that your DTO object and its properties are inherently tied into your database. This means you have to be especially careful when naming your fields and properties as you'll have a lot of change to go through if you need to rename things afterwards. You can potentially minimise the impact of tying object properties and field names together by using the magic __set() method.

Running fetch operations this way does cut down a little on writing code, but what you are doing instead is hiding magic property filling and difficult to debug code. Have you ever used this technique? Let me know in the comments.

PHP
PDO
object
fetch
oop
PHP OOP

Add new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
5 + 2 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.

Related Content

Recreating Spotify Wrapped In PHP

17th March 2024

I quite like the end of the year report from Spotify that they call "Wrapped". This is a little application in which they tell you what your favorite artist was and what sort of genres you listened to the most during the year.

Read more

Should A Constructor Throw An Exception?

18th February 2024

Let's say you had a class that you wanted to use, but there was some sort of error in creating the object. This might be that the wrong parameters were passed, or the third party service (eg. a database) wasn't available at the time of creation.

Read more

PHP Question: Variable Reference

10th December 2023

Question

What does the following code print out?

function arrayPrint($array)
{
   echo implode(' ', $array);
}

$arrayA = [1, 2, 3];
$arrayB = $arrayA;
$arrayB[1] = 0;
arrayPrint($arrayA);

Read more

Creating An Authentication System With PHP and MariaDB

15th October 2023

Using frameworks to handle the authentication of your PHP application is perfectly fine to do, and normally encouraged. They abstract away all of the complexity of managing users and sessions that need to work in order to allow your application to function.

Read more

Creating Sparklines In PHP

17th September 2023

A sparkline is a very small line graph that is intended to convey some simple information, usually in terms of a numeric value over time. They tend to lack axes or other labels and are added to information readouts in order to expand on numbers in order to give them more context.

Read more