Database::insert() - Code Metrics - Inspection of "Fix testing" - AdamB7586/pdo-dbal - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( d9b6b7...ce8fae )

by Adam

created 2017-08-16 16:09 UTC

Database::insert() A

↳ Parent: Database

Complexity

Conditions	2
Paths	2

Size

Total Lines	7
Code Lines	5

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	6

Importance

Changes

Metric	Value
c	0
b	0
f	0
dl	0
loc	7
ccs	0
cts	5
cp	0
rs	9.4285
cc	2
eloc	5
nc	2
nop	2
crap	6

<?php
namespace DBAL;

use PDO;

/**
 * PDO Database connection class
 *
 * @author Adam Binnersley <[email protected]>
 * @version PDO Database Class
 */
final class Database implements DBInterface{
    protected $db;
    protected $sql;
    private $key;
    
    protected $logLocation = 'logs'.DIRECTORY_SEPARATOR;
    public $logErrors = true;
    public $logQueries = false;
    public $displayErrors = false;
    
    protected $database;
    protected $cacheEnabled = false;
    protected $cacheObj;
    protected $cacheValue;
    protected $modified = false;

    private $query;
    private $values = array();
    private $prepare = array();

    /**
     * Connect to database using PDO connection
     * @param string $hostname This should be the host of the database e.g. 'localhost'
     * @param string $username This should be the username for the chosen database
     * @param string $password This should be the password for the chosen database 
     * @param string $database This should be the database that you wish to connect to
     * @param string|null $backuphost If you have a replication server set up put the hostname or IP address incase the primary server goes down
     */
    public function __construct($hostname, $username, $password, $database, $backuphost = NULL){
        try{
            $this->connectToServer($username, $password, $database, $hostname);
        }
        catch(\Exception $e){
            if($backuphost !== NULL){
                $this->connectToServer($username, $password, $database, $backuphost);
            }
            $this->error($e);
        }
    }
    
    /**
     * Closes the PDO database connection when Database object unset
     */
    public function __destruct(){
        $this->closeDatabase();
    }
    
