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: 4 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;
    }
}

For more information send a message to info at phpclasses dot org.