File: D:/HostingSpaces/SBogers10/kms.komma.pro/wwwroot/lib/general/storage.class.php
<?php
/**
* db_handler.class.php
* Created by Komma Mediadesign.
* Author: mike
* Date: 3/20/13
*/
/*
Handles all database tasks such as Insert, Update and Select queries
This object is only used for the basic queries.
*/
class Storage
{
/**
*
* @var object PDO Global pdo object
*/
private $_pdo;
/**
*
* @var array Data, which needs to be set before executing queries.
* Needs to be associative array, from which the key-names match database columns
* Data can be set by setData function.
*/
private $_data = array();
/**
*
* @var array Result which is set by PDOStatement::Execute()
*/
private $_result = array();
/**
*
* @var string Name of the table from which the data is grabbed
*/
private $_tableName = '';
/**
*
* @var boolean If true get only the active items, so not the one in the trashcan
*/
private $_activeOnly = FALSE;
/**
*
* @var int Id of the page
*/
private $_pageId;
/**
*
* @var array Contains the rules that are added to a query, $this->_rules[] = array('key'=>'','value'=>'','condition'='AND')
*/
private $_rules = array();
/**
*
* @var array Contains information for the LIMIT in a query
*/
private $_scope = array('start'=>NULL,'count'=>NULL);
/**
*
* @var array used in the get query, on what condition should we order the items, and in what direction
*/
private $_order = array();
/**
* Constructor
*/
public function __construct()
{
global $pdo;
$this->_pdo = $pdo;
}
/*
GET / SET
*/
/**
* Sets the data variable of this class
*
* @access public
* @param array
* @return null
*/
public function setData($data)
{
if( ! empty($data))
{
$this->_data = $data;
}
}
/**
* Sets the data variable of this class
*
* @access public
* @param array
* @return null
*/
private function setDefaultData()
{
if($st = $this->_pdo->query('SHOW COLUMNS FROM ' . $this->_tableName))
{
$rows = $st->fetchAll(PDO::FETCH_ASSOC);
$fields = array();
foreach($rows as $row)
{
$fields[] = $row['Field'];
}
$this->_data = array_fill_keys($fields,'');
return true;
}
return false;
}
/**
* Sets the activeOnly variable of this class
*
* @access public
* @param $bool
* @param $pageId
* @internal param $boolean
* @return null
*/
public function setActiveOnly($pageId)
{
if( ! empty($pageId))
{
$this->_activeOnly = TRUE;
$this->_pageId = $pageId;
}
}
/**
* Set tableName
*
* @access public
* @param string
* @return null
*/
public function setTableName($name)
{
// Every time somebody changes the storage table name, assume we want to reset the storage.
$this->reset();
// reset table name
if( ! empty($name))
{
$this->_tableName = $name;
}
}
/**
* Change the order variable of this class
*
* @access public
* @param $column
* @param $dir
* @internal param $string , string
* @return null
*/
public function setOrder($column, $dir)
{
if( ! empty($column) && ! empty($dir))
{
$this->_order = array('column'=>$column,'dir'=>$dir);
}
}
/**
* Change the scope variable of this class
*
* @access public
* @param null $start
* @param null $count
* @internal param $int , int
* @return null
*/
public function setScope($start=NULL, $count=NULL)
{
if( $count != NULL)
{
$this->_scope = array('start'=>$start,'count'=>$count);
}
}
/**
* Adds a rule to the array rules
* Rules contain conditions for the SELECT query
*
* @access public
* @param $key
* @param $value
* @param null $condition
* @internal param $string , int/string
* @return null
*/
public function addRule($key = NULL, $value = NULL, $condition=NULL)
{
if( $key != NULL && $value != NULL)
{
$this->_rules[] = array('key'=>$key,'value'=>$value,'condition'=>$condition);
}
}
/**
* Clears an existing rule
* Rules contain conditions for the SELECT query
*
* @access public
* @param string, int/string
* @return null
*/
public function clearRule($key = null)
{
if($key != null)
{
if(isset($this->_rules[$key]))
{
unset($this->_rules[$key]);
}
}
else
{
$this->_rules = array();
}
}
/*
* Reset Storage
*/
private function reset()
{
$this->_data = array();
$this->_activeOnly = FALSE;
$this->_pageId;
$this->_rules = array();
$this->_scope = array('start'=>NULL,'count'=>NULL);
$this->_order = array();
}
/*
Queries
*/
/**
* Handles basic select query
* Returns a result array of FALSE
*
* @access public
* @param bool $echo
* @internal param $string , int/string
* @return array
*/
public function select($echo = FALSE)
{
// select all values
$data = array();
// build query
// what to select:
$query = 'SELECT DISTINCT ';
if(count($this->_data) == 0)
{
$this->setDefaultData();
}
// Get columns
foreach(array_keys($this->_data) as $column)
{
$query.= 'p.'.$column.',';
}
$query = substr($query, 0, -1);
// From table
$query .= ' FROM '.$this->_tableName.' as p ';
// active only
$whereUsed = FALSE;
if($this->_activeOnly)
{
$query .= ', ' . TABLE_PREFIX . 'kms_active AS a WHERE a.active = 1 AND a.itemId = p.id AND a.pageId = '.$this->_pageId.' ';
$whereUsed = TRUE;
}
// rules
$i = 1;
foreach($this->_rules as $rule)
{
if($i == 1 && ! $whereUsed)
{
$query .= ' WHERE ';
}
else {
if( ! empty($rule['condition']))
{
$query .= ' '.$rule['condition'].' ';
}
else
{
$query .= ' AND ';
}
}
$query .= 'p.'.$rule['key'].' = :'.$rule['key'].' ';
$i++;
}
// order:
if( ! empty($this->_order))
{
$query .= ' ORDER BY p.'.$this->_order['column'].' '.$this->_order['dir'].' ';
}
// scope
if( ! empty($this->_scope['start']) || ! empty($this->_scope['count']))
{
if( empty($this->_scope['start']))
{
$query .= ' LIMIT '.$this->_scope['count'];
}
else
{
$query .= ' LIMIT '.$this->_scope['start'].', '.$this->_scope['count'];
}
}
if($echo)
{
echo $query . '<br />';
}
// prepare statement
if($st = $this->_pdo->prepare($query))
{
$param = array();
foreach($this->_rules as $rule)
{
$param[':'.$rule['key']] = $rule['value'];
}
// Return result
if($st->execute($param)){
if($st->rowCount() > 0)
{
if($st->rowCount() > 1)
{
while($this->_result = $st->fetch(PDO::FETCH_ASSOC))
{
$data[] = $this->_result;
}
}
else
{
$this->_result = $st->fetch(PDO::FETCH_ASSOC);
$data = $this->_result;
}
return $data;
}
}
else
{
print_r( $st->errorInfo() );
}
}
else
{
print_r( $this->_pdo->errorInfo() );
}
return FALSE;
}
/**
* Handles basic insert queries
* Needs an associative array from which the keys match the column names in the database.
* Returns the id of the inserted row
*
* @access public
* @param bool $echo
* @internal param $
* @return int
*/
public function insert($echo = false)
{
if(! empty($this->_data))
{
// build query
$query = 'INSERT INTO '.$this->_tableName.'(';
foreach($this->_data as $column => $value)
{
if(! empty($value))
{
$query.= $column.',';
}
}
$query = substr($query, 0, -1);
$query .= ') VALUES(';
foreach($this->_data as $column => $value)
{
if( ! empty($value))
{
$query.= ':'.$column.',';
}
}
$query = substr($query, 0, -1);
$query .= ')';
if($echo) echo $query;
// prepare statement
if($st = $this->_pdo->prepare($query))
{
// set execute parameter-array
$param = array();
foreach($this->_data as $column => $value)
{
if( ! empty($value))
{
$cleanValue = $this->encodeForDb($value);
if($cleanValue == '') $cleanValue = null;
$param[':'.$column] = $cleanValue;
}
}
// execute statement
if($st->execute($param))
{
return $this->_pdo->lastInsertId();
}
else
{
print_r( $st->errorInfo() );
}
}
else
{
print_r( $this->_pdo->errorInfo() );
}
}
return FALSE;
}
/**
* Handles basic update queries
* Needs an associative array from which the keys match the column names in the database.
*
* @access public
* @param bool $echo
* @internal param $
* @return boolean
*/
public function update($echo = false)
{
// build query
$query = 'UPDATE '.$this->_tableName.' SET ';
foreach(array_keys($this->_data) as $column)
{
$query.= $column.' = :'.trim($column).',';
}
$query = substr($query, 0, -1);
// rules
$i = 1;
foreach($this->_rules as $rule)
{
if($i == 1)
{
$query .= ' WHERE ';
}
else {
if( ! empty($rule['condition']))
{
$query .= ' '.$rule['condition'].' ';
}
else
{
$query .= ' AND ';
}
}
$query .= $rule['key'].' = :'.trim($rule['key']).' ';
$i++;
}
if($echo) echo $query . '<br />';
if($st = $this->_pdo->prepare($query))
{
$param = array();
foreach($this->_data as $column => $value)
{
$cleanValue = $this->encodeForDb($value);
if($cleanValue == '') $cleanValue = null;
$param[':'.trim($column)] = $cleanValue;
}
foreach($this->_rules as $rule)
{
$param[':'.trim($rule['key'])] = $rule['value'];
}
// execute statement
if($st->execute($param))
{
return TRUE;
}
else
{
print_r($st->errorInfo());
}
}
else{
print_r($this->_pdo->errorInfo());
}
return FALSE;
}
/*
* Delete from database
*/
public function delete()
{
// build query to remove pageItem from database
$query = 'DELETE FROM ' . $this->_tableName;
// rules
$i = 1;
foreach($this->_rules as $rule)
{
if($i == 1)
{
$query .= ' WHERE ';
}
else {
if( ! empty($rule['condition']))
{
$query .= ' '.$rule['condition'].' ';
}
else
{
$query .= ' AND ';
}
}
$query .= $rule['key'].' = :'.$rule['key'].' ';
$i++;
}
// always limit to 1 (safety pin)
$query .= ' LIMIT 1';
if($st = $this->_pdo->prepare($query))
{
$param = array();
foreach($this->_rules as $rule)
{
$param[':'.$rule['key']] = $rule['value'];
}
$st->execute($param);
}
}
private function encodeForDb($str)
{
// Remove whitespace
$str = trim($str);
// Convert html text to utf-8 special characters
$str = htmlentities($str, ENT_NOQUOTES, 'utf-8');
// Keep html tags
$str = str_replace("<","<",$str);
$str = str_replace(">",">",$str);
$str = str_replace("&","&",$str);
// Convert single <,> or & back to utf-8 special characters
$str = str_replace("& ","& ",$str);
$str = str_replace(" >"," >",$str);
$str = str_replace("< ","< ",$str);
return $str;
}
}