    /**
     * Connect to the database using PDO connection
     * @param string $username This should be the username for the chosen database
     * @param string $password This should be the password for the chosen database 
     * @param string $database This should be the database that you wish to connect to
     * @param string $hostname The hostname for the database
     */
    protected function connectToServer($username, $password, $database, $hostname){
        if(!$this->db){
            $this->database = $database;
            $this->db = new PDO('mysql:host='.$hostname.';dbname='.$database, $username, $password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true, PDO::ATTR_PERSISTENT => true, PDO::ATTR_EMULATE_PREPARES => true));
        }
    }
    
    /**
     * Enables the caching and set the caching object to the one provided
     * @param object $caching This should be class of the type of caching you are using
     */
    public function setCaching($caching){
        if(is_object($caching)){
            $this->cacheObj = $caching;
            $this->cacheEnabled = true;
        }
        return $this;
    }
    
    /**
     * This query function is used for more advanced SQL queries for which non of the other methods fit
     * @param string $sql This should be the SQL query which you wish to run
     * @return array Returns array of results for the query that has just been run
     */
    public function query($sql, $variables = array(), $cache = true){
        try{
            $this->sql = $sql;
            $this->query = $this->db->prepare($this->sql);
            $this->query->execute($variables);
            if(strpos($this->sql, 'SELECT') !== false){
                return $this->query->fetchAll(PDO::FETCH_ASSOC);
            }
        }
        catch(\Exception $e){
            $this->error($e);
        }
    }
    
    /**
     * Returns a single record for a select query for the chosen table
     * @param string $table This should be the table you wish to select the values from
     * @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
     * @param string|array $fields This should be the records you wis to select from the table. It should be either set as '*' which is the default or set as an array in the following format array('field', 'field2', 'field3', etc).
     * @param array $order This is the order you wish the results to be ordered in should be formatted as follows array('fieldname' => 'ASC') or array("'fieldname', 'fieldname2'" => 'DESC')
     * @param boolean $cache If the query should be cached or loaded from cache set to true else set to false
     * @return array Returns a single table record as the standard array when running SQL queries
     */
    public function select($table, $where = array(), $fields = '*', $order = array(), $cache = true){
        return $this->selectAll($table, $where, $fields, $order, 1, $cache);
    }
    
    /**
     * Returns a multidimensional array of the results from the selected table given the given parameters
     * @param string $table This should be the table you wish to select the values from
     * @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
     * @param string|array $fields This should be the records you wis to select from the table. It should be either set as '*' which is the default or set as an array in the following format array('field', 'field2', 'field3', etc).
     * @param array $order This is the order you wish the results to be ordered in should be formatted as follows array('fieldname' => 'ASC') or array("'fieldname', 'fieldname2'" => 'DESC')
     * @param integer|array $limit The number of results you want to return 0 is default and returns all results, else should be formated either as a standard integer or as an array as the start and end values e.g. array(0 => 150)
     * @param boolean $cache If the query should be cached or loaded from cache set to true else set to false
     * @return array Returns a multidimensional array with the chosen fields from the table
     */
    public function selectAll($table, $where = array(), $fields = '*', $order = array(), $limit = 0, $cache = true){        
        $this->buildSelectQuery($table, $where, $fields, $order, $limit);
        $result = $this->executeQuery($cache);
        if(!$result){
            if($limit === 1){$result = $this->query->fetch(PDO::FETCH_ASSOC);} // Reduce the memory usage if only one record and increase performance
            else{$result = $this->query->fetchAll(PDO::FETCH_ASSOC);}
            if($cache && $this->cacheEnabled){$this->setCache($this->key, $result);}
        }
        return $result;
    }
    
    /**
     * Returns a single column value for a given query
     * @param string $table This should be the table you wish to select the values from
     * @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
     * @param array $fields This should be the records you wis to select from the table. It should be either set as '*' which is the default or set as an array in the following format array('field', 'field2', 'field3', etc).
     * @param int $colNum This should be the column number you wish to get (starts at 0)
     * @param array $order This is the order you wish the results to be ordered in should be formatted as follows array('fieldname' => 'ASC') or array("'fieldname', 'fieldname2'" => 'DESC') so it can be done in both directions
     * @param boolean $cache If the query should be cached or loaded from cache set to true else set to false
     * @return mixed If a result is found will return the value of the colum given else will return false
     */
    public function fetchColumn($table, $where = array(), $fields = '*', $colNum = 0, $order = array(), $cache = true){
        $this->buildSelectQuery($table, $where, $fields, $order, 1);
        $result = $this->executeQuery($cache);
        if(!$result){
            $result = $this->query->fetchColumn(intval($colNum));
            if($cache && $this->cacheEnabled){$this->setCache($this->key, $result);}
        }
        return $result;
    }
    
    /**
     * Inserts into database using the prepared PDO statements 
     * @param string $table This should be the table you wish to insert the values into
     * @param array $records This should be the field names and values in the format of array('fieldname' => 'value', 'fieldname2' => 'value2', etc.)
     * @return boolean If data is inserted returns true else returns false
     */
    public function insert($table, $records){
        unset($this->prepare);
        
        $this->sql = sprintf("INSERT INTO `%s` (%s) VALUES (%s);", $table, $this->fields($records, true), implode(', ', $this->prepare));
        $this->executeQuery(false);
        return $this->numRows() ? true : false;
    }
    
    /**
     * Updates values in a database using the provide variables
     * @param string $table This should be the table you wish to update the values for
     * @param array $records This should be the field names and new values in the format of array('fieldname' => 'newvalue', 'fieldname2' => 'newvalue2', etc.)
     * @param array $where Should be the field names and values you wish to update in the form of an array e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
     * @param int $limit The number of results you want to return 0 is default and will update all results that match the query, else should be formated as a standard integer
     * @return boolean Returns true if update is successful else returns false
     */
    public function update($table, $records, $where = array(), $limit = 0){
        $this->sql = sprintf("UPDATE `%s` SET %s %s%s;", $table, $this->fields($records), $this->where($where), $this->limit($limit));
        $this->executeQuery(false);
        return $this->numRows() ? true : false;
    }
    
    /**
     * Deletes records from the given table based on the variables given
     * @param string $table This should be the table you wish to delete the records from
     * @param array $where This should be an array of for the where statement
     * @param int $limit The number of results you want to return 0 is default and will delete all results that match the query, else should be formated as a standard integer
     */
    public function delete($table, $where, $limit = 0){
        $this->sql = sprintf("DELETE FROM `%s` %s%s;", $table, $this->where($where), $this->limit($limit));
        $this->executeQuery(false);
        return $this->numRows() ? true : false;
    }
    
    /**
     * Count the number of return results 
     * @param string $table The table you wish to count the result of 
     * @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
     * @param boolean $cache If the query should be cached or loaded from cache set to true else set to false
     * @return int Returns the number of results
     */
    public function count($table, $where = array(), $cache = true){
        $this->sql = sprintf("SELECT count(*) FROM `%s`%s;", $table, $this->where($where));
        $this->key = md5($this->database.$this->sql.serialize($this->values));
        
        $result = $this->executeQuery($cache);
        if(!$result){
            $result = $this->query->fetchColumn();
            if($cache && $this->cacheEnabled){$this->setCache($this->key, $result);}
        }
        return $result;
    }
    
    /**
     * Truncates a given table from the selected database so there are no values in the table
     * @param string $table This should be the table you wish to truncate
     * @return boolean If the table is emptied returns true else returns false
     */
    public function truncate($table){
        try{
            $this->sql = sprintf("TRUNCATE TABLE `%s`", $table);
            $this->query = $this->db->exec($this->sql);
        }
        catch(\Exception $e){
            $this->error($e);
        }
        return $this->query ? true : false;
    }
    
    /**
     * Returns the number of rows for the last query sent
     * @return int Returns the number of rows for the last query
     */
    public function numRows(){
        if(isset($this->query)){
            return $this->query->rowCount();
        }
        return 0;
    }
    
    /**
     * Returns the number of rows for the last query sent (Looks a the numRows() function just added incase of habbit)
     * @return int Returns the number of rows for the last query
     */
    public function rowCount(){
        return $this->numRows();
    }
    
    /**
     * Returns the ID of the last record last inserted 
     * @param string $name This should be the name of the sequence object you wish to retrieve
     * @return int|string Returns the last inserted ID of the last insert item if $name is null else returns string with sequenced object
     */
    public function lastInsertId($name = null) {
        return $this->db->lastInsertId($name);
    }
    
    /**
     * Returns the index of the given table or tables within the database
     * @param string|array $table Table can wither be a standard string with a single table name or an array with multiple table names
     * @return array Returns the table index for the selected table as an array 
     */
    public function fulltextIndex($table){
        $fieldlist = array();
        if(is_array($table)){
            foreach($table as $name){
                $fieldlist[$name] = $this->fulltextIndex($name);
            }
        }else{
            try{
                $this->sql = sprintf("SHOW INDEX FROM %s;",  $table);
                $this->query = $this->db->exec($this->sql);
            }
            catch(\Exception $e){
                $this->error($e);
            }
            
            while($index = $this->query->fetch(PDO::FETCH_ASSOC)){
                if($index['Index_type'] == 'FULLTEXT' && $index['Key_name'] == 'fulltext'){
                    $fieldlist[] = $index['Column_name'];
                }
            }
        }
        return $fieldlist;
    }
    
    /**
     * Returns the server version information
     */
    public function serverVersion(){
        return $this->db->getAttribute(PDO::ATTR_SERVER_VERSION);
    }
    
    /**
     * Displays the error massage which occurs
     * @param \Exception $error This should be an instance of Exception
     */
    private function error($error){
        if($this->logErrors){
            $file = $this->logLocation.'db-errors.txt';
            $current = file_get_contents($file);
            $current .= date('d/m/Y H:i:s')." ERROR: ".$error->getMessage()." on ".$this->sql."\n";
            file_put_contents($file, $current);
        }
        if($this->displayErrors){
            die('ERROR: '.$error->getMessage().' on '.$this->sql);
        }
    }
    
    /**
     * Writes all queries to a log file
     */
    public function writeQueryToLog(){
        $file = $this->logLocation.'queries.txt';
        $current = file_get_contents($file);
        $current .= "SQL: ".$this->sql.":".serialize($this->values)."\n";
        file_put_contents($file, $current);
    }
    
    /**
     * Closes the PDO database connection by setting the connection to NULL 
     */
    public function closeDatabase(){
        $this->db = null;
    }
    
    /**
     * Build the SQL query but doesn't execute it
     * @param string $table This should be the table you wish to select the values from
     * @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
     * @param string|array $fields This should be the records you wis to select from the table. It should be either set as '*' which is the default or set as an array in the following format array('field', 'field2', 'field3', etc).
     * @param array $order This is the order you wish the results to be ordered in should be formatted as follows array('fieldname' => 'ASC') or array("'fieldname', 'fieldname2'" => 'DESC') so it can be done in both directions
     * @param integer|array $limit The number of results you want to return 0 is default and returns all results, else should be formated either as a standard integer or as an array as the start and end values e.g. array(0 => 150)
     */
    protected function buildSelectQuery($table, $where = array(), $fields = '*', $order = array(), $limit = 0){
        if(is_array($fields)){
            $selectfields = array();
            foreach($fields as $field => $value){
                $selectfields[] = sprintf("`%s`", $value);
            }
            $fieldList = implode(', ', $selectfields);
        }
        else{$fieldList = '*';}
        
        $this->sql = sprintf("SELECT %s FROM `%s`%s%s%s;", $fieldList, $table, $this->where($where), $this->orderBy($order), $this->limit($limit));
        $this->key = md5($this->database.$this->sql.serialize($this->values));
    }
    
    /**
     * Execute the current query if no cache value is available
     * @param boolean $cache If the cache should be checked for the checked for the values of the query set to true else set to false 
     * @return mixed If a cached value exists will be returned else if cache is not checked and query is executed will not return anything
     */
    protected function executeQuery($cache = true){
        if($this->logQueries){$this->writeQueryToLog();}
        if($cache && $this->cacheEnabled && $this->getCache($this->key)){
            return $this->cacheValue;
        }
        try{
            $this->query = $this->db->prepare($this->sql);
            $this->query->execute($this->values);
            unset($this->values);
        }
        catch(\Exception $e){
            $this->error($e);
        }
}
	
    /**
     * This outputs the SQL where query based on a given array
     * @param array $where This should be an array that you wish to create the where query for in the for array('field1' => 'test') or array('field1' => array('>=', 0))
     * @return string|false If the where query is an array will return the where string and set the values else returns false if no array sent
     */
    private function where($where){
        if(is_array($where)){
            $wherefields = array();
            foreach($where as $what => $value){
                if(is_array($value)){
                    if($value[1] == 'NULL' || $value[1] == 'NOT NULL'){
                        $wherefields[] = sprintf("`%s` %s %s", $what, addslashes($value[0]), $value[1]);
                    }
                    else{
                        $wherefields[] = sprintf("`%s` %s ?", $what, addslashes($value[0]));
                        $this->values[] = $value[1];
                    }
                }
                else{
                    $wherefields[] = sprintf("`%s` = ?", $what);
                    $this->values[] = $value;
                }
            }
            if(!empty($wherefields)){
                return " WHERE ".implode(' AND ', $wherefields);
            }
        }
        return false;
    }
    
    /**
     * Sets the order sting for the SQL query based on an array or string
     * @param array|string $order This should be either set to array('fieldname' => 'ASC/DESC') or RAND()
     * @return string|false If the SQL query has an valid order by will return a string else returns false
     */
    private function orderBy($order){
        if(is_array($order)){
            foreach($order as $fieldorder => $fieldvalue){
                return sprintf(" ORDER BY `%s` %s", $fieldorder, strtoupper($fieldvalue));
            }
        }
        elseif($order == 'RAND()'){
            return " ORDER BY RAND()";
        }
        return false;
    }
    
    /**
     * Build the field list for the query
     * @param array $records This should be an array listing all of the fields
     * @param boolean $insert If this is an insert statement should be set to true to create the correct amount of queries for the prepared statement
     * @return string The fields list will be returned as a string to insert into the SQL query
     */
    private function fields($records, $insert = false){
        $fields = array();
        
        foreach($records as $field => $value){
            if($insert === true){
                $fields[] = sprintf("`%s`", $field);
                $this->prepare[] = '?';
            }
            else{
                $fields[] = sprintf("`%s` = ?", $field);
            }
            $this->values[] = $value;
        }
        return implode(', ', $fields);
    }
    
    /**
     * Returns the limit SQL for the current query as a string
     * @param integer|array $limit This should either be set as an integer or should be set as an array with a start and end value  
     * @return string|false Will return the LIMIT string for the current query if it is valid else returns false
     */
    private function limit($limit = 0){
        if(is_array($limit)){
            foreach($limit as $start => $end){
                 return " LIMIT ".(int)$start.", ".(int)$end;
            }
        }
        elseif((int)$limit > 0){
            return " LIMIT ".(int)$limit;
        }
        return false;
    }
    
    
    /**
     * Set the cache with a key and value
     * @param string $key The unique key to store the value against
     * @param mixed $value The value of the MYSQL query 
     */
    public function setCache($key, $value){
        if($this->cacheEnabled){
            $this->cacheObj->save($key, $value);
        }
    }
    
    /**
     * Get the results for a given key
     * @param string $key The unique key to check for stored variables
     * @return mixed Returned the cached results from
     */
    public function getCache($key){
        if($this->modified === true || !$this->cacheEnabled){return false;}
        else{
            $this->cacheValue = $this->cacheObj->fetch($key);
            return $this->cacheValue;
        }
    }
    
    /**
     * Clears the cache
     */
    public function flushDB(){
        $this->cacheObj->deleteAll();
    }
}


