PHP Design Patterns: Active Record

What is Active Record? Active Record is a design pattern that features a class that has both persistence methods and business methods

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 connproperty is static to maintain the value, so we don't need to open the same connection more than once.

The datavariable is defined as mixed, as it can take on several types.

• setConnectionmethod

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 thedataattribute 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} 
";
 // 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} 
";
 $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} 
";
 $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

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 . "
";
 }