MySQL class

MySql.php

<?php
/*
 * Usage
 *
require_once __DIR__ . '/MySql.php';
$mysql = new MySql('database', 'db_username', 'db_password');

$rows = $mysql->select('table', 'col1 > 3 AND col2 = 10', 'col1, col2 DESC, col3, col4 ASC', [10 => 5]); // LIMIT 10 OFFSET 5
$rows = $mysql->select(['table' => true], 'col1 > 3 AND col2 = 10', 'col1, col2 DESC'); // DISTINCT

$result = $mysql->insert('table', ['val1', 'val2'], ['col1', 'col2']);
$result = $mysql->insert('table', [['val1', 'val2'], ['val3', 'val4']], ['col1', 'col2']); // Multiple values (same columns)

$result = $mysql->update('table', ['col1' => 10, 'col2' => 'string'], 'col1 = 100 AND col2 = 200');

$result = $mysql->delete('table', 'col1 = "val1"');

$result = $mysql->clear('table');

// row
$rows = $mysql->rows('SELECT * FROM table WHERE col1 > 10 AND col2 IS NULL');
$result = $mysql->raw('UPDATE table SET col1 = "val1", col2 = "val2" WHERE col3 = "val3"');

// print html table
if ($rows) {
    $first = reset($rows);
    echo "<table border='0'>\n";

    $fields = array_map(static function($key, $value) {
        return "<th>$key</th>";
    }, array_keys($first), $first);

    echo "<tr>" . implode('', $fields) . "</tr>\n";

    foreach($rows as $row) {
        $values = array_map(static function($key, $value) {
            return "<td>$value</td>";
        }, array_keys($row), $row);

        echo "<tr>" . implode('', $values) . "</tr>\n";
    }

    echo "</table>\n";
}

$affectedRows = $mysql->$affected;
*/

class MySql
{
    /** @var mysqli */
    private $mysql;
    /**
     * @var int
     */
    public $affected;

    public function __construct(string $database, string $username, string $password,
        string $hostname = 'localhost', int $port = 3306)
    {
        $this->mysql = new mysqli($hostname, $username, $password, $database, $port);
        if ($this->mysql->connect_error) {
            die('Connect Error (' . $this->mysql->connect_errno . ') ' . $this->mysql->connect_error);
        }

        $this->mysql->set_charset('utf8mb4');
        $this->mysql->query("SET SESSION sql_mode = 'NO_ZERO_IN_DATE,NO_ENGINE_SUBSTITUTION'");
        $this->mysql->query('SET FOREIGN_KEY_CHECKS = 0');
    }

    public function select(
        $table,
        string $columns = null,
        string $where = null,
        string $order = null,
        string $group = null,
        string $having = null,
        $limit = null): array
    {
        $rows = [];

        $distinct = $offset = false;
        if (is_array($table)) {
            $distinct = reset($table);
            $table = key($table);
        }
        if (is_array($limit)) {
            $offset = key($limit);
            $limit = reset($limit);
        }

        $select = 'SELECT ' . ($distinct ? 'DISTINCT ' : ' ') . ($columns ?: '*');

        $sql = sprintf("%s FROM `%s`%s%s%s%s%s%s",
            $select,
            $table,
            $where ? ' WHERE ' . $where : '',
            $group ? ' GROUP BY ' . $group : '',
            $having ? ' HAVING ' . $having : '',
            $order ? ' ORDER BY ' . $order : '',
            $limit ? ' LIMIT ' . $limit : '',
            $offset ? ' OFFSET ' . $offset : ''
        );

        $result = $this->query($sql);
        if ($result) {
            while ($row = $result->fetch_assoc()) {
                $rows[] = $row;
            }
            $result->free();
        }

        return $rows;
    }

    public function insert(string $table, array $values, array $columns = null)
    {
        $prepare = [];
        if (is_array($values[0])) {
            foreach ($values as $item) {
                $prepare[] = '(' . "'" . implode("','", $item) . "'" . ')';
            }
            $prepare = implode(',', $prepare);
        } else {
            $prepare = "('" . implode("','", $values) . "')";
        }

        $sql = sprintf('INSERT INTO `%s`%s VALUES %s', $table, ($columns ?  ' (' . implode(', ', $columns) . ')' : ''), $prepare);
        return $this->query($sql);
    }

    public function update(string $table, array $columnValues, string $where)
    {
        $set = [];
        foreach ($columnValues as $col => $val) {
            $set[] = "$col = $val";
        }

        $sql = sprintf('UPDATE `%s` SET %s%s', $table, implode(', ', $set), $where ? ' WHERE ' . $where : '');
        return $this->query($sql);
    }

    public function delete(string $table, string $where = null)
    {
        $sql = sprintf('DELETE FROM `%s`%s', $table, $where ? ' WHERE ' . $where : '');
        return $this->query($sql);
    }

    public function rows(string $sql): array
    {
        $rows = [];
        $result = $this->query($sql);
        if ($result) {
            while ($row = $result->fetch_assoc()) {
                $rows[] = $row;
            }
            $result->free();
        }

        return $rows;
    }

    public function raw(string $query)
    {
        return $this->query($query);
    }

    public function clear(string $table)
    {
        return $this->query(sprintf('TRUNCATE %s', $table));
    }

    private function query(string $sql)
    {
        if (!($result = $this->mysql->query($sql, MYSQLI_USE_RESULT))) {
            die(sprintf("Query Error (%s) %s\n", $this->mysql->errno, $this->mysql->error));
        }
        $this->affected = $this->mysql->affected_rows;

        return $result;
    }

    public function __destruct() {
        if ($this->mysql) {
            $this->mysql->close();
            unset($this->mysql);
        }
    }
}