How to map the result of an SQL query to a class in PHP using PDO and how to use it?

Question:

In the past days I saw several questions where serious mistakes were made in class design.

In at least two cases, an object that was the connection to the database was included as a member of the class. Another thing that was done was: within the constructor of the class connect to the database, make a query and fill the properties of the class with each column obtained.

In some cases they were related classes and all that logic in the constructor complicated things.

That said: is there an easier way to map the results of a query to a class, to avoid having to create a class like this:

class Actor {
    private $actor_id;
    private $actor_nombre;
    private $actor_apellido;
    private $actor_sexo;
    private $last_update;

    public function __construct($id)
    {
        $pdo = new PDO($dsn, $user_name, $pass_word);
        $strSQL="SELECT * FROM actor_20171002 WHERE actor_id = ?";
        $stmt = $pdo->prepare($strSQL);
        $arrParams=array($id);
        $stmt ->execute($arrParams);
        while ( $row= $stmt->fetch(PDO::PDO::FETCH_OBJ) ) 
        {
            $this->actor_id=$row->actor_id;
            $this->actor_nombre=$row->actor_nombre;
            $this->actor_apellido=$row->actor_apellido;
            $this->actor_sexo=$row->actor_sexo;
            $this->last_update=$row->last_update;
        }
    }

    public function getName()
    {
        return $this->actor_nombre;
    }
    public function getLastName()
    {
        return $this->actor_apellido;
    }
    public function getLastUpdate()
    {
        return new DateTime($this->last_update);
    }
}

Answer:

Indeed, the practice implemented in the question's Actor class is far fetched. Does anyone know an Actor that, as an actor, takes a connection to the database wherever he wants? None, not even the protagonist of The Matrix.

Whenever an Actor instance is created, the code in the constructor will occur:

  • connect to database
  • launch a query
  • based on the data brought fill the properties of the class

If in some part of the program it is required to use the mold that is the Actor class to fill it in another way (by hand for example with $actor->setName("Neo"); ), what would be the point of all the logic that wanted to be implemented in the constructor?

Also, as the question title indicates, there are ways to map database results to a PHP class.

One of the PDO fetch styles is called PDO::FETCH_CLASS which:

…returns a new instance of the requested class, by matching the result set columns to the class property names, and calling the constructor afterwards, unless PDO::FETCH_PROPS_LATE is also provided. If fetch_style includes PDO::FETCH_CLASSTYPE (for example, PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE ), then the class name is determined from the value of the first column.

Knowing this, it is then possible to free our class from all the logic that we wanted to implement in the constructor, leaving the class as it is, a mold to make Actor type objects and nothing else, transferring the logic to the context of use of the class. class.

It's as simple as this:

  1. Connect to database
  2. launch the query
  3. Assign the result set the fetch mode PDO::FETCH_CLASS indicating to which class the results will be mapped.
  4. Apply the fetch method on a data object. That object will receive an Actor class with the data for each column assigned to its respective member.

Let's see a working example:

Actor class

class Actor {

    private $actor_id;
    private $actor_nombre;
    private $actor_apellido;
    private $actor_sexo;
    private $last_update;

    public function __construct()
    {
    }
    public function getName()
    {
        return $this->actor_nombre;
    }
    public function getLastName()
    {
        return $this->actor_apellido;
    }
    public function getLastUpdate()
    {
        return new DateTime($this->last_update);
    }
    public function setName($actor_nombre)
    {
        $this->actor_nombre=$actor_nombre;
    }
    /*Otros setter*/
}

Code in any context of our program

$pdo = new PDO($dsn, $user_name, $pass_word);
$strSQL="SELECT * FROM actor_20171002 WHERE actor_id = ?";
$stmt = $pdo->prepare($strSQL);
$arrParams=array(2);
$stmt ->execute($arrParams);

/*Preparamos para mapear el resultado*/
$stmt->setFetchMode(PDO::FETCH_CLASS, 'Actor');
$actor = $stmt->fetch();

That's all! We already have in $actor an instance of our class filled with the data that the query brought.

Let's review it:

var_dump($actor);

Exit:

object(Actor)#3 (5) {
  ["actor_id":"Actor":private]=>
  string(1) "2"
  ["actor_nombre":"Actor":private]=>
  string(6) "Marlon"
  ["actor_apellido":"Actor":private]=>
  string(6) "Brando"
  ["actor_sexo":"Actor":private]=>
  string(1) "M"
  ["last_update":"Actor":private]=>
  string(19) "2017-10-23 10:08:02"
}

That means we can use any method/property of the class. Here we use getters to display the information:

echo "Nombre:\t {$actor->getName()}\n";
echo "Nombre:\t {$actor->getLastName()}\n";
echo "Update:\t {$actor->getLastUpdate()->format("F j, Y, g:i a")}";

Exit:

Nombre:  Marlon
Nombre:  Brando
Update:  October 23, 2017, 10:08 am

Of course, we can also modify the object, through its setter methods, for example:

$actor->setName("Neo");

fiddler

Here you can see a FIDDLE and test the code. You have to press Run - F9 on the page to see the result.

Post Views: 1
Scroll to Top