<?php

///////////////////////////////////////////////////////////////////////////////////////////////////
#  Author:   Anil Kumar
#  Version:	 1.0
#  Date:     2-Jun-2013
#  Purpose:  PDO Helper Class
#  Requires: Requires PHP5, PDO
///////////////////////////////////////////////////////////////////////////////////////////////////

class cpPDO
{
	public $db_connect_handle = null;
	public $last_error = null;
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function openMySQL($dbhost, $dbname, $dbuser, $dbpassword)
	{
		$this->clearErrors();
		$this->db_connect_handle = null;
		try 
		{
			$this->db_connect_handle = new PDO('mysql:host=' . $dbhost . ';dbname=' . $dbname, $dbuser, $dbpassword, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
		}
		if($this->db_connect_handle)
		{
			$this->db_connect_handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$this->db_connect_handle->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
		}
		return $this->db_connect_handle;
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function setError($msg)
	{
		$this->last_error[] = $msg;
	}
	
	public function clearErrors()
	{
		unset($this->last_error);
	}
	
	public function getErrors()
	{
		if(isset($this->last_error))
			return $this->last_error;
		else
			return null;
	}
	public function getLastError()
	{
		return $this->getError();
	}
	
	public function getError($index = -1)
	{
		if(isset($this->last_error))
		{
			$count = count($this->last_error);
			if($index < 0)
				$index = $count-1;
			if($index >= 0 && $index < $count)
				return $this->last_error[$index];
		}
		return "";
	}
	
	public function showLog()
	{
		if(isset($this->last_error))
		{
			$count = count($this->last_error);
			if($count)
			{
				foreach($this->last_error as $error)
					echo $error . "<br>";
			}
		}
	}
	
	public function getLog()
	{
		$elog = "";
		if(isset($this->last_error))
		{
			$count = count($this->last_error);
			if($count)
			{
				foreach($this->last_error as $error)
					$elog .= $error . "\n";
			}
		}
		return $elog;
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function getAllColumnNames($tablename)
	{
		try
		{
			$columns = array();
			$rs = $this->db_connect_handle->query('SELECT * FROM ' . $tablename . ' LIMIT 0');
			for ($i = 0; $i < $rs->columnCount(); $i++) 
			{
				$col = $rs->getColumnMeta($i);
				$columns[] = $col['name'];
			}
			return $columns;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function addMissingColumns($tablename, $columns)
	{
		$excolumns = $this->getAllColumnNames($tablename);
		if($excolumns !== false)
		{
			$count = count($columns)/3;
			$missing = 0;
			$sql = "ALTER TABLE `" . $tablename . "` ADD (";
			for($i = 0; $i < $count; $i++)
			{
				if(!in_array($columns[$i*3], $excolumns))
				{
					$sql .= "`" . $columns[$i*3] . "` ";
					$sql .= $columns[$i*3+1] . " ";
					$sql .= $columns[$i*3+2] . ",";
					$missing++;
				}
			}
			if($missing)
			{
				$sql = rtrim($sql, ", ");
				$sql .= ")";
				try 
				{
					$this->db_connect_handle->exec($sql);
					return true;
				} 
				catch (PDOException $e) 
				{
					$this->setError($e->getMessage());
					return false;
				}
			}
			else
				$this->setError("No columns are missing");
		}
		return false;
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function createTable($tablename, $columns, $keys, $autoinc=0)
	{
		try
		{
			$this->db_connect_handle->query('select 1 from ' . $tablename);
			$this->setError("Table " . $tablename . " Exists");
			return false;
		} 
		catch (PDOException $e) 
		{
			$count = count($columns)/3;
			$sql = "CREATE TABLE " . $tablename . "(";
			for($i = 0; $i < $count; $i++)
			{
				$sql .= $columns[$i*3] . " ";
				$sql .= $columns[$i*3+1] . " ";
				$sql .= $columns[$i*3+2] . ",";
			}
			$sql .= $keys;
			$sql = rtrim($sql, ", ");
			$sql .= ") ENGINE=MyISAM  DEFAULT CHARSET=utf8 ";
			if($autoinc > 0)
				$sql .= "AUTO_INCREMENT=" . $autoinc;
			$sql = rtrim($sql);			
			try 
			{
				$this->db_connect_handle->exec($sql);
				return true;
			} 
			catch (PDOException $e) 
			{
				$this->setError($e->getMessage());
				return false;
			}
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function renameTable($oldtablename, $newtablename)
	{
		try
		{
			$this->db_connect_handle->query('select 1 from ' . $oldtablename);
			try
			{
				$this->db_connect_handle->query('select 1 from ' . $newtablename);
				$this->setError("Table " . $newtablename . " Exists. Unable to rename.");
				return false;
			}
			catch (PDOException $e) 
			{
				$sql = "RENAME TABLE `" . $oldtablename . "` TO `" . $newtablename . "`";
				try 
				{
					$this->db_connect_handle->exec($sql);
					return true;
				} 
				catch (PDOException $e) 
				{
					$this->setError($e->getMessage());
					return false;
				}
			}
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	# will return false on database error. check $dbError value too.
	
	public function isEntryPresent($tablename, $fieldname, $value, &$dbError)
	{
		$dbError = false;
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT * FROM " . $tablename . " WHERE " . $fieldname . "=?");
			$stmt->execute(array($value));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 0)
				return false;
			else
				return true;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function getSingleEntry($tablename, $fieldname, $value, $entryField="id")
	{
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT " . $entryField . " FROM " . $tablename . " WHERE " . $fieldname . "=?");
			$stmt->execute(array($value));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 0)
				return false;
			else if(isset($rows[0][$entryField]))
			{
				return $rows[0][$entryField];
			}
			return false;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	# to be used in the derived class (required)
	
	public function getColumns($tablename)
	{
		return null;
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	# to be used in the derived class (optional)
	
	public function getDefaultKeys($tablename)
	{
		return "PRIMARY KEY (id)";
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	# to be used in the derived class (optional)
	
	public function getDefaultAutoIncrement($tablename)
	{
		return 1;
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function initTable($tablename)
	{
		return $this->createTable($tablename, $this->getColumns($tablename), $this->getDefaultKeys($tablename), $this->getDefaultAutoIncrement($tablename));
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function repairTable($tablename)
	{
		return $this->addMissingColumns($tablename, $this->getColumns($tablename));
	}
}

///////////////////////////////////////////////////////////////////////////////////////////////////

?>
