PDO FETCH_CLASS

Summary: in this tutorial, you’ll learn how to use the PDO::FETCH_CLASS mode to fetch data into an object of a class.

Introduction to the PDO::FETCH_CLASS mode

Suppose that you have the following books table in the database:

CREATE TABLE IF NOT EXISTS books (
    book_id INT AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    isbn VARCHAR(13) NULL,
    published_date DATE NULL,
    publisher_id INT NULL,
    PRIMARY KEY (book_id),
    CONSTRAINT fk_publisher FOREIGN KEY (publisher_id)
        REFERENCES publishers (publisher_id)
);Code language: SQL (Structured Query Language) (sql)

And the Book class in PHP:

class Book
{
}Code language: PHP (php)

Each row in the books table can map to an instance of the Book class.

To select data from the books table and map the columns to the properties of a Book object, you can use the PDO::FETCH_CLASS mode. For example:

<?php

class Book
{
}

$pdo = require 'connect.php';

$sql = 'SELECT book_id, title, isbn, published_date
        FROM books
        WHERE book_id = :book_id';

$statement = $pdo->prepare($sql);
$statement->execute([':book_id' => 1]);
$statement->setFetchMode(PDO::FETCH_CLASS, 'Book');
$book = $statement->fetch();

var_dump($book);Code language: PHP (php)

Output:

object(Book)#3 (4) {
    ["book_id"]=>  string(1) "1"
    ["title"]=>  string(43) "CISSP All-in-One Exam Guide, Eighth Edition"
    ["isbn"]=>  string(13) "9781260142655"
    ["published_date"]=>  string(10) "2018-09-28"
}Code language: plaintext (plaintext)

How it works.

First, define a class called Book:

class Book
{
}Code language: PHP (php)

Next, connect to the bookdb database using the connect.php script:

$pdo = require 'connect.php';Code language: PHP (php)

Then, select a row with id 1 from the books table:

$sql = 'SELECT book_id, title, isbn, published_date
        FROM books
        WHERE book_id = :book_id';

$statement = $pdo->prepare($sql);
$statement->execute([':book_id' => 1]);Code language: PHP (php)

After that, set the fetch mode to PDO::FETCH_CLASS and pass the Book class name to the second argument of the setFetchMode() method:

$statement->setFetchMode(PDO::FETCH_CLASS, 'Book');Code language: PHP (php)

Finally, fetch the data from the books table to the properties of the Book class:

$book = $statement->fetch();Code language: PHP (php)

Note that if the Book class doesn’t exist, PDO will return an array instead of an object.

The PDO::FETCH_CLASS uses the following rules when assigning column values to the object properties:

• If the Book class has a property which is the same as a column name, it’ll assign the column value to the property.
• If the Book class has no property that is the same as the column name, PHP will call the magic method __set().
• If the Book class has no __set() method, PHP will create a public property with the value derived from the column.

Returning an array of objects

The following example shows how to use the PDO::FETCH_CLASS mode to select data from the books table and return an array of Book objects:

<?php

class Book
{
}

$pdo = require 'connect.php';

$sql = 'SELECT book_id, title, isbn, published_date
        FROM books';

$books = $statement->query()->fetchAll(PDO::FETCH_CLASS, 'Book');

var_dump($books);Code language: PHP (php)

Assigning object properties

The following example illustrates how to select one row from the books table and return a new instance of the Book class:

<?php

class Book
{
    private $book_id;

    private $title;

    private $isbn;

    private $published_date;

    public function __set($name, $value)
    {
        // empty
    }
}

$pdo = require 'connect.php';

$sql = 'SELECT 
            book_id, 
            title, 
            isbn, 
            published_date, 
            publisher_id
        FROM books
        WHERE book_id = :book_id';

$statement = $pdo->prepare($sql);
$statement->execute([':book_id' => 1]);
$statement->setFetchMode(PDO::FETCH_CLASS, 'Book');

$book = $statement->fetch();

var_dump($book);Code language: PHP (php)

Output:

object(Book)#3 (4) {
    ["book_id":"Book":private]=>  string(1) "1"
    ["title":"Book":private]=>  string(43) "CISSP All-in-One Exam Guide, Eighth Edition"
    ["isbn":"Book":private]=>  string(13) "9781260142655"
    ["published_date":"Book":private]=>  string(10) "2018-09-28"
}Code language: plaintext (plaintext)

In this example, the Book class has four private properties and an empty __set() magic method.

Since the book_id, title, isbn, and published_date properties match with the columns from the selected row, PHP assigns the column values to these properties.

However, the Book class doesn’t have the publisher_id property. In this case, PDO calls the __set() method of the Book class.

Because the __set() method doesn’t have any logic, PDO doesn’t assign the value from the publisher_id column to the Book object.

Assigning object properties and calling constructor

By default, PDO assigns column values to the object properties before calling the constructor.

To instruct PDO to call the constructor before assigning column values to object properties, you combine the PDO::FETCH_CLASS with PDO::FETCH_PROPS_LATE flag. For example:

<?php

class Book
{
    public function __construct()
    {
        if (isset($this->isbn)) {
            echo 'ISBN:' . $this->isbn;
        }
        echo 'ISBN has not assigned yet.';
    }
}

$pdo = require 'connect.php';

$sql = 'SELECT book_id, title, isbn, published_date, 
        FROM books
        WHERE book_id = :book_id';

$statement = $pdo->prepare($sql);
$statement->execute([':book_id' => 1]);
$statement->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, 'Book');
$book = $statement->fetch();

var_dump($book);Code language: PHP (php)

Summary

• Use the PDO::FETCH_CLASS fetch mode to return an instance of a class by mapping the column values to the object properties.
• Use the PDO::FETCH_PROPS_LATE to call the constructor before assigning column values to object properites.