File: D:/HostingSpaces/SBogers64/klimroosbudel.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 = [];
/**
* @var array Result which is set by PDOStatement::Execute()
*/
private $_result = [];
/**
* @var string Name of the table from which the data is grabbed
*/
private $_tableName = '';
/**
* @var bool 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,->_rules[] = array('key'=>'','value'=>'','condition'='AND')
*/
private $_rules = [];
/**
* @var array Contains information for the LIMIT in a query
*/
private $_scope = ['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 = ['column'=>'id', 'dir'=>'DESC'];
/* Search function extension */
/**
* @var array Contains the like groups (for the searchfilter)
*/
private $_searchGroups = [];
/**
* @var array Contains the superGroup, a group containing the likeGroup (for the searchFilter)
*/
private $_superGroups = [];
private $_superGroupsParam = [];
/* End Search function extension */
/**
* Constructor
*/
public function __construct()
{
global $pdo;
$this->_pdo = $pdo;
}
/*
GET / SET
*/
/**
* Sets the data variable of this class
*
* @param array
* @return null
*/
public function setData($data)
{
if (! empty($data)) {
$this->_data = $data;
}
}
/**
* Sets the data variable of this class
*
* @param array
* @return null
*/
private function setDefaultData()
{
if ($st = $this->_pdo->query('SHOW COLUMNS FROM '.$this->_tableName)) {
$rows = $st->fetchAll(PDO::FETCH_ASSOC);
$fields = [];
foreach ($rows as $row) {
$fields[] = $row['Field'];
}
$this->_data = array_fill_keys($fields, '');
return true;
}
return false;
}
public function resetData()
{
$this->_data = [];
}
/**
* Sets the activeOnly variable of this class
*
* @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
*
* @param string
* @return null
*/
public function setTableName($name)
{
if (! empty($name)) {
$this->_tableName = $name;
}
}
/**
* Change the order variable of this class
*
* @param $column
* @param $dir
* @internal param $string , string
* @return null
*/
public function setOrder($column, $dir)
{
if (! empty($column) && ! empty($dir)) {
$this->_order = ['column'=>$column, 'dir'=>$dir];
}
}
/**
* Change the scope variable of this class
*
* @param null $start
* @param null $count
* @internal param $int , int
* @return null
*/
public function setScope($start = null, $count = null)
{
if ($count != null) {
$this->_scope = ['start'=>$start, 'count'=>$count];
}
}
/**
* Adds a rule to the array rules
* Rules contain conditions for the SELECT query
*
* @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[] = ['key'=>$key, 'value'=>$value, 'condition'=>$condition];
}
}
/**
* Clears an existing rule
* Rules contain conditions for the SELECT query
*
* @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 = [];
}
}
/*
* 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];
}
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 = [];
$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[] = ['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
*
* @param bool $echo
* @internal param $string , int/string
* @return array
*/
public function select($echo = false)
{
// select all values
$data = [];
// build query
// what to select:
$query = 'SELECT DISTINCT ';
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 = [];
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;
}*/
}
if ($echo) {
echo '<pre>';
print_r($data);
echo '</pre>';
}
return $data;
}
}
}
if ($echo) {
echo 'false';
}
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
*
* @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 = [];
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.
*
* @param
* @return bool
*/
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 = [];
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 = [];
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;
}
}