Skip to content

Instantly share code, notes, and snippets.

@serial

serial/db.class.php

Last active July 3, 2024 06:49
Show Gist options
  • Save serial/bbdb2d3f45fc849ed9c0fc35b1f666e9 to your computer and use it in GitHub Desktop.
Save serial/bbdb2d3f45fc849ed9c0fc35b1f666e9 to your computer and use it in GitHub Desktop.
Download ZIP
PDO database (PHP version >= 8.0)
Raw
db.class.php
<?php
/**
* PHP PDO Class
*
* @require PHP >= 8.0
* @usage
*
* // Construct
* $db = new Database( '$db_host', '$db_name', '$db_user', '$db_pass' );
* $db->setTable = 'test_table';
*
* // Insert
* $db->insert( ['name' => 'General', 'brand_id' => 2] );
* $db->insert( [data_array] );
*
* // Update
* $db->update( ['name' => 'WORD'], 2 );
* $db->update( ['name' => '123'], ['brand_id' => 1] );
* $db->update( [data_array], [where_stmt] );
*
* // Delete
* $db->delete( 11 );
* $db->delete( ['name' => 'General'] );
* $db->delete( [where_stmt] );
*
* // Select
* $db->select( 'phone_id, name', 2 );
* $db->select( 'phone_id, name', ['brand_id' => 2] );
* $db->select( ['phone_id', 'name'], ['brand_id' => 2] );
* $db->select( '*', ['brand_id' => 2] );
* $db->select( [data], [where_stmt] );
*/
class Database
{
private $pdo;
private $table;
/**
* Database constructor
*
* @param $host
* @param $dbname
* @param $username
* @param $password
* @param string $charset
*
* @throws Exception
*
*/
public function __construct($host, $dbname, $username, $password, $charset = 'utf8') {
$dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
$this->pdo = new PDO($dsn, $username, $password, $options);
}
// ------------------------------------------------------------------------
/**
* Set table name
*
* @param string $table
*/
public function setTable($table) {
$this->table = $table;
}
// ------------------------------------------------------------------------
/**
* Check if table name is set
*
* @return void
*
* @throws Exception
*/
private function _isTableSet() {
if (empty($this->table)) {
throw new Exception('Table name is not set.');
}
}
// ------------------------------------------------------------------------
/**
* Build WHERE clause
*
* @param $where
*
* @return string
*/
private function _whereBuilder($where) {
$where_stmt = '';
if (is_numeric($where)) {
$where_stmt = " WHERE `id` = :primary_key";
} elseif (is_array($where)) {
$conditions = [];
foreach ($where as $key => $value) {
$conditions[] = "`$key` = :$key";
}
if (!empty($conditions)) {
$where_stmt = ' WHERE ' . implode(' AND ', $conditions);
}
}
return $where_stmt;
}
// ------------------------------------------------------------------------
/**
* Select data from table
*
* @param string|array $columns
* @param string|array $where
*
* @return array
*
* @throws Exception
*/
public function select($columns = '*', $where = null) {
$this->_isTableSet();
if (is_array($columns)) {
$columns = implode(',', $columns);
}
$where_stmt = $this->_whereBuilder($where);
if (!is_array($where)) {
$where = ['primary_key' => $where];
}
$sql = "SELECT $columns FROM `{$this->table}` $where_stmt";
$stmt = $this->pdo->prepare($sql);
$stmt->execute($where);
return $stmt->fetchAll();
}
// ------------------------------------------------------------------------
/**
* Insert data into table
*
* @param array $data
* @return bool
*
* @throws Exception
*/
public function insert($data) {
$this->_isTableSet();
$columns = implode('`, `', array_keys($data));
$placeholders = implode(', ', array_map(function ($key) {
return ":$key";
}, array_keys($data)));
$sql = "INSERT INTO `{$this->table}` (`$columns`) VALUES ($placeholders)";
$stmt = $this->pdo->prepare($sql);
return $stmt->execute($data);
}
// ------------------------------------------------------------------------
/**
* Update data in table
*
* @param array $data
* @param string|array $where
* @return bool
*
* @throws Exception
*/
public function update($data, $where) {
$this->_isTableSet();
$set_clause = implode(', ', array_map(function ($key) {
return "`$key` = :$key";
}, array_keys($data)));
$where_stmt = $this->_whereBuilder($where);
$sql = "UPDATE `{$this->table}` SET $set_clause $where_stmt";
$stmt = $this->pdo->prepare($sql);
// Merge $data and $where for binding
$params = array_merge($data, is_array($where) ? $where : ['primary_key' => $where]);
return $stmt->execute($params);
}
// ------------------------------------------------------------------------
/**
* Delete data from table
*
* @param string|array $where
* @return bool
*
* @throws Exception
*/
public function delete($where) {
$this->_isTableSet();
$where_stmt = $this->_whereBuilder($where);
$sql = "DELETE FROM `{$this->table}` $where_stmt";
$stmt = $this->pdo->prepare($sql);
return $stmt->execute(is_array($where) ? $where : ['primary_key' => $where]);
}
// ------------------------------------------------------------------------
/**
* Get all data from a table
*
* @return false|PDOStatement
*
* @throws Exception
*/
public function getData() {
$this->_isTableSet();
$sql = "SELECT * FROM `{$this->table}`";
return $this->pdo->query($sql);
}
}
Raw
db.config.php
<?php
const DB_HOST = 'localhost';
const DB_USER = 'db_username';
const DB_PASSWORD = 'db_passwort';
const DB_NAME = 'db_dbname';
const DB_CHARSET = 'utf8mb4';
Raw
example.php
<?php
require "db.class.php";
require "db.config.php";
include "html_helper.php";
html_start("PDO Connection");
try {
// Create new Database object
$db = new Database(DB_HOST, DB_NAME, DB_USER, DB_PASSWORD);
// Set the table to work with
$db->setTable('test_table');
// select data by primary key (id)
$result = $db->select( 'first_name, last_name', 1 );
//select data by where clause
$result = $db->select( 'first_name, last_name, email', ['last_name' => 'Doe'] );
print_pre($result);
// ------------------------------------------------------------------------
/*
// Select
$data = [
'first_name',
'last_name'
];
$where = [
'id' => '6',
'email' => 'dings@localhost'
];
$results = $db->select(['first_name', 'last_name'], ['first_name' => 'Jon', 'last_name' => 'Doe']);
print_pre($results);
*/
/*
// Insert
$data = [
'first_name' => 'John',
'last_name' => 'Doe',
'email' => 'test@test.de'
];
$inserted = $db->insert($data);
echo $inserted ? "Einfügen erfolgreich\n" : "Einfügen fehlgeschlagen\n";
*/
/*
// Update
$data = [
'first_name' => 'Max',
'last_name' => 'Mueller'
];
$where = [
'id' => 7
];
$updated = $db->update($data, $where);
echo $updated ? "Aktualisierung erfolgreich\n" : "Aktualisierung fehlgeschlagen\n";
*/
/*
// Delete
$where = [
'id' => 2
];
$deleted = $db->delete($where);
echo $deleted ? "Löschen erfolgreich\n" : "Löschen fehlgeschlagen\n";
*/
/*
// Select all data
$result = $db->getData()->fetchAll();
print_pre($result);
*/
} catch (Exception $e) {
echo "Error: " . $e->getMessage();
}
html_end();
Raw
html_helper.php
<?php
ini_set('error_reporting', E_ALL);
ini_set('display_errors', 1);
//mysqli_report(MYSQLI_REPORT_ALL);
function print_pre($data) {
echo "<pre>";
print_r($data);
echo "</pre>";
}
function html_start($title = "Default") {
echo "<!DOCTYPE html>";
echo "<html lang='de'>";
echo "<head>";
echo "<meta charset='UTF-8'>";
echo "<meta name='viewport' content='width=device-width, initial-scale=1.0'>";
echo "<title>$title</title>";
echo "</head>";
echo "<body>";
}
function html_end() {
echo "</body>";
echo "</html>";
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment