File: D:/HostingSpaces/SBogers10/spire.komma-mediadesign.nl/wwwroot/mvc/models/m_dbHandler.php
<?php
/*
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 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();
/**
* @var array Betweens: Holds all the between rows. (used in Slider-filter)
*/
private $_betweens = 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'=>'itemOrder','dir'=>'DESC');
/**
* 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 activeOnly variable of this class
*
* @access public
* @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 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 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 string, int/string
* @return null
*/
public function addRule($key, $value=NULL, $condition=NULL)
{
if( ! empty($key))
{
$this->_rules[] = array('key'=>$key,'value'=>$value,'condition'=>$condition);
}
}
/**
* 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;
}
public function addBetweenRows($column, $min, $max, $type='BETWEEN')
{
switch($type)
{
case 'BETWEEN' :
$this->_betweens[] = 'AND (p.'.$column.' BETWEEN '.$min.' AND '.$max.') ';
break;
case 'MIN' :
$this->_betweens[] = 'AND (p.'.$column.' >= '.$min.') ';
break;
case 'MAX' :
$this->_betweens[] = 'AND (p.'.$column.' <= '.$max.') ';
break;
}
}
/**
* Clears an existing rule
* Rules contain conditions for the SELECT query
*
* @access public
* @param string, int/string
* @return null
*/
public function clearRule($key)
{
foreach($this->_rules as $n => $rule)
{
if($rule['key'] == $key){
unset($this->_rules[$n]);
}
}
}
/**
* 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 string, int/string
* @return array
*/
public function select($echo = FAlSE)
{
// select all values
$data = array();
// build query
// what to select:
$query = 'SELECT DISTINCT ';
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 .= ', 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']))
{
if($rule['condition'] != 'AND LIKE' && $rule['condition'] != 'OR LIKE')
{
$query .= ' '.$rule['condition'].' ';
}
else if($rule['condition'] == 'OR LIKE'){
$query .= ' OR ';
}
else{
$query .= ' AND ';
}
}
else
{
$query .= ' AND ';
}
}
if( ! empty($rule['condition']) && ( $rule['condition'] == 'AND LIKE' || $rule['condition'] == 'OR LIKE' ))
{
$query .= 'p.'.$rule['key'].' LIKE :'.$rule['key'].' ';
}
else
{
$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)
{
$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'];
}
// betweens (don't need params)
foreach($this->_betweens as $queryRule)
{
$query .= $queryRule;
}
// order:
$query .= ' ORDER BY '.$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'];
}
}
// prepare statement
if($st = $this->_pdo->prepare($query))
{
$param = array();
// add rules
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(($_SERVER['REMOTE_ADDR'] == '5.172.219.238' || $_SERVER['REMOTE_ADDR'] = '127.0.0.1') && $echo)
{
var_dump($query);
exit;
}
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
* @return int
*/
public function insert()
{
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 .= ')';
// prepare statement
if($st = $this->_pdo->prepare($query))
{
// set execute parameter-array
$param = array();
foreach($this->_data as $column => $value)
{
if( ! empty($value))
{
$param[':'.$column] = $value;
}
}
echo $query;
// 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()
{
// build query
$query = 'UPDATE '.$this->_tableName.' SET ';
foreach(array_keys($this->_data) as $column)
{
$query.= $column.'= :'.$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'].' = :'.$rule['key'].' ';
$i++;
}
//prepare statement
if($st = $this->_pdo->prepare($query))
{
$param = array();
foreach($this->_data as $column => $value)
{
$cleanValue = trim($value);
if(empty($cleanValue)) $cleanValue = null;
$param[':'.$column] = $cleanValue;
}
foreach($this->_rules as $rule)
{
$param[':'.$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;
}
}