File: D:/HostingSpaces/SBogers10/lab.komma-mediadesign.nl/wwwroot/lab/lib/dbHandler.class.php
<?php
/**
* dbHandler.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');
/**
* 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, $condition=NULL)
{
if( ! empty($key) && ! empty($value))
{
$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)
{
if(isset($this->_rules[$key]))
{
unset($this->_rules[$key]);
}
}
/*
* 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;
}
/*
Queries
*/
/**
* Handles basic select query
* Returns a result array of FALSE
*
* @access public
* @param string, int/string
* @return array
*/
public function select()
{
// 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']))
{
$query .= ' '.$rule['condition'].' ';
}
else
{
$query .= ' AND ';
}
}
$query .= 'p.'.$rule['key'].' = :'.$rule['key'].' ';
$i++;
}
// 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'];
}
}
// prepare statement
if($st = $this->_pdo->prepare($query))
{
$param = array();
foreach($this->_rules as $rule)
{
$param[':'.$rule['key']] = $rule['value'];
}
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;
}
}
}
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;
}
}
// 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++;
}
if($st = $this->_pdo->prepare($query))
{
$param = array();
foreach($this->_data as $column => $value)
{
$cleanValue = trim($value);
if($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;
}
}