A design pattern called Table Data Gateway uses an object to serve as a gateway to a database table. The goal is to keep the task of retrieving items from a database apart from how those objects are actually used. As a result, modifications to the database’s object storage scheme are protected from users of the gateway.
Now, let us understand how this pattern helps with SQL operations. I am creating a database helper class based on the TDB pattern to perform basic CRUD operations.
-
Create an AbstractTableGateway class:
<?php
abstract class AbstractTableGateway
{
protected $db;
protected $tablename;
protected $columns;
protected $primary = 'id';
public function __construct(\PDO $db)
{
$this->db = $db;
}
public function listallrecordsBySQL(string $sql, array $params): array
{
$stmt = $this->db->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll();
}
public function getRecordbyID($id): ?array
{
$ret = $this->listallrecordsBySQL("SELECT * FROM $this->tablename WHERE $this->primary=?", [$id]);
return $ret ? reset($ret) : null;
}
public function updateRecordbyID(array $data, int $id): void
{
$this->validate($data);
$params = [];
$set = "";
foreach($data as $key => $value)
{
$set .= "’$key’ = ?,";
$params[] = $value;
}
$set = rtrim($set, ",");
$params[] = $id;
$sql = "UPDATE $this->tablename SET $set WHERE $this->primary=?";
$this->db->prepare($sql)->execute($params);
}
public function insertData($data): int
{
$this->validate($data);
$columns = "'".implode("’,’", array_keys($data))."’";
$placeholders = str_repeat('?,', count($data) - 1) . '?';
$sql = "INSERT INTO $this->tablename ($columns) VALUES ($placeholders)";
$this->db->prepare($sql)->execute(array_values($data));
return $this->db->lastInsertId();
}
public function delete($id)
{
$sql = "DELETE FROM $this->tablename WHERE $this->primary=?";
$this->db->prepare($sql)->execute([$id]);
}
protected function validate($data)
{
$diff = array_diff(array_keys($data), $this->columns);
if ($diff) {
throw new \InvalidArgumentException("Unknown field(s): ". implode($diff));
}
}
}
- Now we create a child class named as “UserTableGateway” where we first set the properties of the “AbstractTableGateway” class as below:
<?php
class UserTableGateway extends AbstractTableGateway {
protected $tablename = 'users';
protected $columns = ['name', 'email', 'password'];
} - Now, we are going to call the AbstractTableGateway class functions to perform the CRUD operations:
<?php
$data = [
'name' => 'Test User',
'email' => 'testuser@gmail.com',
'password' => 'hashed'
];
$id = $UserTableGateway->insertData($data); // Insert the record
$user_record = $UserTableGateway->getRecordbyID($id); //Get record by ID
print_r( json_encode($user_record));
$UserTableGateway->update(['name' => 'Test Account'], $id); //Update the name from “Test User” to “Test Account’
$user_record = $UserTableGateway->getRecordbyID($id); //Get record by ID
print_r( json_encode($user_record));
$UserTableGateway->delete($id); //Delete the “Test Account” record
Get certification for your knowledge in the fundamentals of Computer functioning by clearing the Computer Certification exam conducted by StudySection. After going through this Computer Certification Exam, you will be able to evaluate your basic knowledge of computers.
