Last active
February 1, 2022 13:10
-
-
Save rseon/59f02a8e0cb8b31f4a147aaf7379205d to your computer and use it in GitHub Desktop.
[PHP] Simple database abstraction class (using PDO)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| /** | |
| * Simple MySQL database abstraction class (using PDO). | |
| * | |
| * @method Database::connect(array $config): Database | |
| * @method Database::getInstance(): Database | |
| * @method $Database->setConfig($key, $value): void | |
| * @method $Database->getConfig(): array | |
| * @method $Database->getDBO(): PDO | |
| * @method $Database->printOnly(bool $flag): void | |
| * @method $Database->prefix(string $prefix): void | |
| * @method $Database->readOnly(bool $flag = true): void | |
| * @method $Database->getAll(string $table, array $conditions = [], array $order = [], ?array|int $limit = null, array|string $only_fields = '*'): array | |
| * @method $Database->getRow(string $table, array $conditions = [], array|string $only_fields = null): array | |
| * @method $Database->getValue(string $fieldname, string $table, array $conditions = []): array|bool|mixed | |
| * @method $Database->count(string $table, array $conditions = [], string $fieldsearch = '*'): int|false | |
| * @method $Database->fetchAll(string $sql, array $data = []): array | |
| * @method $Database->fetchRow(string $sql, array $data = []): array | |
| * @method $Database->insert(string $table, array $data): int | |
| * @method $Database->update(string $table, array $data, array $conditions): int | |
| * @method $Database->delete(string $table, array $conditions): int | |
| * @method $Database->routine(string $name, array $params = []): array | |
| * @method $Database->truncate(string $table): bool | |
| * @method $Database->drop(string $table): bool | |
| * @method $Database->create(string $table, array $columns, array $indexes = [], ?string $extra = null): bool | |
| * @method $Database->getLastError(): array | |
| * @method $Database->getLastQuery(bool $raw_query = false): string | |
| * @method $Database->getLastQueryParams(): array | |
| * @method $Database->debug(): array | |
| */ | |
| class Database | |
| { | |
| const ON_ERROR_EXIT = 'exit'; | |
| const ON_ERROR_EXCEPTION = 'exception'; | |
| const ON_ERROR_CONTINUE = 'continue'; | |
| const RAW_QUERY = true; | |
| private static $_instance; | |
| protected $dbo; | |
| protected $config; | |
| protected $last_query = null; | |
| protected $last_query_params = []; | |
| protected $last_error = []; | |
| protected $printOnly = false; | |
| protected $prefix = ''; | |
| protected $readonly = false; | |
| // These methods will be disabled if readonly mode is active | |
| protected static $readonly_methods = ['insert', 'update', 'delete', 'truncate', 'drop', 'create']; | |
| /** | |
| * Connect to the database | |
| * | |
| * @example $db = Database::connect([ | |
| * 'host' => 'localhost', | |
| * 'username' => 'root', | |
| * 'password' => '', | |
| * 'dbname' => 'my_db', | |
| * 'port' => 3306, | |
| * 'charset' => 'utf8', | |
| * 'on_error' => ON_ERROR_EXIT | ON_ERROR_EXCEPTION | ON_ERROR_CONTINUE, // default ON_ERROR_EXIT | |
| * ]); | |
| * | |
| * Error handling : | |
| * ON_ERROR_EXIT : Display error message and stop script execution. | |
| * ON_ERROR_EXCEPTION : Throw the Exception (stop script execution). | |
| * ON_ERROR_CONTINUE : For debug purpose only, because execution is not stopped. | |
| * Good practice is debugging with methods like getLastError() or | |
| * debug() then stop execution with an `exit` or `die` instruction. | |
| * | |
| * @param array $config array(host, username, password, dbname [, port [, charset [, on_error] ]]) | |
| * | |
| * @return Database | |
| */ | |
| public static function connect(array $config) | |
| { | |
| return new static($config); | |
| } | |
| /** | |
| * Returns instance of connection | |
| * | |
| * @example Database::getInstance()->getAll() | |
| * | |
| * @return Database | |
| * @throws Exception | |
| */ | |
| public static function getInstance() | |
| { | |
| if (!(static::$_instance instanceof static)) { | |
| throw new Exception('Please connect first'); | |
| } | |
| return static::$_instance; | |
| } | |
| /** | |
| * Set configuration key | |
| * | |
| * @param mixed $value | |
| */ | |
| public function setConfig($key, $value) | |
| { | |
| $this->config[$key] = $value; | |
| } | |
| /** | |
| * Get configuration | |
| * | |
| * @return array | |
| */ | |
| public function getConfig() | |
| { | |
| return $this->config; | |
| } | |
| /** | |
| * Allows you to directly execute methods of the PDO class. | |
| * | |
| * Warning : prefix, readOnly and printOnly methods won't be executed | |
| * | |
| * @example $this->getDBO()->execute() | |
| * | |
| * @return PDO | |
| * | |
| * @link http://php.net/manual/fr/class.pdo.php | |
| */ | |
| public function getDBO() | |
| { | |
| return $this->dbo; | |
| } | |
| /** | |
| * If true, all requests are not executed but printed. | |
| * | |
| * @param bool $flag | |
| */ | |
| public function printOnly(bool $flag = true) | |
| { | |
| $this->printOnly = $flag; | |
| } | |
| /** | |
| * Set a prefix to tables. | |
| * | |
| * The prefix will be automatically added to the table name for the following methods : | |
| * getAll, getRow, getValue, count, insert, update, delete | |
| * | |
| * @param string $prefix | |
| */ | |
| public function prefix(string $prefix) | |
| { | |
| $this->prefix = $prefix; | |
| } | |
| /** | |
| * Set the database as readonly mode (insert, update, delete will not be available) | |
| * | |
| * @param bool $flag | |
| */ | |
| public function readOnly(bool $flag = true) | |
| { | |
| $this->readonly = $flag; | |
| } | |
| /** | |
| * Returns the rows of the $table corresponding to the $conditions, ordered by $order and if needed only the $limit rows. | |
| * You can get $only_fields. | |
| * | |
| * @example $this->getAll( | |
| * 'my_table', | |
| * [ | |
| * 'active' => 1, // WHERE `active` = 1 | |
| * 'id_user >=' => 1, // AND `id_user` >= 1 | |
| * 'name IS NOT NULL', // AND `name` IS NOT NULL | |
| * 'id_client' => [3,4], // AND `id_client` IN (3,4) | |
| * 'id_client NOT IN' => [5,6] // AND `id_client` NOT IN (5,6) | |
| * ], | |
| * ['id_user', 'active DESC'], // ORDER BY `id_user`, `active` DESC | |
| * ['active', 'id_user' => 'user'] // SELECT active, id_user as user | |
| * ) | |
| * @example $limit is an integer : returns the $limit first rows | |
| * $this->getAll('my_table', [], [], 10) // LIMIT 10 | |
| * @example $limit is an array [$nb] : returns the $nb first rows | |
| * $this->getAll('my_table', [], [], [10]) // LIMIT 10 | |
| * @example $limit is an array [$nb, $offset] : returns the $nb rows from the $offset'th | |
| * $this->getAll('my_table', [], [], [10, 20]) // LIMIT 10 OFFSET 20 | |
| * @example $only_fields is a string | |
| * $this->getAll('my_table', [], [], null, 'active, id_user as user') // SELECT active, id_user as user | |
| * @example $only_fields is an array | |
| * $this->getAll('my_table', [], [], null, ['active', 'id_user' => 'user']) // SELECT active, id_user as user | |
| * | |
| * @param string $table | |
| * @param array $conditions | |
| * @param array $order | |
| * @param array|int $limit | |
| * @param array|string $only_fields | |
| * | |
| * @return array | |
| * @throws Exception | |
| */ | |
| public function getAll(string $table, array $conditions = [], array $order = [], $limit = null, $only_fields = '*') | |
| { | |
| $_fields = '*'; | |
| if (!empty($only_fields)) { | |
| if (is_array($only_fields)) { | |
| $_array_fields = []; | |
| foreach ($only_fields as $k => $v) { | |
| if (is_numeric($k)) { | |
| $_array_fields[] = $v; | |
| } | |
| else { | |
| $_array_fields[] = "{$k} as {$v}"; | |
| } | |
| } | |
| $only_fields = $_array_fields; | |
| $_fields = implode(', ', $only_fields); | |
| } | |
| else { | |
| $_fields = $only_fields; | |
| } | |
| } | |
| $sql = "SELECT {$_fields} FROM `{$this->prefix}{$table}`"; | |
| $sql .= static::createWhere($conditions); | |
| if ($order) { | |
| $_order = implode(', ', array_map(function($v) { | |
| if (strpos($v, ' ') !== false) { | |
| list($a, $b) = explode(' ', $v); | |
| return "`{$a}` {$b}"; | |
| } | |
| return "`{$v}`"; | |
| }, $order)); | |
| $sql .= " ORDER BY {$_order}"; | |
| } | |
| if ($limit) { | |
| $limit_nb = null; | |
| $limit_offset = 0; | |
| if (is_numeric($limit)) { | |
| $limit_nb = (int)$limit; | |
| } | |
| elseif (is_array($limit)) { | |
| if (isset($limit[0])) { | |
| $limit_nb = (int)$limit[0]; | |
| } | |
| if (isset($limit[1])) { | |
| $limit_offset = (int)$limit[1]; | |
| } | |
| } | |
| if ($limit_nb) { | |
| $sql .= " LIMIT {$limit_nb} OFFSET {$limit_offset}"; | |
| } | |
| } | |
| if ($this->printOnly) { | |
| return $this->printQuery($sql, $conditions); | |
| } | |
| $this->resetLastQuery(); | |
| $stmt = $this->dbo->prepare($sql); | |
| $this->bindValues($stmt, $conditions); | |
| $this->setLastQuery($stmt); | |
| try { | |
| $stmt->execute(); | |
| return $stmt->fetchAll(); | |
| } catch(\PDOException $e) { | |
| $this->catchAndLaunchError($e, $stmt); | |
| } | |
| return []; | |
| } | |
| /** | |
| * Returns one row of the $table corresponding to the $conditions | |
| * | |
| * @param string $table | |
| * @param array $conditions | |
| * @param array|string $only_fields | |
| * | |
| * @return array | |
| * @throws Exception | |
| * | |
| * @see Database::getAll() | |
| */ | |
| public function getRow(string $table, array $conditions = [], $only_fields = null) | |
| { | |
| $res = $this->getAll($table, $conditions, [], 1, $only_fields); | |
| if ($this->printOnly) { | |
| return $res; | |
| } | |
| if (isset($res[0])) { | |
| return $res[0]; | |
| } | |
| return []; | |
| } | |
| /** | |
| * Get the value of field $fieldname (false if unknown). | |
| * If $fieldname is an array, returns an array of the values. | |
| * | |
| * @example $this->getValue('active', 'my_table', ['id_user' => 1]); // => string "1" | |
| * @example $this->getValue(['id_user', 'active'], 'my_table', ['id_user' => 1]); // => array ['id_user' => 1, 'active' => 1]; | |
| * @example $this->getValue(['MAX(id_user) as nb', 'active'], 'my_table'); // => array ['nb' => 4, 'active' => 1]; | |
| * | |
| * @param string|array $fieldname | |
| * @param string $table | |
| * @param array $conditions | |
| * | |
| * @return array|bool|mixed | |
| * @throws Exception | |
| * | |
| * @see Database::getRow() | |
| */ | |
| public function getValue($fieldname, string $table, array $conditions = []) | |
| { | |
| $res = $this->getRow($table, $conditions, $fieldname); | |
| if ($this->printOnly) { | |
| return $res; | |
| } | |
| if (!$res) { | |
| return false; | |
| } | |
| if (is_array($fieldname)) { | |
| $returns = []; | |
| foreach ($fieldname as $f) { | |
| if (strpos(strtolower($f), ' as ') !== false) { | |
| $f = explode(' as ', strtolower($f))[1]; | |
| } | |
| if (isset($res[$f])) { | |
| $returns[$f] = $res[$f]; | |
| } | |
| else { | |
| $returns[$f] = false; | |
| } | |
| } | |
| if (count($returns) === 1) { | |
| return array_values($returns)[0]; | |
| } | |
| return $returns; | |
| } | |
| if (strpos(strtolower($fieldname), ' as ') !== false) { | |
| $fieldname = explode(' as ', strtolower($fieldname))[1]; | |
| } | |
| if (isset($res[$fieldname])) { | |
| return $res[$fieldname]; | |
| } | |
| return false; | |
| } | |
| /** | |
| * Performs a COUNT request and return its result | |
| * | |
| * @example $this->count('my_table', ['active' => 1]); // => int 12 | |
| * @example $this->count('my_table', ['active' => 1], 'primary_key'); // => int 12 | |
| * | |
| * @param string $table | |
| * @param array $conditions | |
| * @param string $fieldsearch | |
| * | |
| * @return int|false | |
| * @throws Exception | |
| * | |
| * @see Database::getRow() | |
| */ | |
| public function count(string $table, array $conditions = [], string $fieldsearch = '*') | |
| { | |
| $fieldname = 'count'; | |
| $res = $this->getRow($table, $conditions, "COUNT({$fieldsearch}) as {$fieldname}"); | |
| if ($this->printOnly) { | |
| return $res; | |
| } | |
| if (isset($res[$fieldname])) { | |
| return (int) $res[$fieldname]; | |
| } | |
| return false; | |
| } | |
| /** | |
| * Returns all rows of the query $sql. | |
| * For prepared statements you must fill in the array of values $data. | |
| * | |
| * @example $this->fetchAll('SELECT * FROM user') | |
| * @example $this->fetchAll('SELECT * FROM user WHERE id_user = :id_user AND active = 1', ['id_user' => 1]) | |
| * | |
| * @param string $sql | |
| * @param array $data | |
| * | |
| * @return array | |
| * @throws Exception | |
| */ | |
| public function fetchAll(string $sql, array $data = []) | |
| { | |
| // Check not allowed keywords | |
| // @link https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html | |
| $not_allowed = [ | |
| 'alter', 'change', 'create', 'deallocate', 'delete', 'describe', 'do', 'drop', 'execute', 'explain', 'handler', 'help', 'import', 'insert', 'load', 'lock', 'prepare', 'purge', 'replace', 'rename', 'reset', 'savepoint', 'set', 'show', 'start', 'stop', 'truncate', 'update', 'use', 'xa', | |
| ]; | |
| $sql_clean = strtolower($sql); | |
| $sql_clean = preg_replace('/\s+/', ' ', $sql_clean); // Remove multiple whitespaces | |
| foreach ($not_allowed as $not) { | |
| if (strpos($sql_clean, $not.' ') !== false) { | |
| $this->launchError('Not allowed keyword in this method ('.$not.')'); | |
| exit; | |
| } | |
| } | |
| if ($this->printOnly) { | |
| return $this->printQuery($sql, $data); | |
| } | |
| $this->resetLastQuery(); | |
| $stmt = $this->dbo->prepare($sql); | |
| $this->bindValues($stmt, $data); | |
| $this->setLastQuery($stmt); | |
| try { | |
| $stmt->execute(); | |
| return $stmt->fetchAll(); | |
| } catch(\PDOException $e) { | |
| $this->catchAndLaunchError($e, $stmt); | |
| } | |
| return []; | |
| } | |
| /** | |
| * Returns one row of the query $sql. | |
| * For prepared statements you must fill in the array of values $data. | |
| * | |
| * @param $sql | |
| * @param array $data | |
| * | |
| * @return array | |
| * @throws Exception | |
| * | |
| * @see Database::fetchAll() | |
| */ | |
| public function fetchRow(string $sql, array $data = []) | |
| { | |
| $res = $this->fetchAll($sql, $data); | |
| if ($this->printOnly) { | |
| return $res; | |
| } | |
| if (isset($res[0])) { | |
| return $res[0]; | |
| } | |
| return []; | |
| } | |
| /** | |
| * Insertion of $data in the $table and returns number of insertions. | |
| * For multiple insertions, you must fill in an array of associative arrays with all the same keys. | |
| * | |
| * @example $this->insert('my_table', ['name' => 'foo', 'active' => 1]) | |
| * @example $this->insert('my_table', [['id' => 1, 'name' => 'foo'],['id' => 2, 'name' => 'bar']]) | |
| * | |
| * @param string $table | |
| * @param array $data | |
| * | |
| * @return int | |
| * @throws Exception | |
| */ | |
| public function insert(string $table, array $data) | |
| { | |
| $keys = array_keys($data); | |
| // Bulk insert | |
| $bulk = false; | |
| if (is_numeric($keys[0]) && is_array($data[0])) { | |
| $bulk = true; | |
| $base_keys = array_keys(array_values($data)[0]); | |
| $_keys = implode(', ', array_map(function($v) { return "`{$v}`"; }, $base_keys)); | |
| $sql = "INSERT IGNORE INTO `{$this->prefix}{$table}` ({$_keys}) VALUES "; | |
| $values = []; | |
| $exec_values = []; | |
| foreach ($data as $row) { | |
| $_values = implode(', ', array_map(function($v) { return '?'; }, $row)); | |
| $values[] = "({$_values})"; | |
| $exec_values = array_merge($exec_values, array_values($row)); | |
| } | |
| $sql .= implode(', ', $values); | |
| } | |
| else { | |
| // Regular insert | |
| $_keys = implode(', ', array_map(function($v) { return "`{$v}`"; }, $keys)); | |
| $_values = implode(', ', array_map(function($v) { return ":{$v}"; }, $keys)); | |
| $sql = "INSERT INTO `{$this->prefix}{$table}` ({$_keys}) VALUES ({$_values})"; | |
| } | |
| if ($this->printOnly) { | |
| if ($bulk) { | |
| return $this->printQueryWithPlaceholders($sql, $exec_values); | |
| } | |
| return $this->printQuery($sql, $data); | |
| } | |
| $this->checkIsReadonly('insert'); | |
| $this->resetLastQuery(); | |
| $stmt = $this->dbo->prepare($sql); | |
| $this->setLastQuery($stmt); | |
| try { | |
| $this->dbo->beginTransaction(); | |
| // Bulk insert | |
| if ($bulk) { | |
| $stmt->execute($exec_values); | |
| } | |
| else { | |
| // Regular insert | |
| $this->bindValues($stmt, $data); | |
| $stmt->execute(); | |
| } | |
| $id = $this->dbo->lastInsertId(); | |
| $this->dbo->commit(); | |
| return (int) $id; | |
| } catch(\PDOException $e) { | |
| $this->dbo->rollback(); | |
| $this->catchAndLaunchError($e, $stmt); | |
| } | |
| return 0; | |
| } | |
| /** | |
| * Update $table with $data corresponding to the $conditions and returns number of updates | |
| * | |
| * @example $this->update('my_table', ['name' => 'Test'], ['id_user' => 1]) | |
| * | |
| * @param string $table | |
| * @param array $data | |
| * @param array $conditions | |
| * | |
| * @return int | |
| * @throws Exception | |
| */ | |
| public function update(string $table, array $data, array $conditions) | |
| { | |
| $sql = "UPDATE `{$this->prefix}{$table}` SET "; | |
| foreach ($data as $field => $value) { | |
| $sql .= "`{$field}` = :{$field}, "; | |
| } | |
| $sql = substr($sql, 0, -2); | |
| $sql .= static::createWhere($conditions); | |
| if ($this->printOnly) { | |
| return $this->printQuery($sql, $data, $conditions); | |
| } | |
| $this->checkIsReadonly('update'); | |
| $this->resetLastQuery(); | |
| $stmt = $this->dbo->prepare($sql); | |
| $this->setLastQuery($stmt); | |
| try { | |
| $this->bindValues($stmt, $data); | |
| $this->bindValues($stmt, $conditions); | |
| $this->dbo->beginTransaction(); | |
| $stmt->execute(); | |
| $nb_rows = $stmt->rowCount(); | |
| $this->dbo->commit(); | |
| return (int) $nb_rows; | |
| } catch(\PDOException $e) { | |
| $this->dbo->rollback(); | |
| $this->catchAndLaunchError($e, $stmt); | |
| } | |
| return 0; | |
| } | |
| /** | |
| * Delete from $table corresponding to the $conditions and returns number of deletions | |
| * | |
| * @example $this->delete('my_table', ['id_user' => 1]) | |
| * | |
| * @param string $table | |
| * @param array $conditions | |
| * | |
| * @return int | |
| * @throws Exception | |
| */ | |
| public function delete(string $table, array $conditions) | |
| { | |
| $sql = "DELETE FROM `{$this->prefix}{$table}`"; | |
| $sql .= static::createWhere($conditions); | |
| if ($this->printOnly) { | |
| return $this->printQuery($sql, $conditions); | |
| } | |
| $this->checkIsReadonly('delete'); | |
| $this->resetLastQuery(); | |
| $stmt = $this->dbo->prepare($sql); | |
| $this->setLastQuery($stmt); | |
| try { | |
| $this->bindValues($stmt, $conditions); | |
| $this->dbo->beginTransaction(); | |
| $stmt->execute(); | |
| $nb_rows = $stmt->rowCount(); | |
| $this->dbo->commit(); | |
| return (int) $nb_rows; | |
| } catch(\PDOException $e) { | |
| $this->dbo->rollback(); | |
| $this->catchAndLaunchError($e, $stmt); | |
| } | |
| return 0; | |
| } | |
| /** | |
| * Call the routine $name with parameters $params | |
| * | |
| * @example $this->routine('My_Routine', ['foo', 'bar']) | |
| * | |
| * @param string $name | |
| * @param array $params | |
| * | |
| * @return array | |
| * @throws Exception | |
| */ | |
| public function routine(string $name, array $params = []) | |
| { | |
| $_params = implode(', ', array_fill(0, count($params), '?')); | |
| $query = "CALL `{$name}`({$_params});"; | |
| if ($this->printOnly) { | |
| return $this->printQueryWithPlaceholders($query, $params); | |
| } | |
| $this->checkIsReadonly('routine'); | |
| $this->resetLastQuery(); | |
| $stmt = $this->dbo->prepare($query); | |
| $this->setLastQuery($stmt); | |
| try { | |
| $i = 0; | |
| foreach ($params as $p) { | |
| $stmt->bindValue(++$i, $p, PDO::PARAM_STR); | |
| $this->setLastQueryParams($i, $p); | |
| } | |
| $stmt->execute(); | |
| return $stmt->fetchAll(); | |
| } | |
| catch (Exception $e) { | |
| $this->catchAndLaunchError($e, $stmt); | |
| } | |
| return []; | |
| } | |
| /** | |
| * Truncate a table. | |
| * Warning : you will lose all your data. | |
| * | |
| * @example $this->truncate('my_table') | |
| * | |
| * @param string $table | |
| * | |
| * @return bool | |
| * @throws Exception | |
| */ | |
| public function truncate(string $table) | |
| { | |
| $sql = "TRUNCATE `{$this->prefix}{$table}`"; | |
| if ($this->printOnly) { | |
| return $this->printQuery($sql); | |
| } | |
| $this->checkIsReadonly('truncate'); | |
| $this->resetLastQuery(); | |
| $stmt = $this->dbo->prepare($sql); | |
| $this->setLastQuery($stmt); | |
| try { | |
| $this->dbo->beginTransaction(); | |
| $stmt->execute(); | |
| $this->dbo->commit(); | |
| return true; | |
| } catch(\PDOException $e) { | |
| $this->dbo->rollback(); | |
| $this->catchAndLaunchError($e, $stmt); | |
| } | |
| return false; | |
| } | |
| /** | |
| * Drop a table. | |
| * Warning : you will lose all your data. | |
| * | |
| * @example $this->drop('my_table') | |
| * | |
| * @param string $table | |
| * | |
| * @return bool | |
| * @throws Exception | |
| */ | |
| public function drop(string $table) | |
| { | |
| $sql = "DROP TABLE `{$this->prefix}{$table}`"; | |
| if ($this->printOnly) { | |
| return $this->printQuery($sql); | |
| } | |
| $this->checkIsReadonly('drop'); | |
| $this->resetLastQuery(); | |
| $stmt = $this->dbo->prepare($sql); | |
| $this->setLastQuery($stmt); | |
| try { | |
| $this->dbo->beginTransaction(); | |
| $stmt->execute(); | |
| $this->dbo->commit(); | |
| return true; | |
| } catch(\PDOException $e) { | |
| $this->dbo->rollback(); | |
| $this->catchAndLaunchError($e, $stmt); | |
| } | |
| return false; | |
| } | |
| /** | |
| * Create a table. | |
| * | |
| * @example $this->create('my_table', [ | |
| * 'id_test' => 'INT UNSIGNED NOT NULL AUTO_INCREMENT', | |
| * 'name' => 'VARCHAR(168) NOT NULL', | |
| * 'description' => 'TEXT NULL', | |
| * 'active' => 'TINYINT(1) NOT NULL DEFAULT 1', | |
| * ], [ | |
| * 'PRIMARY KEY (`id_test`)' | |
| * ], 'ENGINE = MyISAM'); | |
| * | |
| * @param string $table | |
| * @param array $columns | |
| * @param array $indexes | |
| * @param string $extra | |
| * | |
| * @return bool | |
| * @throws Exception | |
| */ | |
| public function create(string $table, array $columns, array $indexes = [], string $extra = null) | |
| { | |
| $sql = "CREATE TABLE `{$this->prefix}{$table}` ("; | |
| $cols = []; | |
| foreach ($columns as $name => $attributes) { | |
| $cols[] = "`{$name}` {$attributes}"; | |
| } | |
| $cols = array_merge($cols, $indexes); | |
| $sql .= implode(', ', $cols) . ') ' . $extra; | |
| if ($this->printOnly) { | |
| return $this->printQuery($sql); | |
| } | |
| $this->checkIsReadonly('create'); | |
| $this->resetLastQuery(); | |
| $stmt = $this->dbo->prepare($sql); | |
| $this->setLastQuery($stmt); | |
| try { | |
| $this->dbo->beginTransaction(); | |
| $stmt->execute(); | |
| $this->dbo->commit(); | |
| return true; | |
| } catch(\PDOException $e) { | |
| $this->dbo->rollback(); | |
| $this->catchAndLaunchError($e, $stmt); | |
| } | |
| return false; | |
| } | |
| /** | |
| * Get last PDO error | |
| * | |
| * @return array | |
| */ | |
| public function getLastError() | |
| { | |
| return $this->last_error; | |
| } | |
| /** | |
| * Get last query. | |
| * If $raw_query, query parameters are replaced by their values. | |
| * | |
| * @param bool $raw_query | |
| * | |
| * @return string | |
| */ | |
| public function getLastQuery(bool $raw_query = false) | |
| { | |
| $query = $this->last_query; | |
| if ($raw_query === static::RAW_QUERY) { | |
| return $query; | |
| } | |
| foreach ($this->last_query_params as $field => $value) { | |
| if (is_array($value)) { | |
| continue; | |
| } | |
| $query = str_replace(":{$field}", "'{$value}'", $query); | |
| } | |
| if (strpos($query, '?') !== false) { | |
| } | |
| return $query; | |
| } | |
| /** | |
| * Get parameters of last prepared query | |
| * | |
| * @return array | |
| */ | |
| public function getLastQueryParams() | |
| { | |
| return $this->last_query_params; | |
| } | |
| /** | |
| * Returns an array for debugging with last error, raw query, query with parameters and parameters | |
| * | |
| * @return array | |
| */ | |
| public function debug() | |
| { | |
| return [ | |
| 'error' => $this->getLastError(), | |
| 'raw_query' => $this->getLastQuery(static::RAW_QUERY), | |
| 'query' => $this->getLastQuery(), | |
| 'query_params' => $this->getLastQueryParams(), | |
| ]; | |
| } | |
| /** | |
| * Database constructor | |
| * | |
| * @param array $config | |
| * | |
| * @throws Exception | |
| */ | |
| protected function __construct(array $config) | |
| { | |
| $required = ['host','dbname','username','password']; | |
| foreach ($required as $r) { | |
| if (!isset($config[$r])) { | |
| throw new Exception('Please set required configuration fields : ' . implode(', ', $required)); | |
| } | |
| } | |
| if (!array_key_exists('on_error', $config)) { | |
| $config['on_error'] = static::ON_ERROR_EXIT; | |
| } | |
| $this->config = $config; | |
| $pdoparams = []; | |
| if (isset($config['charset'])) { | |
| $pdoparams[PDO::MYSQL_ATTR_INIT_COMMAND] = "SET NAMES '{$config['charset']}'"; | |
| } | |
| $dsn = "mysql:host={$config['host']};dbname={$config['dbname']}"; | |
| if (isset($config['port'])) { | |
| $dsn .= ";port={$config['port']}"; | |
| } | |
| try { | |
| $this->dbo = new PDO($dsn, $config['username'], $config['password'], $pdoparams); | |
| $this->dbo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); | |
| $this->dbo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); | |
| } | |
| catch(Exception $e) { | |
| exit($e->getMessage().PHP_EOL); | |
| } | |
| static::$_instance = $this; | |
| return $this; | |
| } | |
| /** | |
| * Returns the type of parameter from value for prepared statement | |
| * | |
| * @param mixed $value | |
| * | |
| * @return bool|int | |
| */ | |
| protected static function getParamType($value) | |
| { | |
| if (is_int($value)) { | |
| return \PDO::PARAM_INT; | |
| } | |
| if (is_bool($value)) { | |
| return \PDO::PARAM_BOOL; | |
| } | |
| if (is_null($value)) { | |
| return \PDO::PARAM_NULL; | |
| } | |
| if (is_string($value)) { | |
| return \PDO::PARAM_STR; | |
| } | |
| return false; | |
| } | |
| /** | |
| * Returns an array filled with the field and the operator for a WHERE clause | |
| * | |
| * @param string $value | |
| * | |
| * @return array | |
| * | |
| * @see Database::getAll() | |
| */ | |
| protected static function splitOperator(string $value) | |
| { | |
| $operator = '='; | |
| if (strpos($value, ' ') !== false) { | |
| $tmp = explode(' ', $value); | |
| $value = array_shift($tmp); | |
| $operator = implode(' ', $tmp); | |
| unset($tmp); | |
| } | |
| return [$value, $operator]; | |
| } | |
| /** | |
| * Create the WHERE conditions. | |
| * | |
| * @param array $conditions | |
| * | |
| * @return string | |
| */ | |
| protected static function createWhere(array $conditions = []) | |
| { | |
| if (empty($conditions)) { | |
| return ''; | |
| } | |
| $where = ' WHERE '; | |
| foreach ($conditions as $field => $value) { | |
| $_where = ''; | |
| if (is_array($value)) { | |
| if (!empty($value)) { | |
| list($field, $operator) = static::splitOperator($field); | |
| if ($operator === '=') { | |
| $operator = 'IN'; | |
| } | |
| $in_values = implode(', ', array_map(function($v){ | |
| return is_numeric($v) ? $v : "'{$v}'"; | |
| }, $value)); | |
| $_where = "`{$field}` {$operator} ({$in_values})"; | |
| } | |
| } | |
| else { | |
| if (is_numeric($field)) { | |
| list($value, $operator) = static::splitOperator($value); | |
| $_where = "`{$value}` {$operator}"; | |
| unset($conditions[$field]); | |
| } | |
| else { | |
| list($field, $operator) = static::splitOperator($field); | |
| $_where = "`{$field}` {$operator} :{$field}"; | |
| } | |
| } | |
| if ($_where) { | |
| $where .= "{$_where} AND "; | |
| } | |
| } | |
| $where = substr($where, 0, -5); | |
| return $where; | |
| } | |
| /** | |
| * Bind the values for prepared queries. | |
| * | |
| * @param PDOStatement $stmt | |
| * @param array $conditions | |
| */ | |
| protected function bindValues($stmt, array $conditions = []) | |
| { | |
| if (empty($conditions)) { | |
| return; | |
| } | |
| foreach ($conditions as $field => $value) { | |
| if (!is_array($value)) { | |
| list($field, $operator) = static::splitOperator($field); | |
| $stmt->bindValue(":{$field}", $value, static::getParamType($value)); | |
| $this->setLastQueryParams($field, $value); | |
| } | |
| } | |
| } | |
| /** | |
| * Set last PDO error | |
| * | |
| * @param array $errorInfo | |
| */ | |
| protected function setLastError(array $errorInfo) | |
| { | |
| $this->last_error = $errorInfo; | |
| } | |
| /** | |
| * Reset query debugging | |
| */ | |
| protected function resetLastQuery() | |
| { | |
| $this->last_query = null; | |
| $this->last_query_params = []; | |
| $this->last_error = []; | |
| } | |
| /** | |
| * Set parameters of last query | |
| * | |
| * @param mixed $field | |
| * @param mixed $value | |
| */ | |
| protected function setLastQueryParams($field, $value) | |
| { | |
| $this->last_query_params[$field] = $value; | |
| } | |
| /** | |
| * Set last query and last error | |
| * | |
| * @param PDOStatement $stmt | |
| */ | |
| protected function setLastQuery($stmt) | |
| { | |
| $this->last_query = trim($stmt->queryString); | |
| $this->last_error = $stmt->errorInfo(); | |
| } | |
| /** | |
| * Replace placeholders in query with values in params. | |
| * | |
| * @param string $query | |
| * @param array $params | |
| * | |
| * @return string | |
| */ | |
| protected function printQuery(string $query, ...$params) | |
| { | |
| if (!empty($params)) { | |
| array_map(function($params) use (&$query) { | |
| $this->printQueryWalk($query, $params); | |
| }, $params); | |
| } | |
| return $query; | |
| } | |
| /** | |
| * Update the query with values | |
| * | |
| * @param string $query | |
| * @param array $params | |
| */ | |
| protected function printQueryWalk(string &$query, array $params = []) | |
| { | |
| foreach ($params as $field => $value) { | |
| if (is_array($value)) { | |
| $this->printQueryWalk($query, $value); | |
| } | |
| else { | |
| list($field, $operator) = static::splitOperator($field); | |
| if (is_string($value)) { | |
| $value = $this->dbo->quote($value, static::getParamType($value)); | |
| } | |
| $query = str_replace(":{$field}", $value, $query); | |
| } | |
| } | |
| } | |
| /** | |
| * Similar to the printQuery method but for query with '?' as placeholders. | |
| * | |
| * @param string $query | |
| * @param array $params | |
| * | |
| * @return string | |
| */ | |
| protected function printQueryWithPlaceholders(string $query, array $params) | |
| { | |
| return $this->printQuery(vsprintf(str_replace("?", "%s", $query), array_map(function($v) { | |
| return is_string($v) ? "'{$v}'" : $v; | |
| }, $params))); | |
| } | |
| /** | |
| * Catch error and stop execution | |
| * | |
| * @param PDOException | |
| * @param PDOStatement | |
| * | |
| * @throws Exception | |
| */ | |
| protected function catchAndLaunchError($error, $statement) | |
| { | |
| $this->setLastError($statement->errorInfo()); | |
| $this->launchError($error->getMessage()); | |
| } | |
| /** | |
| * Stop the script execution with a message (exit or throw an exception, as decided for the error handling). | |
| * | |
| * @param string $message | |
| * | |
| * @throws Exception | |
| */ | |
| protected function launchError(string $message) | |
| { | |
| if ($this->config['on_error'] === static::ON_ERROR_EXCEPTION) { | |
| throw new Exception($message); | |
| } | |
| print($message.PHP_EOL); | |
| if ($this->config['on_error'] === static::ON_ERROR_EXIT) { | |
| exit(1); | |
| } | |
| } | |
| /** | |
| * Check if method can be called in readOnly mode | |
| * | |
| * @throws Exception | |
| */ | |
| protected function checkIsReadonly(string $method) | |
| { | |
| if ($this->readonly !== false && in_array($method, static::$readonly_methods)) { | |
| $this->launchError("Error: Method $method is not available (database is set as readonly mode)."); | |
| exit; | |
| } | |
| } | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| require_once 'Database.php'; | |
| /** | |
| * 1.2 : Connect to the database. | |
| * Then use $DB or Database::getInstance() | |
| */ | |
| $DB = Database::connect([ | |
| 'host' => 'localhost', | |
| 'username' => 'root', | |
| 'password' => '', | |
| 'dbname' => 'my_db', | |
| 'port' => 3306, | |
| 'charset' => 'utf8', | |
| ]); | |
| // Use PDO methods | |
| $stmt = $DB->getDBO()->prepare('DROP TABLE IF EXISTS my_table'); | |
| $stmt->execute(); | |
| // 1.4 : Only print next requests without execute them | |
| $DB->printOnly(true); | |
| /* | |
| SELECT COUNT(*) as nb, name | |
| FROM `my_table` | |
| WHERE `active` = 1 AND `id_user` >= 4 AND `name` IS NOT NULL AND `id_client` IN (3, 4) AND `id_client` NOT IN (5, 6) | |
| ORDER BY `id_user`, `active` DESC | |
| LIMIT 10 OFFSET 20 | |
| */ | |
| $res = $DB->getAll('my_table', [ | |
| 'active' => 1, | |
| 'id_user >=' => 4, | |
| 'name IS NOT NULL', | |
| 'id_client' => [3,4], | |
| 'id_client NOT IN' => [5,6], | |
| ], ['id_user', 'active DESC'], [10,20], ['COUNT(*)' => 'nb', 'name']); | |
| // SELECT name FROM `my_table` WHERE `id_user` = 4 LIMIT 1 OFFSET 0 | |
| $res = $DB->getRow('my_table', ['id_user' => 4], 'name'); | |
| // Value | |
| $res = $DB->getValue('active', 'my_table', ['id_user' => 1]); // SELECT active FROM `my_table` WHERE `id_user` = 1 LIMIT 1 OFFSET 0 | |
| $res = $DB->getValue(['id_user', 'active'], 'my_table', ['id_user' => 1]); // SELECT id_user, active FROM `my_table` WHERE `id_user` = 1 LIMIT 1 OFFSET 0 | |
| // Multiple rows | |
| $res = $DB->fetchAll('SELECT * FROM my_table'); // return an array of arrays of datas | |
| $res = $DB->fetchAll('SELECT * FROM my_table WHERE id_user > :id_user', ['id_user' => 1]); // return an array of arrays of datas | |
| // One row | |
| $res = $DB->fetchRow('SELECT * FROM my_table'); // return an array of datas | |
| $res = $DB->fetchRow('SELECT * FROM my_table WHERE id_user = :id_user', ['id_user' => 1]); // return an array of datas | |
| // Insert datas | |
| $res = $DB->insert('my_table', ['name' => 'foo', 'active' => 1]); // INSERT INTO `my_table` (`name`, `active`) VALUES ('Test', '1') | |
| $res = $DB->insert('my_table', [['id' => 1, 'name' => 'foo'], ['id' => 2, 'name' => 'bar']]); // INSERT IGNORE INTO `my_table` (`id`, `name`) VALUES ('1', 'foo'), ('2', 'bar'); | |
| // Update datas | |
| $res = $DB->update('my_table', ['name' => 'foo'], ['id_user' => 1]); // UPDATE `my_table` SET `name` = 'foo' WHERE `id_user` = 1; | |
| // Delete datas | |
| $res = $DB->delete('my_table', ['id_user' => 1]); // DELETE FROM `my_table` WHERE `id_user` = 1 | |
| // 1.1 : Call routine | |
| $res = $DB->routine('my_routine', ['foo', 'bar']); // CALL `my_routine`('foo', 'bar'); | |
| // 1.4 : Count results | |
| $res = $DB->count('my_table', ['active' => 1]); // => int 12 | |
| // 1.5 : Add prefix | |
| $DB->prefix('ps_'); | |
| $res = $DB->insert('my_table', ['name' => 'foo']); // INSERT INTO `ps_my_table` (`name`) VALUES ('Test') | |
| // 1.5 : Readonly mode : will exit (or throw an exception) without execute the request | |
| $DB->readonly(); | |
| $res = $DB->insert('my_table', ['name' => 'foo']); | |
| // 1.6 : Truncate table | |
| $DB->truncate('my_table'); // TRUNCATE my_table | |
| // 1.6 : Drop table | |
| $DB->drop('my_table'); // DROP TABLE IF EXISTS my_table | |
| // 1.6 : Create table | |
| /* | |
| CREATE TABLE `my_table` ( | |
| `id_test` INT UNSIGNED NOT NULL AUTO_INCREMENT, | |
| `name` VARCHAR(168) NOT NULL, | |
| `description` TEXT NULL, | |
| `active` TINYINT(1) NOT NULL DEFAULT 1, | |
| PRIMARY KEY (`id_test`), | |
| ) ENGINE = MyISAM | |
| */ | |
| $DB->create('my_table', [ | |
| 'id_test' => 'INT UNSIGNED NOT NULL AUTO_INCREMENT', | |
| 'name' => 'VARCHAR(168) NOT NULL', | |
| 'description' => 'TEXT NULL', | |
| 'active' => 'TINYINT(1) NOT NULL DEFAULT 1', | |
| ], [ | |
| 'PRIMARY KEY (`id_test`)' | |
| ], 'ENGINE = MyISAM'); | |
| // | |
| // Error handling | |
| // | |
| // Exit with error message (default) | |
| $DB->setConfig('on_error', Database::ON_ERROR_CONTINUE); | |
| // Throw exception | |
| $DB->setConfig('on_error', Database::ON_ERROR_EXCEPTION); | |
| // Continue execution. Useful for debugging but can be dangerous. | |
| // Stop execution with `die` or `exit` instruction just after debug. | |
| $DB->setConfig('on_error', Database::ON_ERROR_CONTINUE); | |
| //var_dump($DB->debug()); | |
| //die; | |
| // | |
| // Debugging | |
| // | |
| // Get last error | |
| var_dump($DB->getLastError()); | |
| /* | |
| array(3) { | |
| [0]=> | |
| string(5) "42S02" | |
| [1]=> | |
| int(1146) | |
| [2]=> | |
| string(45) "Table 'my_db.my_table' does not exist" | |
| } | |
| */ | |
| // Get last query with binded parameters | |
| var_dump($DB->getLastQuery()); | |
| // string(77) "SELECT name FROM `my_table` WHERE 1 AND `id_user` = '4' LIMIT 1 OFFSET 0" | |
| // Get last raw query | |
| var_dump($DB->getLastQuery(Database::RAW_QUERY)); | |
| // string(77) "SELECT name FROM `my_table` WHERE 1 AND `id_user` = :id_user LIMIT 1 OFFSET 0" | |
| // Get last query parameters | |
| var_dump($DB->getLastQueryParams()); | |
| /* | |
| array(1) { | |
| ["id_user"]=> | |
| int(4) | |
| } | |
| */ | |
| // Alias to debug all previous debug methods | |
| var_dump($DB->debug()); | |
| /* | |
| array(4) { | |
| ["error"]=> | |
| array(3) { | |
| [0]=> | |
| string(5) "42S02" | |
| [1]=> | |
| int(1146) | |
| [2]=> | |
| string(40) "Table 'my_db.my_table' doesn't exist" | |
| } | |
| ["raw_query"]=> | |
| string(77) "SELECT name FROM `my_table` WHERE 1 AND `id_user` = :id_user LIMIT 1 OFFSET 0" | |
| ["query"]=> | |
| string(72) "SELECT name FROM `my_table` WHERE 1 AND `id_user` = '4' LIMIT 1 OFFSET 0" | |
| ["query_params"]=> | |
| array(1) { | |
| ["id_user"]=> | |
| int(4) | |
| } | |
| } | |
| */ | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment