Skip to content

Instantly share code, notes, and snippets.

@mrwadson
Last active Oct 1, 2022
Embed
What would you like to do?
Rapidly usage MySQL class
<?php
/*
* Usage
*
require_once __DIR__ . '/MySql.php';
$mysql = new MySqlCrud('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"');
$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);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment