File: D:/HostingSpaces/SBogers68/resortouddorpduin.nl/wwwroot/kms/lib/general/db_handler.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 DatabaseHandler
{
/**
*
* @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('column'=>'id','dir'=>'DESC');
/**
* @var array Contains the parameters for a join query
*/
private $_params = array();
/**
* @var string Contains join query
*/
private $_query = '';
/* Search function extension */
/**
* @var array Contains the like groups (for the searchfilter)
*/
private $_searchGroups = array();
/**
* @var array Contains the superGroup, a group containing the likeGroup (for the searchFilter)
*/
private $_superGroups = array();
private $_superGroupsParam = array();
/* End Search function extension */
/**
* 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;
}
public function resetData()
{
$this->_data = array();
}
/**
* Sets the activeOnly variable of this class
*
* @access public
* @param $bool
* @param $pageId
* @internal param $boolean
* @return null
*/
public function setActiveOnly($bool, $pageId)
{
if( ! empty($bool) && ! empty($pageId))
{
$this->_activeOnly = $bool;
$this->_pageId = $pageId;
}
}
/**
* Set tableName
*
* @access public
* @param string
* @return null
*/
public function setTableName($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, $value, $condition=NULL)
{
if( ! empty($key) && ( ! empty($value) || $value == '0'))
{
$this->_rules[] = array('key'=>$key,'value'=>$value,'condition'=>$condition);
}
}
public function setQuery($query, $param)
{
if (!empty($query))
{
$this->_params = $param;
$this->_query = $query;
}
}
/**
* 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();
}
}
/*
* Checks whether input is a 2-dimensional or 1-dimensional array
* If 1-dimensional it gets converted to a 2-dimensional array
*/
public function twoDimensional($array)
{
if( ! is_array($array[key($array)])) $array = array($array);
return $array;
}
/*
Search
*/
/**
* Used by search fields. Adds a row that could look like: AND ( title LIKE :title0 OR description LIKE :description0 )
*/
public function addSearchGroup($search,$columns, $innerCondition = 'AND', $outerCondition = 'AND')
{
$searchGroupKey = count($this->_searchGroups);
if( is_array($columns))
{
$param = array();
$queryPart = ' '.$outerCondition.' (';
foreach($columns as $key => $column)
{
if($search != null)
{
if($key>0)$queryPart .= $innerCondition;
$queryPart .= ' '.$column.' LIKE :'.$column.$searchGroupKey.' ';
$param[$column.$searchGroupKey] = $search;
}
}
$queryPart .= ') ';
if($queryPart != ' '.$outerCondition.' () ')
{
$this->_searchGroups[] = array('query'=>$queryPart,'param'=>$param);
}
}
return $searchGroupKey;
}
/**
* This function inserts "searchGroups" into a supergroup
* (Needed for a search filter - Spire 2013)
* @param array
*/
public function addToSuperGroup($searchGroupIds)
{
if(is_array($searchGroupIds))
{
$this->_superGroups[] = $searchGroupIds;
}
}
/*
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
// check if _query is empty
if ($this->_query!='')
{
$query = $this->_query;
}else
{
$query = 'SELECT DISTINCT ';
// what to select:
if(count($this->_data) == 0)
{
$this->setDefaultData();
}
foreach(array_keys($this->_data) as $column)
{
$query.= 'p.'.$column.',';
}
$query = substr($query, 0, -1);
$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++;
}
// super groups
foreach($this->_superGroups as $s => $superGroup)
{
// Add a superGroup, then remove searchGroups used in superGroup from searchGroups array. (You don't want to use them twice)
if(count($superGroup) > 0 && ! empty($this->_searchGroups))
{
$query .= 'AND (';
foreach($superGroup as $key => $likeGroupId)
{
if(isset($this->_searchGroups[$likeGroupId]))
{
$group = $this->_searchGroups[$likeGroupId];
if($key == 0)
{
$group['query'] = str_replace(' AND (','(',$group['query']);
$group['query'] = str_replace(' OR (','(',$group['query']);
}
$query .= $group['query'];
// add data to superGroupParam array (because we are about to unset the likeGroup)
foreach($group['param'] as $column => $value)
{
$this->_superGroupsParam[$s.'-'.$key][$column] = $value;
}
unset($this->_searchGroups[$likeGroupId]);
}
}
$query .= ')';
}
}
// like groups
foreach($this->_searchGroups as $key => $group)
{
if($key == 0 && ! $whereUsed)
{
$group['query'] = str_replace(' AND (',' WHERE (',$group['query']);
}
$query .= $group['query'];
}
// 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 != empty controle
if (!empty($this->_params))
{
$param = $this->_params;
}else
{
$param = array();
foreach($this->_rules as $rule)
{
$param[':'.$rule['key']] = $rule['value'];
}
// add superGroups
foreach($this->_superGroupsParam as $groupParam)
{
foreach($groupParam as $column => $value)
{
$param[':'.$column] = $value;
if($echo) echo ':'.$column . ' = ' . $value . '<br />';
}
}
// add groups
foreach($this->_searchGroups as $key => $group)
{
foreach($group['param'] as $column => $value)
{
$param[':'.$column] = $value;
if($echo) echo ':'.$column . ' = ' . $value . '<br />';
}
}
}
if($st->execute($param))
{
if($st->rowCount() > 0)
{
if($st->rowCount() > 1)
{
while($this->_result = $st->fetch(PDO::FETCH_ASSOC))
{
$data[] = $this->_result;
/*
foreach($data as $i => $dataRow)
{
foreach($dataRow as $key => $value)
{
$value = html_entity_decode($value);
$value = str_replace("<","<",$value);
$value = str_replace(">",">",$value);
$value = str_replace("&","&",$value);
$value = str_replace("& ","& ",$value);
$data[$i][$key] = $value;
}
}*/
}
}
else
{
$this->_result = $st->fetch(PDO::FETCH_ASSOC);
$data = $this->_result;
/*
foreach($data as $key => $value)
{
$value = html_entity_decode($value);
$value = str_replace("<","<",$value);
$value = str_replace(">",">",$value);
$value = str_replace("&","&",$value);
$value = str_replace("& ","& ",$value);
$data[$key] = $value;
}*/
}
// echo '<pre>';
// print_r($data);
// echo '</pre>';
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
* @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 . '<br />';
// 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;
if($echo)
{
echo ':'.$column . ' => ' . $cleanValue . '<br />';
}
}
}
// 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
* @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($echo = false)
{
// 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($echo)
{
echo $query . '<br />';
}
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;
}
}