Minimalist database wrappers for PDO and MySQLI for development purposes. PSR-12 code standards, UTF8-MB4 compatible (PDO).

Database-1-PDO-UTF8MB4.php

<?php

declare(strict_types=1);

namespace App;

use PDO;

use function is_array;

/**
 * PDO database connection.
 */
// phpcs:ignore
final class Database
{
    /**
     * Database connection config.
     *
     * @todo Optimize - Use custom data type instead array
     * @var array
     */
    private $config = [
        'host'     => '',
        'name'     => '',
        'user'     => '',
        'password' => '',
    ];

    /**
     * Connection reference.
     *
     * @var null|object
     */
    private $connection;

    /**
     * Constructor sets dependencies.
     */
    public function __construct(array $config)
    {
        $this->config = $config;
    }

    /**
     * Close (opened) database connection.
     */
    public function close()
    {
        $this->connection = null;
    }

    /**
     * Return existing or open and return new database connection.
     */
    public function open() : object
    {
        return $this->connection
            ? $this->connection
            : $this->getNewConnection();
    }

    /**
     * Get all or empty result.
     *
     * @param mixed $statement PDO statement object
     */
    public function fetchAllOrEmptyResult($statement) : array
    {
        $result = $statement->fetchAll();

        if (! $result || ! is_array($result)) {
            $result = [];
        }

        return $result;
    }

    /**
     * Create and return new connection.
     */
    private function getNewConnection() : object
    {
        $config = $this->config;

        $hostDatabase = "mysql:host={$config['host']};dbname={$config['name']}";
        $encoding     = [PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4'];

        $connection = new PDO($hostDatabase, $config['user'], $config['password'], $encoding);

        $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

        return $connection;
    }
}

Database-1-Queries.php

<?php

declare(strict_types=1);

namespace App;

use Exception;
use PDO;

use function is_array;

/**
 * Database queries.
 */
// phpcs:ignore
final class DatabaseQueries
{
    /**
     * Database instance.
     *
     * @var null|object
     */
    private $db;

    /**
     * Database connection.
     *
     * @var null|object
     */
    private $connection;

    /**
     * Constructor sets dependencies.
     */
    public function __construct(Database $db)
    {
        $this->db = $db;
    }

    /**
     * Destructor closes open connections.
     */
    public function __destruct()
    {
        $this->db->close();
    }

    /**
     * Start database connection.
     */
    public function start() : self
    {
        $this->connection = $this->db->open();

        return $this;
    }

    /**
     * Get orders.
     */
    public function getOrders() : array
    {
        $sql = "SELECT * FROM `orders` ORDER BY id DESC";

        $statement = $this->connection->query($sql, PDO::FETCH_ASSOC);

        return $this->db->fetchAllOrEmptyResult($statement);
    }

    /**
     * Get total price for everything.
     */
    public function getTotalPrice() : int
    {
        $sql = "SELECT SUM(price) FROM `orders`";

        $statement = $this->connection->query($sql, PDO::FETCH_NUM);
        $priceData = $statement->fetch();

        if (! $priceData || ! is_array($priceData)) {
            throw new Exception('No price data found');
        }

        return (int) $priceData[0];
    }

    /**
     * Get highest existing order ID, increment by 1 and return.
     */
    public function getOrderId() : int
    {
        $sql       = "SELECT MAX(`order_id`) as `orderID` FROM `orders`";
        $statement = $this->connection->query($sql, PDO::FETCH_ASSOC);

        $result = $statement->fetch();
        $result = (int) $result['orderID'];

        return $result += 1;
    }
}

Database-2-MySQLI.php

<?php

declare(strict_types=1);

namespace App;

use Exception;
use mysqli;

use function mysqli_close;
use function mysqli_fetch_array;
use function mysqli_free_result;
use function mysqli_query;

use const MYSQLI_ASSOC;

/**
 * DB queries.
 */
class Database
{
    /**
     * DB host.
     */
    private $host = '';

    /**
     * DB user password.
     */
    private $pass = '';

    /**
     * DB name.
     */
    private $db = '';

    /**
     * DB user.
     */
    private $user = '';

    /**
     * Flag to mark if Database connection is ok.
     * Used to determine if methods can be executed.
     */
    private $connectionOk = false;

    /**
     * Flag to mark if SQL query is ok.
     * Used to determine if methods can be executed.
     */
    private $queryOk = false;

    /**
     * DB connection object.
     */
    private $connection = null;

    /**
     * SQL query result object or error.
     */
    private $query = null;

    /**
     * Constructor sets Database access.
     *
     * @param string $host
     * @param string $pass
     * @param string $db
     * @param string $user
     */
    public function __construct($host, $pass, $db, $user = '')
    {
        $this->host = $host;
        $this->pass = $pass;
        $this->db   = $db;
        $this->user = $user ? $user : $db;
    }

    /**
     * Try to connect to Database and store object.
     * Set 'ok'-flag on success or call 'auto-clean/close'.
     *
     * @return $this
     */
    public function connect()
    {
        $this->connection = new mysqli($this->host, $this->user, $this->pass, $this->db);

        if ($this->connection->connect_errno) {
            $this->close();

            throw new Exception('Failed to connect: ' . $this->connection->connect_errno);
        } else {
            $this->connectionOk = true;

            return $this;
        }
    }

    /**
     * Query Database and return result or error.
     * Set 'ok'-flag on success or call 'auto-clean/close'.
     *
     * @param string $query
     * @return self
     */
    public function query($query = '')
    {
        if ($this->connectionOk) {
            if ($this->query = mysqli_query($this->connection, $query)) {
                $this->queryOk = true;

                return $this;
            }
        }

        return $this->close();
    }

    /**
     * Global auto-clean/close method.
     * Try to close database connection, free query results and reset properties.
     *
     * @return $this
     */
    public function close()
    {
        // Free query and reset properties
        if ($this->query !== null) {
            if ($this->query !== false) {
                mysqli_free_result($this->query);
            }

            $this->queryOk = false;
            $this->query   = null;
        }

        // Close connection
        if ($this->connection !== null) {
            mysqli_close($this->connection);

            $this->connectionOk = false;
            $this->connection   = null;
        }

        return $this;
    }

    /**
     * Write query result to associative array and close connection.
     * Call this function last.
     *
     * @return self|false
     */
    public function toArray()
    {
        if ($this->queryOk && ! empty($this->query)) {
            $result = [];

            while ($item = mysqli_fetch_array($this->query, MYSQLI_ASSOC)) {
                $result[] = $item;
            }

            $this->close();

            return $result;
        }

        $this->close();

        return false;
    }
}