Powerful and easy to use cms to build websites, blogs or ecommerce stores.
HTML backendblogblog-engineblog-platformbloggingcmscontent-managementcontent-management-systemecommerceecommerce-platformno-codepage-builderphpphp-cmsphp7php8self-hostedshopping-cartweb-applicationwebsite-builder
<?php
/**
* Vvveb
*
* Copyright (C) 2022 Ziadin Givan
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License as
* published by the Free Software Foundation, either version 3 of the
* License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see <https://www.gnu.org/licenses/>.
*
*/
namespace Vvveb\System\Db;
use Vvveb\System\Event;
class Mysqli extends DBDriver {
/** @var ?\MySQLi The shared database connection (singleton per process). */
private static ?\mysqli $link = null;
/** @var int Number of rows affected by the last query. */
public int $affected_rows = 0;
/** @var ?int The auto-increment ID generated by the last INSERT. */
public ?int $insert_id = null;
/** @var string Identifier quote character for MySQL. */
public string $quote = '`';
/** @var string Table name prefix. */
public string $prefix = ''; //'vv_';
/**
* Get the MySQL server version as an integer.
*
* @return int|null The server version (e.g. 80031) or null if not connected.
*/
public static function version(): ?int {
if (self :: $link) {
return mysqli_get_server_version(self :: $link);
}
return null;
}
/**
* Get the MySQL server version string.
*
* @return string|null The version string (e.g. '8.0.31') or null if not connected.
*/
public static function info(): ?string {
if (self :: $link) {
return mysqli_get_server_info(self :: $link);
}
return null;
}
/**
* Get the error message from the last MySQL operation.
*
* @return string|null The error message, or null if not connected.
*/
public function error(): ?string {
if (self :: $link) {
return self :: $link->error;
}
return null;
}
/**
* Get the error code from the last MySQL operation.
*
* @return int|null The error code, or null if not connected.
*/
public function errorCode(): ?int {
if (self :: $link) {
return self :: $link->errno;
}
return null;
}
public function __construct() {}
/**
* Connect to the MySQL database.
*
* Uses a singleton pattern — returns the existing connection if already open.
* Throws an exception with a sanitised error message on failure.
*
* @param string $host Database host.
* @param string $dbname Database name.
* @param string $user Database user.
* @param string $pass Database password.
* @param int|null $port Database port.
* @param string $prefix Table name prefix.
*
* @throws \Exception On connection failure.
*
* @return ?\MySQLi The database connection, or null on failure.
*/
public function connect(string $host = DB_HOST, string $dbname = DB_NAME, string $user = DB_USER, string $pass = DB_PASS, ?int $port = DB_PORT, string $prefix = DB_PREFIX): ?\mysqli {
//connect to database
if (self :: $link) {
return self :: $link;
}
$this->prefix = $prefix;
\mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
self :: $link = new \mysqli($host, $user, $pass, $dbname, $port);
} catch (\mysqli_sql_exception $e) {
$errorMessage = str_replace($pass, '*****', $e->getMessage());
throw new \Exception($errorMessage, $e->getCode());
}
// check if a connection established
if (\mysqli_connect_errno()) {
$error = mysqli_connect_error();
$errorMessage = str_replace($pass, '*****', $error);
throw new \Exception($errorMessage, mysqli_connect_errno());
}
//sync database time
//$this->execute('SET `time_zone` = :zone', ['zone' => $this->escape(date('P'))]);
return self :: $link;
}
/**
* Get all columns for a table used for sanitizing input.
*
* @param string $tableName The table name to introspect.
* @param bool $comment Whether to include the column comment field.
*
* @return array<string, array{name: string, d: mixed, n: string, t: string, e: string, l: int|null, c?: string}>|false
* Associative array keyed by column name, or false on failure.
*/
public function getColumnsMeta(string $tableName, bool $comment = false): array|false {
$sql =
'SELECT COLUMN_NAME as name, COLUMN_DEFAULT as d, IS_NULLABLE as n, DATA_TYPE as t, EXTRA as e, CHARACTER_MAXIMUM_LENGTH as l'
. ($comment ? ', COLUMN_COMMENT as c' : '') .
' FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA`= "' . DB_NAME . '"
AND `TABLE_NAME`="' . $tableName . '"';
if ($result = $this->query($sql)) {
//$columns = $result->fetch_all(MYSQLI_ASSOC);
$columns = [];
while ($row = $result->fetch_assoc()) {
$columns[$row['name']] = $row;
}
// free result set
$result->close();
return $columns;
}
return false;
}
/**
* Get all table names in the database.
*
* @param string $db The database name.
*
* @return array<int, string>|false List of table names, or false on failure.
*/
public function getTableNames(string $db = DB_NAME): array|false {
$sql = "SELECT table_name as name
FROM information_schema.tables
WHERE table_schema = '$db' ORDER BY table_name";
if ($result = $this->query($sql)) {
//$names = $result->fetch_all(MYSQLI_ASSOC);
$names = [];
while ($row = $result->fetch_assoc()) {
$names[] = $row['name'];
}
// free result set
$result->close();
return $names;
}
return false;
}
/**
* Select a different database on the current connection.
*
* @param string $db The database name.
*
* @return bool True on success, false on failure.
*/
public function select_db(string $db): bool {
return self :: $link->select_db($db);
}
/**
* Execute a raw SQL query.
*
* Automatically connects if not connected. Updates affected_rows and insert_id.
*
* @param string $sql The SQL query to execute.
*
* @throws \Exception On query failure.
*
* @return bool|\mysqli_result Query result or true for success, false on failure.
*/
public function query(string $sql): bool|\mysqli_result {
if (!self :: $link) {
$this->connect();
}
if (LOG_SQL_QUERIES) {
error_log($sql);
}
try {
$result = self :: $link->query($sql);
} catch (\mysqli_sql_exception $e) {
$message = $e->getMessage() . "\n" . $sql . "\n - " . print_r(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 1), true);
throw new \Exception($message, $e->getCode());
}
if ($result) {
$this->affected_rows = self :: $link->affected_rows;
$this->insert_id = self :: $link->insert_id;
}
return $result;
}
/**
* Execute multiple SQL statements in a single call.
*
* @param string $sql The SQL statements to execute (semicolon-separated).
*
* @return bool True on success, false on failure.
*/
public function multi_query(string $sql): bool {
if (!self :: $link) {
$this->connect();
}
$result = self :: $link->multi_query($sql);
if ($result) {
$this->affected_rows = self :: $link->affected_rows;
$this->insert_id = self :: $link->insert_id;
}
return $result;
}
/**
* Escape a value for safe inclusion in a SQL query.
*
* Strings are escaped with real_escape_string. Null values become 'null'.
* All other types are returned as-is.
*
* @param mixed $string The value to escape.
*
* @return mixed The escaped value.
*/
public function escape(mixed $string): mixed {
if (is_string($string)) {
return self :: $link->real_escape_string($string);
}
if (is_null($string)) {
return 'null';
}
return $string;
}
/**
* Escape a string value for use in a SQL literal.
*
* @param string $string The string to escape.
*
* @return string The escaped string.
*/
public function escapeLiteral(string $string): string {
return $this->escape($string);
}
/**
* Generate a MySQL-compatible LIMIT clause.
*
* @param int|string $start Offset (0-based).
* @param int|string $limit Maximum number of rows.
*
* @return string The LIMIT clause (e.g. 'LIMIT 0, 10').
*/
public function sqlLimit(int|string $start, int|string $limit): string {
return "LIMIT $start, $limit";
}
/**
* Fetch a single row from a statement or result set.
*
* @param \mysqli_stmt|\mysqli_result $stmt The statement or result to fetch from.
*
* @return array<string, mixed> The fetched row, or empty array if no rows.
*/
public function fetchArray(\mysqli_stmt|\mysqli_result $stmt): array {
$data = [];
$statement = ($stmt instanceof \mysqli_stmt);
if ($statement) {
$this->store_result();
$result = $stmt->get_result();
} else {
$result = $stmt;
}
if ($result) {
$result = $result->fetch_array(MYSQLI_ASSOC);
if ($result) {
$data = $result;
if ($statement && $stmt->more_results()) {
$stmt->next_result();
}
}
}
return $data;
}
/**
* Fetch all rows from a statement or result set.
*
* @param \mysqli_stmt|\mysqli_result $stmt The statement or result to fetch from.
*
* @return array<int, array<string, mixed>> All result rows.
*/
public function fetchAll(\mysqli_stmt|\mysqli_result $stmt): array {
$data = [];
$statement = ($stmt instanceof \mysqli_stmt);
if ($statement) {
$this->store_result();
$result = $stmt->get_result();
} else {
$result = $stmt;
}
if ($result) {
$data = $result->fetch_all(MYSQLI_ASSOC);
}
return $data;
}
/**
* Store the result of the last query in the client-side buffer.
*
* @return \mysqli_result|false The stored result, or false on failure.
*/
public function store_result(): \mysqli_result|false {
return self :: $link->store_result();
}
/**
* Check if the current statement has more result sets.
*
* @return bool True if more results are available.
*/
public function more_results(): bool {
return self :: $link->more_results();
}
/**
* Move to the next result set in a multi-query.
*
* @return bool True on success, false on failure.
*/
public function next_result(): bool {
return self :: $link->next_result();
}
/**
* Close the database connection.
*
* @return ?bool True on success, null if not connected.
*/
public function close(): ?bool {
if ((self :: $link instanceof \mysqli)/* && self :: $link->ping()*/) {
return self :: $link->close();
}
return null;
}
/**
* Prepare and execute a parameterized query.
*
* Fires an Event::trigger hook before execution. Converts named parameters
* to positional placeholders, binds parameters, and executes the statement.
* Updates affected_rows and insert_id on success.
*
* @param string $sql The SQL query with :named parameters.
* @param array<string, mixed> $params Named parameter values.
* @param array<string, string> $paramTypes Optional explicit type overrides.
*
* @throws \Exception On prepare or execute failure.
*
* @return ?\mysqli_stmt The executed statement, or null on failure.
*/
public function execute(string $sql, array $params = [], array $paramTypes = []): ?\mysqli_stmt {
[$sql, $params] = Event::trigger(__CLASS__, __FUNCTION__, $sql, $params);
//save orig sql for debugging info
$origSql = $sql;
if (!self :: $link) {
$this->connect();
}
[$parameters, $types] = $this->paramsToQmark($sql, $params, $paramTypes);
try {
$stmt = self::$link->prepare($sql);
} catch (\mysqli_sql_exception $e) {
$message = $e->getMessage() . "\n" . $this->debugSql($origSql, $params, $paramTypes) . "\n - " . $origSql . "\n - " . print_r(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 1), true);
throw new \Exception($message, $e->getCode());
}
if ($stmt && !empty($types)) {
array_unshift($parameters, $types);
//hack for php 7.x bind_param "expected to be a reference, value given" stupid warning
$referenceArray = [];
foreach ($parameters as $key => $value) {
$referenceArray[$key] = &$parameters[$key];
}
@call_user_func_array([$stmt, 'bind_param'], $referenceArray);
}
if (LOG_SQL_QUERIES) {
error_log($this->debugSql($origSql, $params, $paramTypes));
}
if ($stmt) {
try {
if ($stmt->execute()) {
$this->affected_rows = self :: $link->affected_rows;
$this->insert_id = self :: $link->insert_id;
return $stmt;
} else {
error_log(print_r($stmt, true));
error_log($this->debugSql($sql, $params, $paramTypes));
}
} catch (\mysqli_sql_exception $e) {
$message = $e->getMessage() . "\n" . $origSql . "\n" . $this->debugSql($origSql, $params, $paramTypes) . "\n" . print_r($parameters, true) . $types . "\n" . print_r(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 1), true);
throw new \Exception($message, $e->getCode());
}
} else {
error_log(print_r($stmt, true));
error_log($this->debugSql($origSql, $params, $paramTypes));
}
return $stmt;
}
}