PHP Classes

File: inc/classes/Libs/Phptricks_Database/Database.php

Recommend this page to a friend!
  Classes of mohammad anzawi   PHP Multilingual Support Library   inc/classes/Libs/Phptricks_Database/Database.php   Download  
File: inc/classes/Libs/Phptricks_Database/Database.php
Role: Class source
Content type: text/plain
Description: Class source
Class: PHP Multilingual Support Library
Translate texts for Web sites from JSON or DB
Author: By
Last change:
Date: 7 years ago
Size: 26,550 bytes
 

Contents

Class file image Download
<?php namespace PHPtricks\Logaty\Libs\Phptricks_Database; include __DIR__ . "/config_function.php"; class Database { /** * @var $_instance object * store DB class object to allow one connection with database (deny duplicate) * @access private */ private static $_instance; /** * @var $_pdo object PDO object * @var $_query string store sql statement * @var $_results array store sql statement result * @var $_count int store row count for _results variable * @var $_error bool if cant fetch sql statement = true otherwise = false */ private $_pdo, $_query = '', $_results, $_count, $_error = false, $_schema, $_where = "WHERE", $_sql, $_colsCount = -1; protected $_table, $_idColumn = "id"; /** * DB::__construct() * Connect with database * @access private * @return void */ protected function __construct() { call_user_func_array([$this, \DBConfig()], [null]); } protected function mysql($null) { try { $this->_pdo = new \PDO("mysql:host=" . \DBConfig('host_name') . ";dbname=" . DBConfig('db_name'), \DBConfig('db_user'), \DBConfig('db_password')); $this->_pdo->exec("set names " . 'utf8'); $this->_pdo->setAttribute( \PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION ); } catch(\PDOException $e) { die($e->getMessage()); } } protected function sqlite($null) { try { $this->_pdo = new \PDO("sqlite:" . \DBConfig('db_path')); $this->_pdo->exec("set names " . 'utf8'); $this->_pdo->setAttribute( \PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION ); } catch(\PDOException $e) { die($e->getMessage()); } } protected function pgsql($null) { try { $this->_pdo = new \PDO('pgsql:user='. \DBConfig('db_user') .' dbname=' . \DBConfig('db_name') . ' password='.\DBConfig('db_password')); $this->_pdo->exec("set names " . 'utf8'); $this->_pdo->setAttribute( \PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION ); } catch(\PDOException $e) { die($e->getMessage()); } } protected function mssql($null) { try { $this->_pdo = new \PDO("mssql:host=" . \DBConfig('host_name') . ";dbname=" . \DBConfig('db_name'), \DBConfig('db_user'), \DBConfig('db_password')); $this->_pdo->exec("set names " . 'utf8'); $this->_pdo->setAttribute( \PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION ); } catch(\PDOException $e) { die($e->getMessage()); } } protected function sybase($null) { try { $this->_pdo = new \PDO("sybase:host=" . \DBConfig('host_name') . ";dbname=" . \DBConfig('db_name'), \DBConfig('db_user'), \DBConfig('db_password')); $this->_pdo->exec("set names " . 'utf8'); $this->_pdo->setAttribute( \PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION ); } catch(\PDOException $e) { die($e->getMessage()); } } protected function oci($null) { try{ $conn = new \PDO("oci:dbname=".\DBConfig('tns'), \DBConfig('db_user'), \DBConfig('db_password')); $this->_pdo->exec("set names " . 'utf8'); $this->_pdo->setAttribute( \PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION ); }catch(\PDOException $e){ die ($e->getMessage()); } } /** * DB::connect() * return instace * @return object */ public static function connect() { if(!isset(self::$_instance)) { self::$_instance = new Database(); } return self::$_instance; } /** * DB::query() * check if sql statement is prepare * append value for sql statement if $params is set * fetch results * @param string $sql * @param array $params * @return mixed */ public function query($sql, $params = []) { $this->_query = ""; $this->_where = "WHERE"; // set _error. true to that if they can not be false for this function to work properly, this function makes the // value of _error false if there is no implementation of the sentence correctly $this->_error = false; // check if sql statement is prepared $query = $this->_pdo->prepare($sql); // if $params isset if(count($params)) { /** * @var $x int * counter */ $x = 1; foreach($params as $param) { // append values to sql statement $query->bindValue($x, $param); $x++; } } // check if sql statement executed if($query->execute()) { $this->_sql = $query; // set _results = data comes try { $this->_results = $query->fetchAll(\DBConfig('fetch')); } catch(\PDOException $e){} // set _count = count rows comes $this->_count = $query->rowCount(); } else { // set _error = true if sql statement not executed $this->_error = true; } return $this; } /** * DB::insert() * insert into database tables * @param string $table * @param array $values * @return bool */ public function insert($values = []) { // check if $values set if(count($values)) { /** * @var $fields type array * store fields user want insert value for them */ $fields = array_keys($values); /** * @var $value type string * store value for fields user want inserted */ $value = ''; /** * @var $x type int * counter */ $x = 1; foreach($values as $field) { // add new value $value .="?"; if($x < count($values)) { // add comma between values $value .= ", "; } $x++; } // generate sql statement $sql = "INSERT INTO {$this->_table} (`" . implode('`,`', $fields) ."`)"; $sql .= " VALUES({$value})"; // check if query is not have an error if(!$this->query($sql, $values)->error()) { return true; } } return false; } /** * DB::update() * * @param string $table * @param array $values * @param array $where * @return bool */ public function update($values = []) { /** * @var $set type string * store update value * @example "colomn = value" */ $set = ''; // initialize $set $x = 1; // initialize feilds and values foreach($values as $i => $row) { $set .= "{$i} = ?"; // add comma between values if($x < count($values)) { $set .= " ,"; } $x++; } // generate sql statement $sql = "UPDATE {$this->_table} SET {$set} " . $this->_query; // check if query is not have an error if(!$this->query($sql, $values)->error()) { return true; } return false; } /** * select from database * @param array $fields fields we need to select * @return array result of select */ public function select($fields = ['*']) { $sql = "SELECT " . implode(', ', $fields) . " FROM {$this->_table} {$this->_query}"; $this->_query = $sql; return $this->query($sql)->results(); } /** * delete from table * @return bool */ public function delete() { $sql = "DELETE FROM $this->_table " . $this->_query; $delete = $this->query($sql); if($delete) return true; $this->_error = true; return false; } /** * find single row from table via id * @param int $id [description] * @return array or object (as you choice from config file) results or empty */ public function find($id) { $find = $this->where($this->_idColumn, $id) ->select(); $this->_query = ''; $this->_where = "WHERE"; return isset($find[0]) ? $find[0] : []; } /** * add where condition to sql statement * @param string $field field name from table * @param string $operator operator (= , <>, .. etc) * @param mix $value the value * @return object this class */ public function where($field, $operator, $value = false) { /** * if $value is not set then set $operator to (=) and * $value to $operator */ if($value === false) { $value = $operator; $operator = "="; } if(!is_numeric($value)) $value = "'$value'"; $this->_query .= " $this->_where $field $operator $value"; $this->_where = "AND"; return $this; } /** * between condition * @param string $field table field name * @param arrya $values ['from', 'to'] * @return object this class */ public function whereBetween($field, $values = []) { if(count($values)) { $this->_query .= " $this->_where $field BETWEEN '$values[0]' and '$values[1]'"; $this->_where = "AND"; } return $this; } /** * Like whare * @param string $field database field name * @param string $value value * @return object this class */ /** * we can do that with where() methode * $db->table('test')->where('name', 'LIKE', '%moha%'); */ public function likeWhere($field, $value) { $this->_query .= " $this->_where $field LIKE '%$value%'"; $this->_where = "AND"; return $this; } /** * add OR condition to sql statement * @param string $field field name from table * @param string $operator operator (= , <>, .. etc) * @param mix $value the value * @return object this class */ public function orWhere($field, $operator, $value = false) { /** * if $value is not set then set $operator to (=) and * $value to $operator */ if($value === false) { $value = $operator; $operator = "="; } $this->_query .= " OR $field $operator '$value'"; $this->_where = "AND"; return $this; } /** * add in condition to query * @param string $field field name from table * @param array $value the values * @return object this class */ public function in($field, $values = []) { if(count($values)) { $this->_query .= " $this->_where $field IN (" . implode(",", $values) . ")"; $this->_where = "AND"; } return $this; } /** * add not in condition to query * @param string $field field name from table * @param array $value the values * @return object this class */ public function notIn($field, $values = []) { if(count($values)) { $this->_query .= " $this->_where $field NOT IN (" . implode(",", $values) . ")"; $this->_where = "AND"; } return $this; } /** * get first row from query results * @return array */ public function first($selectNew = true) { if($selectNew === true) $first = $this->select(); else $first = $this->results(); if(count($first)) return $first[0]; return []; } /** * add limit rows to query * @param int $from * @param int $to * @return $this */ public function limit($from = 0, $to = 15) { if(is_integer($from) && is_integer($to)) $this->_query .= " LIMIT {$from}, {$to}"; return $this; } /** * @param $offset * @return $this */ public function offset($offset) { $this->_query .=" OFFSET " .$offset; return $this; } /** * DB::error() * return _error variable * @return bool */ public function error() { return $this->_error; } /** * set _table var value * @param string $table the table name * @return object - DBContent */ public function table($table) { $this->_table = $table; return $this; } public function results() { return $this->_results; } /** * Show last query * @return string */ public function showMeQuery() { return $this->_sql; } /** * * New In V.2.1.0 * */ /** * @sense v.2.1.0 * pagination functionality * @param int $recordsCount count records per page * @return array */ /** * How to Use: * * $db = PHPtricks\Database\Database::connect(); * $results = $db->table("blog")->paginate(15); * * var_dump($results); * * now add to url this string query (?page=2 or 3 or 4 .. etc) * see (link() method to know how to generate navigation automatically) */ public function paginate($recordsCount = 0) { if($recordsCount === 0) $recordsCount = DBConfig("pagination.records_per_page"); // this method accept one argument must be an integer number . if(!is_integer($recordsCount)) { trigger_error("Oops, the records count must be an integer number" . "<br> <p><strong>paginate method</strong> accept one argument must be" ." an <strong>Integer Number</strong> ," . gettype($recordsCount) . " given!</p>" . "<br><pre>any question? contact me on team@phptricks.org</pre>", E_USER_ERROR); } // check current page $startFrom = isset($_GET[DBConfig("pagination.link_query_key")]) ? ($_GET[DBConfig("pagination.link_query_key")] - 1) * $recordsCount : 0; // get pages count rounded up to the next highest integer $this->_colsCount = ceil(count($this->select()) / $recordsCount); // return query results return $this->limit($startFrom, $recordsCount)->select(); } /** * view query results in table * we need to create a simple table to view results of query * @return string (html) */ /** * How to Use: * * $db = PHPtricks\Database\Database::connect(); * $db->table("blog")->where("vote", ">", 2)->select(); * echo $db->dataView(); */ public function dataView() { // get columns count to create the table $colsCount = count($this->first(false)); // if no data received so return no data found! if($colsCount <= 0) { return DBConfig("pagination.no_data_found_message"); } // get Columns name's $colsName = array_keys((array)$this->first(false)); // init html <table> tag $html = "<table border=1><thead><tr>"; /** * create table header * its contain table columns names */ foreach ($colsName as $colName) { $html .= "<th>"; $html .= $colName; $html .= "</th>"; } // end table header tag and open table body tag $html .= "</tr></thead><tbody>"; // loop all results to create the table (tr's and td's) foreach ((array)$this->results() as $row) { $row = (array)$row; // make sure the $row is array and not an object $html .= "<tr>"; // open tr tag // loop all columns in row to create <td>'s tags for ($i = 0; $i <= $colsCount + 1; $i++) { $html .= "<td>"; $html .= $row[$colsName[$i]]; // get current data from the row $html .= "</td>"; } $html .= "</tr>"; } $html .= "</tbody></table>"; return $html; // return created table } /** * create pagination list to navigate between pages * @return string (html) */ /** * How to Use: * * $db = PHPtricks\Database\Database::connect(); * $db->table("blog")->where("vote", ">", 2)->paginate(5); * echo $db->link(); */ public function link() { // get current url $link = $_SERVER['PHP_SELF']; // current page $currentPage = (isset($_GET[DBConfig("pagination.link_query_key")]) ? $_GET[DBConfig("pagination.link_query_key")] : 1); /** * $html var to store <ul> tag */ $html = ''; if($this->_colsCount > 0) // check if columns count is not 0 or less { $operator = $this->checkAndGetUriQuery(); $html = "<ul class=\"pagination\">"; // loop to get all pages for ($i = 1; $i <= $this->_colsCount; $i++) { // we need other pages link only .. if($i == $currentPage) { $html .= "<li>{$i}</li>"; } else { $html .= "<li><a href=\"{$link}{$operator}" . DBConfig("pagination.link_query_key") . "={$i}\">{$i}</a></li>"; } } $html .= "</ul>"; } return $html; } /** * check if we have a string query in current uri other (pagination key) * if not so return (?) otherwise we want to reorder a string query to keep other keys * @return string */ private function checkAndGetUriQuery() { $get = $_GET; // remove pagination key from query string unset($get[DBConfig("pagination.link_query_key")]); // init query string and set init value (?) $queryString = "?"; // check if we have other pagination key in query string if(count($get)) { // reorder query string to keep other keys foreach ($get as $key => $value) { $queryString .= "{$key}" . (!empty($value) ? "=" : "") . $value . "&"; } return $queryString; } return "?"; } /** * @return int pages count when use paginate() method */ public function pagesCount() { if($this->_colsCount < 0) return null; return $this->_colsCount; } /** * get count of rows for last select query * @return int */ public function count() { return $this->_count; } /** * Join's */ /** * make join between tables * @param string $table * @param array $condition * @param string $join * @return $this */ /** * How to use : * $db = PHPtricks\Database\Database::connect(); * $db->table("blog")->join("comments", ["comments.id", "=", blog.id], "left"); * * sql = SELECT * FROM blog LEFT JOIN comments ON comments.id = blog.id */ public function join($table, $condition = [], $join = '') { // make sure the $condition has 3 indexes (`table_one.field`, operator, `table_tow.field`) if(count($condition) == 3) $this->_query .= strtoupper($join) . // convert $join to upper case (left -> LEFT) " JOIN {$table} ON {$condition[0]} {$condition[1]} {$condition[2]}"; // that's it now return object from this class return $this; } /** * check if table is exist in database * @param string $table * @return bool */ public function tableExist($table = '') { $table = $this->query("SHOW TABLES LIKE '{$table}'")->results(); if(!is_null($table) && count($table)) return true; return false; } /** * End Added in V.2.1.0 */ // create table // alter table [ // add column // remove column // rename column // ] // delete table // /* table('table')->schema([ 'column_name' => 'type', 'column_name' => 'type|constraint', 'column_name' => 'type|constraint,more_constraint,other_constraint', ])->create(); */ /* 'id' => 'increments' mean -> this field is primary key, auto increment not null, and unsigned */ /** * set _schema var value * @param array $structures the structer od table * @return object retrun DB object */ public function schema($structures = []) { if(count($structures)) // check if isset $structers { /** * to store columns structers * @var array */ $schema = []; foreach($structures as $column => $options) { $type = $options; // the type is the prototype of column $constraints = ''; // store all constraints for one column // check if we have a constraints if(!strpos($options, '|') === false) { $constraints = explode('|', $options); // the separator to constraints is --> | <-- $type = $constraints[0]; // the type is first key unset($constraints[0]); // remove type from constraints $constraints = implode(' ', $constraints); // convert constraints to string $constraints = strtr($constraints, [ 'primary' => 'PRIMARY KEY', // change (primary to PRIMARY KEY -> its valid constraint in sql) 'increment' => 'AUTO_INCREMENT', // same primary 'not_null' => 'NOT NULL', // same primary ]); } // check if type is 'increments' we want to change it to integer and add some constraints like primary key ,not null, unsigned and auto increment ($type == 'increments'? $type = "INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL": null); // check if type of column is string change it to valid sql type (VARCHAR and set length) // ['username' => 'string:255'] convert to username VARCHAR(255) if(strpos($type, 'string') !== false) { $type = explode(':', $type); $type = "VARCHAR({$type[1]})"; } // check if column has a default value // ['username' => 'string:255|default:no-name'] convert to username VARCHAR(255) DEFAULT 'no name' if(strpos($constraints, 'default') !== false) { preg_match("/(:)[A-Za-z0-9](.*)+/", $constraints, $match); $match[0] = str_replace(':', '', $match[0]); $temp = str_replace('-', ' ', $match[0]); $constraints = str_replace(":" . $match[0] , " '{$temp}' ", $constraints); } // add key to schema var contains column _type constraints // ex: username VARCHAR(255) DEFUALT 'no name' NOT NULL $schema[] = "$column $type " . $constraints; } // set _schema the all columns structure $this->_schema = '(' . implode(",", $schema) . ')'; return $this; // return DB object } return null; // return null } /** * this method to run sql statement and create table * @param string $createStatement its create statement -> i mean you can change it to -> CREATE :table IF NOT EXIST * @return bool */ public function create($createStatement = "CREATE TABLE") // you can use (CREATE TABLE IF NOT EXIST) { $createStatement = $createStatement . " :table "; // check if table is not exist // by default in (try catch) block we can detect this problem // but if you want to display a custom error message you can uncomment // this (if) block and set your error message /*if($this->tableExist($this->_table)) { print ("Oops.. the table {$this->_table} already Exists in " . DBConfig('host_name') . "/" . DBConfig("db_name")); die; }*/ $createStatement = str_replace(':table', $this->_table, $createStatement); try { $this->_pdo->exec($createStatement . $this->_schema); } catch(\PDOException $e) { print $e->getMessage(); return false; } return true; } public function drop() { try { $this->_pdo->exec("DROP TABLE {$this->_table}"); } catch(\PDOException $e) { die($e->getMessage()); } return true; } // "ALTER TABLE ADD COLUMN (COLUMN_NAME TYPE AND CONSTRAINT)" // "ALTER TABLE DROP COLUMN COLUMN_NAME" // "ALTER TABLE RENAME COLUMN (COLUMN_NAME TYPE AND CONSTRAINT)" // // table('table')->alterSchema(['add', 'column_name', 'type'])->alter(); // table('table')->alterSchema(['drop', 'column_name'])->alter(); // table('table')->alterSchema(['rename', 'column_name','new_name' ,'type'])->alter(); // table('table')->alterSchema(['modify', 'column_name', 'new_type'])->alter(); public function alterSchema($schema = []) { if(count($schema)) { $function = $schema[0]."Column"; unset($schema[0]); call_user_func_array([$this, $function], [$schema]); return $this; } return null; } public function alter() { // check if table is not exist // by default in (try catch) block we can detect this problem // but if you want to display a custom error message you can uncomment // this (if) block and set your error message /*if(!$this->tableExist($this->_table)) { print ("Oops.. cant alter table {$this->_table} because is not Exists in " . DBConfig('host_name') . "/" . DBConfig("db_name")); die; }*/ try { $this->_pdo->exec("ALTER TABLE {$this->_table} {$this->_schema}"); } catch(\PDOException $e) { die($e->getMessage()); } } public function addColumn($options = []) { if(count($options) === 2) $this->_schema = "ADD COLUMN {$options[1]} {$options[2]}"; } public function dropColumn($options = []) { if(count($options) === 1) $this->_schema = "DROP COLUMN {$options[1]}"; } public function renameColumn($options = []) { if(count($options) === 3) $this->_schema = "CHANGE {$options[1]} {$options[2]} {$options[3]}"; } public function typeColumn($options = []) { if(count($options) === 2) $this->_schema = "MODIFY {$options[1]} {$options[2]}"; } public function showMeSchema() { return $this->_schema; } }