<?php
trait DatabaseTrait
{
/**
* Name of the primary field in the database/object.
* @return string name of field
*/
abstract static function getPrimaryField(): string;
/**
* Name of the table for this class.
* @return string name of table
*/
abstract static function getTable(): string;
/**
* Name of the Object for this class.
* @param int $id
* @return static name of whatever object in use
*/
abstract static function getObject(int $id): static;
/**
* Returns the primary key, usually an int.
* @return int
*/
function getID(): int
{
return $this->{static::getPrimaryField()};
}
/**
* Inserts a new record into the table.
*
* @param array $array An associative array representing the values to be inserted
* @return int The ID of the newly inserted record
* @throws Exception
*/
static function insert(array $array): int
{
$db = new Database();
return $db->insert(static::getTable(), $array);
}
/**
* Updates the record in the table with the specified values.
*
* @param array $array The array of key-value pairs representing the columns and their new values.
* @return bool Returns true if the update was successful, false otherwise.
* @throws Exception Thrown if the input array is empty.
*/
function update(array $array): bool
{
$db = new Database();
return $db->update(static::getTable(), $array, [static::getPrimaryField() => $this->getID()]);
}
/**
* Delete the record from the table.
* @return bool Returns true if the record is successfully deleted, otherwise false.
* @throws Exception
*/
function delete(): bool
{
$db = new Database();
return $db->delete(static::getTable(), [static::getPrimaryField() => $this->getID()]);
}
/**
* Retrieves all records from the table, with optional order by clause.
*
* @param array $orderBy Optional order by clause in the form of ['column' => 'ASC/DESC']
* @return static[] An array of objects representing the records
* @throws Exception
*/
static function all(array $orderBy = []): array
{
$db = new Database();
$results = $db->select(static::getTable(), [static::getPrimaryField()], [], $orderBy);
return array_map(fn($row) => static::getObject($row[static::getPrimaryField()]), $results);
}
/**
* Get an array of objects based on a set of WHERE conditions
*
* @param array $whereFields An associative array representing the WHERE conditions. The keys are the column names
* and the values are the column values. If a value is null, it will be treated as a NULL condition.
* @param array $orderBy An associative array representing the ORDER BY conditions. The keys are the column names
* and the values are the sort direction (ASC or DESC).
* @return static[] An array of objects.
* @throws Exception
*/
static function where(array $whereFields = [], array $orderBy = [], array $whereNot = []): array
{
$db = new Database();
$results = [];
$sql = "SELECT `" . static::getPrimaryField() . "` FROM `" . static::getTable() . "`";
$clauses = [];
$types = '';
$params = [];
foreach ($whereFields as $col => $val) {
if ($val === null) {
$clauses[] = "$col IS NULL";
} else {
$clauses[] = "$col = ?";
$types .= $db->getParamType($val);
$params[] = $val;
}
}
foreach ($whereNot as $col => $val) {
if ($val === null) {
$clauses[] = "$col IS NOT NULL";
} else {
$clauses[] = "$col != ?";
$types .= $db->getParamType($val);
$params[] = $val;
}
}
if ($clauses) {
$sql .= " WHERE " . implode(" AND ", $clauses);
}
if (!empty($orderBy)) {
$orderParts = [];
foreach ($orderBy as $col => $dir) {
$orderParts[] = "$col $dir";
}
$sql .= " ORDER BY " . implode(', ', $orderParts);
}
$stmt = $db->prepareAndExecute($sql, $types, $params);
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
$results[] = static::getObject($row[static::getPrimaryField()]);
}
return $results;
}
/**
* Retrieves a single, unique object based on the provided filter conditions.
* If the query results in more than one object, an exception is thrown to indicate
* that the object is not unique. If no object matches the conditions, null is returned.
*
* @param array $whereFields Associative array of field-value pairs to filter the query.
* @return static|null The unique object matching the filter conditions, or null if no match is found.
* @throws Exception If more than one result is found for the given filter conditions.
*/
static function whereUnique(array $whereFields): ?static
{
$results = static::where($whereFields);
if (count($results) > 1) {
throw new Exception("Object is not unique");
}
return $results[0] ?? null;
}
/**
* @param array $likeFields
* @param array $orderBy
* @return array
* @throws Exception
*/
static function whereLIKE(array $likeFields = [], array $orderBy = []): array
{
$db = new Database();
$sql = "SELECT `" . static::getPrimaryField() . "` FROM `" . static::getTable() . "`";
$clauses = [];
$types = '';
$params = [];
foreach ($likeFields as $col => $val) {
if ($val === null) {
$clauses[] = "$col IS NULL";
} else {
$clauses[] = "$col LIKE ?";
$types .= 's';
$params[] = "%$val%";
}
}
if (!empty($clauses)) {
$sql .= " WHERE " . implode(" OR ", $clauses);
}
if (!empty($orderBy)) {
$orderParts = [];
foreach ($orderBy as $col => $dir) {
$orderParts[] = "$col $dir";
}
$sql .= " ORDER BY " . implode(', ', $orderParts);
}
$stmt = $db->prepareAndExecute($sql, $types, $params);
$result = $stmt->get_result();
$objects = [];
while ($row = $result->fetch_assoc()) {
$objects[] = static::getObject($row[static::getPrimaryField()]);
}
return $objects;
}
/**
* Loads the count of all entities with a given where condition (filter specified by
* $conditions array)
*
* @param array<string, mixed> $conditions
* @return int number of matching results in the DB
* @throws Exception
*/
static function countWhere(array $conditions = []): int
{
$db = new Database();
return $db->count(static::getTable(), static::getPrimaryField(), $conditions);
}
/**
* Loads the count of all entities within the table
* @return int number of matching results in the DB
* @throws Exception
*/
static function countAll(): int
{
$db = new Database();
return $db->count(static::getTable(), static::getPrimaryField());
}
/**
* This function takes in an array of IDs (or names, if names are unique
* and you have the capability to look up on names), and returns an array of
* objects of this type, all ready to be used.
*
* This will then run through a "duplicate check" (where any duplicate objects are
* culled) and cached locally.
* @param int[] $ids array of IDs to load
* @return static[]
* @throws Exception
*/
static function load(array $ids): array
{
if (empty($ids)) return [];
$db = new Database();
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$types = str_repeat('i', count($ids));
$sql = "SELECT `" . static::getPrimaryField() . "` FROM `" . static::getTable() . "` WHERE `" . static::getPrimaryField() . "` IN ($placeholders)";
$stmt = $db->prepareAndExecute($sql, $types, $ids);
$result = $stmt->get_result();
$objects = [];
while ($row = $result->fetch_assoc()) {
$objects[] = static::getObject($row[static::getPrimaryField()]);
}
return $objects;
}
}
|
Download