1		<?php
2		namespace DBAL;
3
4		use PDO;
5
6		/**
7		* PDO Database connection class
8		*
9		* @author Adam Binnersley <[email protected]>
10		* @version PDO Database Class
11		*/
12		final class Database implements DBInterface{
13		protected $db;
14		protected $sql;
15		private $key;
16
17		protected $logLocation = 'logs'.DIRECTORY_SEPARATOR;
18		public $logErrors = true;
19		public $logQueries = false;
20		public $displayErrors = false;
21
22		protected $database;
23		protected $cacheEnabled = false;
24		protected $cacheObj;
25		protected $cacheValue;
26		protected $modified = false;
27
28		private $query;
29		private $values = array();
30		private $prepare = array();
31
32		/**
33		* Connect to database using PDO connection
34		* @param string $hostname This should be the host of the database e.g. 'localhost'
35		* @param string $username This should be the username for the chosen database
36		* @param string $password This should be the password for the chosen database
37		* @param string $database This should be the database that you wish to connect to
38		* @param string\|null $backuphost If you have a replication server set up put the hostname or IP address incase the primary server goes down
39		*/
40		public function __construct($hostname, $username, $password, $database, $backuphost = NULL){
41		try{
42		$this->connectToServer($username, $password, $database, $hostname);
43		}
44		catch(\Exception $e){
45		if($backuphost !== NULL){
46		$this->connectToServer($username, $password, $database, $backuphost);
47		}
48		$this->error($e);
49		}
50		}
51
52		/**
53		* Closes the PDO database connection when Database object unset
54		*/
55		public function __destruct(){
56		$this->closeDatabase();
57		}
58
59		/**
60		* Connect to the database using PDO connection
61		* @param string $username This should be the username for the chosen database
62		* @param string $password This should be the password for the chosen database
63		* @param string $database This should be the database that you wish to connect to
64		* @param string $hostname The hostname for the database
65		*/
66		protected function connectToServer($username, $password, $database, $hostname){
67		if(!$this->db){
68		$this->database = $database;
69		$this->db = new PDO('mysql:host='.$hostname.';dbname='.$database, $username, $password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true, PDO::ATTR_PERSISTENT => true, PDO::ATTR_EMULATE_PREPARES => true));
70		}
71		}
72
73		/**
74		* Enables the caching and set the caching object to the one provided
75		* @param object $caching This should be class of the type of caching you are using
76		*/
77		public function setCaching($caching){
78		if(is_object($caching)){
79		$this->cacheObj = $caching;
80		$this->cacheEnabled = true;
81		}
82		return $this;
83		}
84
85		/**
86		* This query function is used for more advanced SQL queries for which non of the other methods fit
87		* @param string $sql This should be the SQL query which you wish to run
88		* @return array Returns array of results for the query that has just been run
89		*/
90	1	public function query($sql, $variables = array(), $cache = true){
91		try{
92	1	$this->sql = $sql;
93	1	$this->query = $this->db->prepare($this->sql);
94	1	$this->query->execute($variables);
95	1	if(strpos($this->sql, 'SELECT') !== false){
96	1	return $this->query->fetchAll(PDO::FETCH_ASSOC);
97		}
98		}
99		catch(\Exception $e){
100		$this->error($e);
101		}
102		}
103
104		/**
105		* Returns a single record for a select query for the chosen table
106		* @param string $table This should be the table you wish to select the values from
107		* @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
108		* @param string\|array $fields This should be the records you wis to select from the table. It should be either set as '*' which is the default or set as an array in the following format array('field', 'field2', 'field3', etc).
109		* @param array $order This is the order you wish the results to be ordered in should be formatted as follows array('fieldname' => 'ASC') or array("'fieldname', 'fieldname2'" => 'DESC')
110		* @param boolean $cache If the query should be cached or loaded from cache set to true else set to false
111		* @return array Returns a single table record as the standard array when running SQL queries
112		*/
113	1	public function select($table, $where = array(), $fields = '*', $order = array(), $cache = true){
114	1	return $this->selectAll($table, $where, $fields, $order, 1, $cache);
115		}
116
117		/**
118		* Returns a multidimensional array of the results from the selected table given the given parameters
119		* @param string $table This should be the table you wish to select the values from
120		* @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
121		* @param string\|array $fields This should be the records you wis to select from the table. It should be either set as '*' which is the default or set as an array in the following format array('field', 'field2', 'field3', etc).
122		* @param array $order This is the order you wish the results to be ordered in should be formatted as follows array('fieldname' => 'ASC') or array("'fieldname', 'fieldname2'" => 'DESC')
123		* @param integer\|array $limit The number of results you want to return 0 is default and returns all results, else should be formated either as a standard integer or as an array as the start and end values e.g. array(0 => 150)
124		* @param boolean $cache If the query should be cached or loaded from cache set to true else set to false
125		* @return array Returns a multidimensional array with the chosen fields from the table
126		*/
127		public function selectAll($table, $where = array(), $fields = '*', $order = array(), $limit = 0, $cache = true){
128		$this->buildSelectQuery($table, $where, $fields, $order, $limit);
129		$result = $this->executeQuery($cache);
130		if(!$result){
131		if($limit === 1){$result = $this->query->fetch(PDO::FETCH_ASSOC);} // Reduce the memory usage if only one record and increase performance
132		else{$result = $this->query->fetchAll(PDO::FETCH_ASSOC);}
133		if($cache && $this->cacheEnabled){$this->setCache($this->key, $result);}
134		}
135		return $result;
136		}
137
138		/**
139		* Returns a single column value for a given query
140		* @param string $table This should be the table you wish to select the values from
141		* @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
142		* @param array $fields This should be the records you wis to select from the table. It should be either set as '*' which is the default or set as an array in the following format array('field', 'field2', 'field3', etc).
143		* @param int $colNum This should be the column number you wish to get (starts at 0)
144		* @param array $order This is the order you wish the results to be ordered in should be formatted as follows array('fieldname' => 'ASC') or array("'fieldname', 'fieldname2'" => 'DESC') so it can be done in both directions
145		* @param boolean $cache If the query should be cached or loaded from cache set to true else set to false
146		* @return mixed If a result is found will return the value of the colum given else will return false
147		*/
148		public function fetchColumn($table, $where = array(), $fields = '*', $colNum = 0, $order = array(), $cache = true){
149		$this->buildSelectQuery($table, $where, $fields, $order, 1);
150		$result = $this->executeQuery($cache);
151		if(!$result){
152		$result = $this->query->fetchColumn(intval($colNum));
153		if($cache && $this->cacheEnabled){$this->setCache($this->key, $result);}
154		}
155		return $result;
156		}
157
158		/**
159		* Inserts into database using the prepared PDO statements
160		* @param string $table This should be the table you wish to insert the values into
161		* @param array $records This should be the field names and values in the format of array('fieldname' => 'value', 'fieldname2' => 'value2', etc.)
162		* @return boolean If data is inserted returns true else returns false
163		*/
164		public function insert($table, $records){
165		unset($this->prepare);
166
167		$this->sql = sprintf("INSERT INTO `%s` (%s) VALUES (%s);", $table, $this->fields($records, true), implode(', ', $this->prepare));
168		$this->executeQuery(false);
169		return $this->numRows() ? true : false;
170		}
171
172		/**
173		* Updates values in a database using the provide variables
174		* @param string $table This should be the table you wish to update the values for
175		* @param array $records This should be the field names and new values in the format of array('fieldname' => 'newvalue', 'fieldname2' => 'newvalue2', etc.)
176		* @param array $where Should be the field names and values you wish to update in the form of an array e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
177		* @param int $limit The number of results you want to return 0 is default and will update all results that match the query, else should be formated as a standard integer
178		* @return boolean Returns true if update is successful else returns false
179		*/
180		public function update($table, $records, $where = array(), $limit = 0){
181		$this->sql = sprintf("UPDATE `%s` SET %s %s%s;", $table, $this->fields($records), $this->where($where), $this->limit($limit));
182		$this->executeQuery(false);
183		return $this->numRows() ? true : false;
184		}
185
186		/**
187		* Deletes records from the given table based on the variables given
188		* @param string $table This should be the table you wish to delete the records from
189		* @param array $where This should be an array of for the where statement
190		* @param int $limit The number of results you want to return 0 is default and will delete all results that match the query, else should be formated as a standard integer
191		*/
192		public function delete($table, $where, $limit = 0){
193		$this->sql = sprintf("DELETE FROM `%s` %s%s;", $table, $this->where($where), $this->limit($limit));
194		$this->executeQuery(false);
195		return $this->numRows() ? true : false;
196		}
197
198		/**
199		* Count the number of return results
200		* @param string $table The table you wish to count the result of
201		* @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
202		* @param boolean $cache If the query should be cached or loaded from cache set to true else set to false
203		* @return int Returns the number of results
204		*/
205		public function count($table, $where = array(), $cache = true){
206		$this->sql = sprintf("SELECT count(*) FROM `%s`%s;", $table, $this->where($where));
207		$this->key = md5($this->database.$this->sql.serialize($this->values));
208
209		$result = $this->executeQuery($cache);
210		if(!$result){
211		$result = $this->query->fetchColumn();
212		if($cache && $this->cacheEnabled){$this->setCache($this->key, $result);}
213		}
214		return $result;
215		}
216
217		/**
218		* Truncates a given table from the selected database so there are no values in the table
219		* @param string $table This should be the table you wish to truncate
220		* @return boolean If the table is emptied returns true else returns false
221		*/
222		public function truncate($table){
223		try{
224		$this->sql = sprintf("TRUNCATE TABLE `%s`", $table);
225		$this->query = $this->db->exec($this->sql);
226		}
227		catch(\Exception $e){
228		$this->error($e);
229		}
230		return $this->query ? true : false;
231		}
232
233		/**
234		* Returns the number of rows for the last query sent
235		* @return int Returns the number of rows for the last query
236		*/
237		public function numRows(){
238		if(isset($this->query)){
239		return $this->query->rowCount();
240		}
241		return 0;
242		}
243
244		/**
245		* Returns the number of rows for the last query sent (Looks a the numRows() function just added incase of habbit)
246		* @return int Returns the number of rows for the last query
247		*/
248		public function rowCount(){
249		return $this->numRows();
250		}
251
252		/**
253		* Returns the ID of the last record last inserted
254		* @param string $name This should be the name of the sequence object you wish to retrieve
255		* @return int\|string Returns the last inserted ID of the last insert item if $name is null else returns string with sequenced object
256		*/
257		public function lastInsertId($name = null) {
258		return $this->db->lastInsertId($name);
259		}
260
261		/**
262		* Returns the index of the given table or tables within the database
263		* @param string\|array $table Table can wither be a standard string with a single table name or an array with multiple table names
264		* @return array Returns the table index for the selected table as an array
265		*/
266		public function fulltextIndex($table){
267		$fieldlist = array();
268		if(is_array($table)){
269		foreach($table as $name){
270		$fieldlist[$name] = $this->fulltextIndex($name);
271		}
272		}else{
273		try{
274		$this->sql = sprintf("SHOW INDEX FROM %s;", $table);
275		$this->query = $this->db->exec($this->sql);
276		}
277		catch(\Exception $e){
278		$this->error($e);
279		}
280
281		while($index = $this->query->fetch(PDO::FETCH_ASSOC)){
282		if($index['Index_type'] == 'FULLTEXT' && $index['Key_name'] == 'fulltext'){
283		$fieldlist[] = $index['Column_name'];
284		}
285		}
286		}
287		return $fieldlist;
288		}
289
290		/**
291		* Returns the server version information
292		*/
293		public function serverVersion(){
294		return $this->db->getAttribute(PDO::ATTR_SERVER_VERSION);
295		}
296
297		/**
298		* Displays the error massage which occurs
299		* @param \Exception $error This should be an instance of Exception
300		*/
301		private function error($error){
302		if($this->logErrors){
303		$file = $this->logLocation.'db-errors.txt';
304		$current = file_get_contents($file);
305		$current .= date('d/m/Y H:i:s')." ERROR: ".$error->getMessage()." on ".$this->sql."\n";
306		file_put_contents($file, $current);
307		}
308		if($this->displayErrors){
309		die('ERROR: '.$error->getMessage().' on '.$this->sql);
310		}
311		}
312
313		/**
314		* Writes all queries to a log file
315		*/
316		public function writeQueryToLog(){
317		$file = $this->logLocation.'queries.txt';
318		$current = file_get_contents($file);
319		$current .= "SQL: ".$this->sql.":".serialize($this->values)."\n";
320		file_put_contents($file, $current);
321		}
322
323		/**
324		* Closes the PDO database connection by setting the connection to NULL
325		*/
326		public function closeDatabase(){
327		$this->db = null;
328		}
329
330		/**
331		* Build the SQL query but doesn't execute it
332		* @param string $table This should be the table you wish to select the values from
333		* @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
334		* @param string\|array $fields This should be the records you wis to select from the table. It should be either set as '*' which is the default or set as an array in the following format array('field', 'field2', 'field3', etc).
335		* @param array $order This is the order you wish the results to be ordered in should be formatted as follows array('fieldname' => 'ASC') or array("'fieldname', 'fieldname2'" => 'DESC') so it can be done in both directions
336		* @param integer\|array $limit The number of results you want to return 0 is default and returns all results, else should be formated either as a standard integer or as an array as the start and end values e.g. array(0 => 150)
337		*/
338		protected function buildSelectQuery($table, $where = array(), $fields = '*', $order = array(), $limit = 0){
339		if(is_array($fields)){
340		$selectfields = array();
341		foreach($fields as $field => $value){
342		$selectfields[] = sprintf("`%s`", $value);
343		}
344		$fieldList = implode(', ', $selectfields);
345		}
346		else{$fieldList = '*';}
347
348		$this->sql = sprintf("SELECT %s FROM `%s`%s%s%s;", $fieldList, $table, $this->where($where), $this->orderBy($order), $this->limit($limit));
349		$this->key = md5($this->database.$this->sql.serialize($this->values));
350		}
351
352		/**
353		* Execute the current query if no cache value is available
354		* @param boolean $cache If the cache should be checked for the checked for the values of the query set to true else set to false
355		* @return mixed If a cached value exists will be returned else if cache is not checked and query is executed will not return anything
356		*/
357		protected function executeQuery($cache = true){
358		if($this->logQueries){$this->writeQueryToLog();}
359		if($cache && $this->cacheEnabled && $this->getCache($this->key)){
360		return $this->cacheValue;
361		}
362		try{
363		$this->query = $this->db->prepare($this->sql);
364		$this->query->execute($this->values);
365		unset($this->values);
366		}
367		catch(\Exception $e){
368		$this->error($e);
369		}
370		}
371
372		/**
373		* This outputs the SQL where query based on a given array
374		* @param array $where This should be an array that you wish to create the where query for in the for array('field1' => 'test') or array('field1' => array('>=', 0))
375		* @return string\|false If the where query is an array will return the where string and set the values else returns false if no array sent
376		*/
377		private function where($where){
378		if(is_array($where)){
379		$wherefields = array();
380		foreach($where as $what => $value){
381		if(is_array($value)){
382		if($value[1] == 'NULL' \|\| $value[1] == 'NOT NULL'){
383		$wherefields[] = sprintf("`%s` %s %s", $what, addslashes($value[0]), $value[1]);
384		}
385		else{
386		$wherefields[] = sprintf("`%s` %s ?", $what, addslashes($value[0]));
387		$this->values[] = $value[1];
388		}
389		}
390		else{
391		$wherefields[] = sprintf("`%s` = ?", $what);
392		$this->values[] = $value;
393		}
394		}
395		if(!empty($wherefields)){
396		return " WHERE ".implode(' AND ', $wherefields);
397		}
398		}
399		return false;
400		}
401
402		/**
403		* Sets the order sting for the SQL query based on an array or string
404		* @param array\|string $order This should be either set to array('fieldname' => 'ASC/DESC') or RAND()
405		* @return string\|false If the SQL query has an valid order by will return a string else returns false
406		*/
407		private function orderBy($order){
408		if(is_array($order)){
409		foreach($order as $fieldorder => $fieldvalue){
410		return sprintf(" ORDER BY `%s` %s", $fieldorder, strtoupper($fieldvalue));
411		}
412		}
413		elseif($order == 'RAND()'){
414		return " ORDER BY RAND()";
415		}
416		return false;
417		}
418
419		/**
420		* Build the field list for the query
421		* @param array $records This should be an array listing all of the fields
422		* @param boolean $insert If this is an insert statement should be set to true to create the correct amount of queries for the prepared statement
423		* @return string The fields list will be returned as a string to insert into the SQL query
424		*/
425		private function fields($records, $insert = false){
426		$fields = array();
427
428		foreach($records as $field => $value){
429		if($insert === true){
430		$fields[] = sprintf("`%s`", $field);
431		$this->prepare[] = '?';
432		}
433		else{
434		$fields[] = sprintf("`%s` = ?", $field);
435		}
436		$this->values[] = $value;
437		}
438		return implode(', ', $fields);
439		}
440
441		/**
442		* Returns the limit SQL for the current query as a string
443		* @param integer\|array $limit This should either be set as an integer or should be set as an array with a start and end value
444		* @return string\|false Will return the LIMIT string for the current query if it is valid else returns false
445		*/
446		private function limit($limit = 0){
447		if(is_array($limit)){
448		foreach($limit as $start => $end){
449		return " LIMIT ".(int)$start.", ".(int)$end;
450		}
451		}
452		elseif((int)$limit > 0){
453		return " LIMIT ".(int)$limit;
454		}
455		return false;
456		}
457
458
459		/**
460		* Set the cache with a key and value
461		* @param string $key The unique key to store the value against
462		* @param mixed $value The value of the MYSQL query
463		*/
464		public function setCache($key, $value){
465		if($this->cacheEnabled){
466		$this->cacheObj->save($key, $value);
467		}
468		}
469
470		/**
471		* Get the results for a given key
472		* @param string $key The unique key to check for stored variables
473		* @return mixed Returned the cached results from
474		*/
475		public function getCache($key){
476		if($this->modified === true \|\| !$this->cacheEnabled){return false;}
477		else{
478		$this->cacheValue = $this->cacheObj->fetch($key);
479		return $this->cacheValue;
480		}
481		}
482
483		/**
484		* Clears the cache
485		*/
486		public function flushDB(){
487		$this->cacheObj->deleteAll();
488		}
489		}
490

AdamB7586 / pdo-dbal

Push — master ( d9b6b7...ce8fae )

Database::insert() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like