What is Active Record?

Active Record is a design pattern that features a class that has both persistence methods and business methods.From the point of view of Object Orientation, this class ends up mixing many responsibilities, thus breaking the principle of single responsibility.

So one way to solve this “problem” is to create a parent class that implements the persistence methods in a generic way, finally having a separation between the business methods and the persistence methods, this design pattern is known as Layer Supertype.

Example

Next we will have an example of how Active Record works.

Step 1 - Directory System:

📦Active_Record
 ┣ 📂class
 ┃ ┗ 📜Product.php
 ┣ 📂config
 ┃ ┗ 📜config.ini
 ┣ 📂database
 ┃ ┗ 📜product.db
 ┗ 📜index.php

Step 2 - Database Config File:

host = 
name = database/product.db
user = 
pass = 
type = sqlite

Step 3 - Database:

CREATE TABLE product(
  id INTEGER PRIMARY KEY NOT NULL,
  description TEXT,
  stock FLOAT,
  cost_price FLOAT,
  sale_price FLOAT,
  bar_code TEXT,
  date_register DATE,
  origin CHAR(1)
);

Step 5 - Product Class:

class Product
{
    private static $conn;
    private mixed $data;
}

The conn property is static to maintain the value, so we don't need to open the same connection more than once.
The data variable is defined as mixed, as it can take on several types.

setConnection method

public static function setConnection(PDO $conn)
{  
    self::$conn = $conn;
}

setConnection receives a PDO connection as a parameter and stores it in the static attribute conn.

__get and __set methods

public function __get(String $prop): mixed
{  
    return $this->data[$prop];
}

public function __set(String $prop, mixed $value)
{
    $this->data[prop] = $value;
}

__get is a magic method which is invoked when reading the value of a non-existent or inaccessible property, we receive the name of the property and pass it to the data attribute as the key.
__set is a magic method which is invoked when writing a value to a non-existing or inaccessible property, we receive the name and value of the property respectively

find method

public static function find(int $id): Product
{
    // Store the SQL in a variable
    $sql = "SELECT * FROM product WHERE id = :id";
    // Print the SQL
    print "{$sql} <br>";
    // perform the prepare method
    $result = self::$conn->prepare($sql);
    // Binds a parameter to the specified variable name
    // PDO::PARAM_INT Represents the SQL INTEGER data type
    $result->bindParam(':id', $id, PDO::PARAM_INT);
    // execute sql statement
    $result->execute();
    // Fetches the next row and returns it as an object
    // __CLASS__ receives the name of the created class
    return $result->fetchObject(__CLASS__);
}

The find method receives an id as a parameter and returns an object of the Product class as a value

all method

public static function all(String $filter = null): array
{
    $sql = "SELECT * FROM product";

    // checks if there is a filter
    if($filter)
    {
      // concatenate the filter to sql statement
      $sql .= " WHERE $filter";
    }

    print "{$sql} <br>";
    $result = self::$conn->prepare($sql);
    $result->execute();
    // Sets the remaining rows of a result set
    // PDO::FETCH_CLASS Returns a new instance of the requested class
    return $result->fetchALL(PDO::FETCH_CLASS, __CLASS__);

}

the all method can receive a filter and return a array of objects as a value

delete method

public function delete(int $id, String $operator = '=')
{  
    $sql = "DELETE FROM product WHERE id $operator :id";
    print "{$sql} <br>";
    $result = self::$conn->prepare($sql);             
    $result->bindParam(':id', $id, PDO::PARAM_INT);

    if($result->execute())
    {
      // returns an array indexed by column name as returned in your result set
      return $result->fetch(PDO::FETCH_ASSOC);
    }

}

save method

public function save()
{

    if(empty($this->data['id']))
    {
      $sql = "INSERT INTO product (description, stock, cost_price, sale_price, bar_code, date_register, origin)
              VALUES (:description, :stock, :cost_price, :sale_price, :bar_code, :date_register, :origin)";
    }
    else
    {
      $sql = "UPDATE product SET description = :description, stock = : stock, cost_price = :cost_price, sale_price = :sale_price,
              bar_code = :bar_code, date_register = :date_register, origin = :origin WHERE id = :id";
    }

    print $sql;
    $result = self::$conn->prepare($sql);
    return $result->execute($this->data);

}

The save method performs both the save and update.

Testing

<?php

  require_once 'class/Produto.php';

  try
  {
    $ini = parse_ini_file('config/config.ini');
    $name = $ini['name'];

    $conn = new PDO('sqlite:' . $name);
    $conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

    Product::setConnection($conn);

    $product = new Product;

  }
  catch(Exception $e)
  {
    print $e->getMessage();
  }

INSERT

  $product->description = 'Juice';
  $product->stock = 8;
  $product->cost_price = 12;
  $product->sale_price = 18;
  $product->bar_code = '123123123';
  $product->origin = 'S';
  $product->date_register = date('Y-m-d');
  $product->save();

UPDATE

  $update = Product::find(1);
  $update->description = "Grape Juice";
  $update->save();

ALL

  foreach(Product::all() as $product)
  {
    print $product->description . ' ';
    print $product->cost_price . ' ';
    print $product->sale_price . "<br>";
  }

If you have any questions, just leave a comment below.

PHP Design Patterns: Active Record

What is Active Record?

Example

Testing