<?php
/**
* Class for managing database operations using MySQL.
* This class provides methods*/
class Database {
public string $host;
public int $port;
private string $username;
private string $password;
private string $database;
public mysqli $connection;
public int|string $insert_id = '';
/**
* Constructor method for initializing database connection
* This method sets the host, port, database name, username, and password
* properties for the database connection. It then calls the connect() method
* to establish the actual connection to the database server.
* @return void
* @throws Exception
*/
function __construct()
{
$this->host = MYSQL_DB_HOST;
$this->port = MYSQL_DB_PORT;
$this->database = MYSQL_DB_DATABASE;
$this->username = MYSQL_DB_USERNAME;
$this->password = MYSQL_DB_PASSWORD;
$this->connect();
}
/**
* Destructor method for closing the database connection
* This method is automatically called when the object is no longer referenced
* and is being destroyed. It closes the connection to the database server
* by using the mysqli_close() function, which terminates the current active
* connection.
* @return void
*/
function __destruct()
{
mysqli_close($this->connection);
}
/**
* Establishes a connection to the MySQL database server.
* This method uses the host, port, database name, username, and password
* properties that are set in the class to establish a connection to the
* MySQL database server. If the connection fails, an error message is logged
* and the script is terminated.
* @return void
* @throws Exception
*/
function connect(): void
{
if(!$this->connection = mysqli_connect($this->host.':'.$this->port, $this->username, $this->password, $this->database)) {
throw new Exception('Failed to connect to MySQL database');
}
}
/**
* Executes a given SQL query with optional parameters and returns the result.
* $results = $db->query("SELECT * FROM articles WHERE status = ? AND author_id = ?", ['published', 42]);
* @param string $sql The SQL query to be executed.
* @param array $params Optional array of parameters to bind to the query.
* @return mysqli_result The result set of the executed query.
* @throws Exception
*/
public function query(string $sql, array $params = []): mysqli_result
{
$types = $params ? $this->getParamTypes($params) : '';
$stmt = $this->prepareAndExecute($sql, $types, $params);
return $stmt->get_result();
}
/**
* Prepares and executes a SQL statement with optional parameters.
* @param string $sql The SQL query to prepare and execute.
* @param string $types Optional parameter types for binding (e.g., 's', 'i', etc.).
* @param array $params Optional array of values to bind to the prepared statement.
* @return mysqli_stmt The executed mysqli statement object.
* @throws Exception If the statement preparation or execution fails.
*/
public function prepareAndExecute(string $sql, string $types = '', array $params = []): mysqli_stmt {
$stmt = $this->connection->prepare($sql);
if (!$stmt) {
throw new Exception("Prepare failed: " . $this->connection->error);
}
if ($types !== '' && count($params) > 0) {
if (!$stmt->bind_param($types, ...$params)) {
throw new Exception("bind_param failed: " . $stmt->error);
}
} elseif ($types !== '' || count($params) > 0) {
throw new Exception("Types and Params must be populated together");
}
if (!$stmt->execute()) {
throw new Exception("Execute failed: " . $stmt->error);
}
$this->insert_id = $stmt->insert_id;
return $stmt;
}
/**
* Inserts a new record into the specified database table.
*
* @param string $table The name of the database table to insert the data into.
* @param array $data An associative array where the keys are column names and the values are the data to insert.
* @return int The ID of the newly inserted row.
* @throws Exception
*/
public function insert(string $table, array $data): int {
$fields = [];
$placeholders = [];
$types = '';
$params = [];
foreach ($data as $col => $val) {
$fields[] = "`$col`";
if ($val === null) {
$placeholders[] = "NULL";
} else {
$placeholders[] = "?";
$types .= $this->getParamType($val);
$params[] = $val;
}
}
$sql = "INSERT INTO `$table` (" . implode(', ', $fields) . ") VALUES (" . implode(', ', $placeholders) . ")";
$this->prepareAndExecute($sql, $types, $params);
return $this->insert_id;
}
/**
* Selects data from a database table based on specified conditions and order.
*
* @param string $table The name of the table to select data from.
* @param array $columns The columns to be retrieved; defaults to all columns ['*'].
* @param array $where An associative array of conditions for the WHERE clause, where keys are column names and values are their respective values.
* @param array $orderBy An associative array of columns and their sorting direction (e.g., ['column' => 'ASC', 'column2' => 'DESC']).
* @return array An array of associative arrays representing the fetched rows.
* @throws Exception
*/
public function select(string $table, array $columns = ['*'], array $where = [], array $orderBy = []): array {
$fields = implode(', ', $columns);
$sql = "SELECT $fields FROM `$table`";
$types = '';
$params = [];
$conditions = [];
foreach ($where as $col => $val) {
if ($val === null) {
$conditions[] = "$col IS NULL";
} else {
$conditions[] = "$col = ?";
$types .= $this->getParamType($val);
$params[] = $val;
}
}
if (!empty($conditions)) {
$sql .= " WHERE " . implode(' AND ', $conditions);
}
if (!empty($orderBy)) {
$orderParts = [];
foreach ($orderBy as $col => $direction) {
$orderParts[] = "$col $direction";
}
$sql .= " ORDER BY " . implode(', ', $orderParts);
}
$stmt = $this->prepareAndExecute($sql, $types, $params);
$result = $stmt->get_result();
return $result->fetch_all(MYSQLI_ASSOC);
}
/**
* Updates records in the specified table based on given data and conditions.
*
* @param string $table The name of the table to update.
* @param array $data An associative array of column-value pairs to update.
* @param array $where An associative array of column-value pairs to define the where conditions.
* @return bool Returns true if the update is executed successfully.
* @throws Exception If the data or where conditions are empty.
*/
public function update(string $table, array $data, array $where): bool {
if (empty($data) || empty($where)) {
throw new Exception("Update requires both data and where conditions");
}
$setParts = [];
$types = '';
$params = [];
foreach ($data as $col => $val) {
if ($val === null) {
$setParts[] = "$col = NULL";
} else {
$setParts[] = "$col = ?";
$types .= $this->getParamType($val);
$params[] = $val;
}
}
$conditions = [];
foreach ($where as $col => $val) {
if ($val === null) {
$conditions[] = "$col IS NULL";
} else {
$conditions[] = "$col = ?";
$types .= $this->getParamType($val);
$params[] = $val;
}
}
$sql = "UPDATE `$table` SET " . implode(', ', $setParts) . " WHERE " . implode(' AND ', $conditions);
$this->prepareAndExecute($sql, $types, $params);
return true;
}
/**
* Deletes records from a specified table based on the given conditions.
*
* @param string $table The name of the table to delete records from.
* @param array $where An associative array of conditions for the delete operation, where the key is the column name and the value is the column value.
* @return bool Returns true if the delete operation is prepared and executed successfully.
* @throws Exception If the $where parameter is empty.
*/
public function delete(string $table, array $where): bool {
if (empty($where)) {
throw new Exception("Delete requires where conditions");
}
$conditions = [];
$types = '';
$params = [];
foreach ($where as $col => $val) {
if ($val === null) {
$conditions[] = "$col IS NULL";
} else {
$conditions[] = "$col = ?";
$types .= $this->getParamType($val);
$params[] = $val;
}
}
$sql = "DELETE FROM `$table` WHERE " . implode(' AND ', $conditions);
$this->prepareAndExecute($sql, $types, $params);
return true;
}
/**
* Counts the number of rows in a table based on the given filters.
* @param string $table The name of the database table.
* @param string $primaryID The primary column to count (default is 'id').
* @param array $filter An associative array of column-value pairs to filter the rows.
* @param string $filterBoolType The logical operator for combining filters (default is 'AND').
* @return int The number of rows matching the conditions.
* @throws Exception
*/
public function count(string $table, string $primaryID = 'id', array $filter = [], string $filterBoolType = 'AND'): int {
$sql = "SELECT COUNT($primaryID) as count FROM `$table`";
$types = '';
$params = [];
$conditions = [];
foreach ($filter as $col => $val) {
if ($val === null) {
$conditions[] = "$col IS NULL";
} else {
$conditions[] = "$col = ?";
$types .= $this->getParamType($val);
$params[] = $val;
}
}
if (!empty($conditions)) {
$sql .= " WHERE " . implode(" $filterBoolType ", $conditions);
}
$stmt = $this->prepareAndExecute($sql, $types, $params);
$result = $stmt->get_result();
$row = $result->fetch_assoc();
return (int) $row['count'];
}
/**
* Executes the provided SQL query, binds the parameters, and fetches a single result row as an object.
*
* @param string $sql The SQL query to execute.
* @param array $params An array of parameters to bind to the query.
* @return object|null The fetched row as an object, or null if no rows are returned.
* @throws Exception
*/
public function fetchRecord(string $sql, array $params = []): ?object
{
$stmt = $this->prepareAndExecute($sql, 'i', $params);
$result = $stmt->get_result();
return $result->fetch_object() ?: null;
}
/**
* Determines the parameter type for a given value.
* @param mixed $value The value for which to determine the parameter type.
* @return string A string representing the parameter type ('i' for integer, 'd' for double, 's' for string or null).
*/
public function getParamType(mixed $value): string {
return match (gettype($value)) {
'integer' => 'i',
'double' => 'd',
'string' => 's',
'NULL' => 's',
default => 's'
};
}
/**
* Retrieves a concatenated string of parameter types based on the provided parameters.
* @param array $params The array of parameters for which types will be determined.
* @return string A string representing the types of the provided parameters.
*/
public function getParamTypes(array $params): string {
return implode('', array_map([$this, 'getParamType'], $params));
}
}
|
Download
