Php7.4+ MVC Pattern & PDO

CREATE TABLE `app_person` (
  `id` smallint(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
   `age` TINYINT NOT NULL,
  `date_created` datetime NOT NULL DEFAULT NOW(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_USER` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `app_book` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `creation_date` datetime NOT NULL DEFAULT NOW(),
  `person_id` SMALLINT(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT fk_book_person foreign key(person_id) REFERENCES app_person(id)
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Only 2 tables here matching a Person entity and a Book entity.

Now fill in the tables using the content :

INSERT INTO app_person (name, age) VALUES ('Eddy', 70);
INSERT INTO app_book (title, person_id) VALUES ('The lies of the magician', LAST_INSERT_ID());
INSERT INTO app_person (name, age) VALUES ('Bob', 15);
INSERT INTO app_book (title, person_id) VALUES ('Case of the chineses baboon', LAST_INSERT_ID());
INSERT INTO app_person (name, age) VALUES ('Jenny', 22);
INSERT INTO app_book (title, person_id) VALUES ('The dynasty of Dawn', LAST_INSERT_ID());
INSERT INTO app_person (name, age) VALUES ('George', 55);
INSERT INTO app_book (title, person_id) VALUES ('The descendant of hope', LAST_INSERT_ID());
INSERT INTO app_person (name, age) VALUES ('Emmy', 24);
INSERT INTO app_book (title, person_id) VALUES ('The mortal lady', LAST_INSERT_ID()), ('Ocean 12', LAST_INSERT_ID());

Codebase

As the example on the autoloader tutorial, we prepared a composer.json file to target our application sources files :

{
	"autoload" : {
		"psr-4" : {
			"App\\" : "src/"
		}
	}
}

Let’s use the same index page also :

<?php

require __DIR__ . "/vendor/autoload.php";

use App\Controller\Frontend;

$controller = new Frontend();

$controller->helloEveryone();
$controller::separator();
$controller->helloWithBooks();

Entities

2 entities defined following the SQL tables.

Note : we use typed properties available since Php 7.

<?php
declare(strict_types = 1);

namespace App\Model\Entity;

class Book
{
    private int $id;
    private string $title;
    private string $date;

    public function __construct($title, $date)
    {
        $this->title = $title;
        $this->date = $date;
    }

    public function sayHello(): void
    {
        echo $this->title . " got on ".$this->date;
    }
}

<?php
declare(strict_types = 1);

namespace App\Model\Entity;

class Person
{
    public int $id;
    private string $name;
    private int $age;
    
    public array $books = [];

    public function sayHello(): void
    {
        echo "Hello ".$this->name . ", I'm ".$this->age;
    }

    public function sayHelloToMyBooks(): void
    {
        foreach($this->books as $book)
        {
            $book->sayHello();
            echo "<br/>";
        }
    }
}

We won’t talk much about the entities here, notice the sayHello… methods which iterate on the property fields to simply echo a message.

We also use typed properties to constraint the use of our classes.

DB Manager

We will create an abstract class (DBAccess.php) to set up our DB access details which can be extend from all managers.

<?php
declare(strict_types = 1);

namespace App\Model;

abstract class DBAccess
{
    protected $db;

    public function __construct()
    {
        try {
        $this->db = new \PDO('mysql:host=db;dbname=test;charset=utf8', 'test', 'testpass');
        $this->db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        
        } catch (PDOException $e) {
            echo 'Connection error : ' . $e->getMessage();
        }
    }
}

Replace the database details to match your set up :

• host
• dbname
• user and password

Now an example of manager (Manager.php):

<?php
declare(strict_types = 1);

namespace App\Model;

use App\Model\Entity\Person;
use App\Model\Entity\Book;

class Manager extends DBAccess
{
    public function getPersons(): array
    {
        $sql = "SELECT name, age FROM app_person ORDER BY id DESC";
        
        $query = $this->db->query($sql);
        $query->setFetchMode(\PDO::FETCH_CLASS, Person::class);

        $persons = $query->fetchAll();
        
        $query->closeCursor();
        
        return $persons;
    }

    public function getPersonsAndBooks(): array
    {
        $sql = "SELECT app_person.name, app_person.id, app_person.age, app_book.title as book_title, app_book.creation_date as `book_date` FROM app_person LEFT JOIN app_book ON app_book.person_id = app_person.id ORDER BY app_person.id, app_book.creation_date DESC";
        
        $query = $this->db->query($sql);
        $query->setFetchMode(\PDO::FETCH_CLASS, Person::class);

        $persons = $query->fetchAll();

        $persons = Manager::mergePersons($persons);
        
        $query->closeCursor();
        
        return $persons;
    }

    public static function mergePersons(array $persons): array
    {
        $result = [];

        foreach($persons as $person)
        {
            $book = new Book($person->book_title, $person->book_date);

            if (array_key_exists($person->id, $result))
            {
                array_push($result[$person->id]->books, $book);
            }
            else 
            {
                $result[$person->id] = $person;
                array_push($result[$person->id]->books, $book);
            }
        }
        
        return $result;
    }
}

As expected we extend the DBAccess class to be able to access our database and include a method to only retrieve all rows of the person table.

Another method features a LEFT JOIN on the book table so we can retrieve the books associated with the owner at the same time. We chose to use the PDO FETCH_CLASS mode to map the database fields directly toward our Person object.

However PDO does not seem to handle things right for such schema !

Thus we decided to implement another method (mergePersons()) to add the mapping for our books using the fields book_title and book_date which have been filled into our Person object.

Note : if you have a better implementation without actually building an ORM such as Doctrine, please drop a message.

I stumbled upon searching the best way to achieve this kind of mapping. In a next tutorial we will see how Doctrine handles this !

The Controller

This is the last part covered from the MVC pattern (skipping the View implementation).

To keep things simple, there is only 1 controller and 1 method which purpose is to tell every persons to say “Hello”  plus another method to display the same message along with the list of books owned by the person.

<?php
declare(strict_types = 1);

namespace App\Controller;

use App\Model\Manager;
use App\Model\Entity\Person;

class Frontend
{
    public const BR_TAG = "<br/>";
    private array $persons;
    private Manager $manager;

    public function __construct() 
    {
        $this->manager = new Manager();
    }

    public function helloEveryone(): void
    {
        $this->persons = $this->manager->getPersons();    

        foreach($this->persons as $person)
        {
            $person->sayHello();
            echo self::BR_TAG;
        }
    }

    public function helloWithBooks(): void
    {
        $this->persons = $this->manager->getPersonsAndBooks();    

        foreach($this->persons as $person)
        {
            echo "<fieldset><legend>";
            $person->sayHello();
            echo "</legend>";
            $person->sayHelloToMyBooks();
            echo "</fieldset>";
        }
    }
    
    public static function separator(): void 
    {
        echo "<hr/>";
    }
}

There is a little bit of HTML formatting here with the fieldset but to keep things simple, we did not add the View part.

Conclusion

That was fun to work around with PDO and typed properties, we can also tell that is might not be straightforward to properly map database fields to objects.

In more complex examples, you might opt for another PDO mode such as FETH_ASSOC and do your own properties mapping using an array and some object hydrating.

We will explore how using Doctrine will ease our work in such environment in a next article.

Additional resources :

Php pdo statement

Pdo Objects