Page 1 of 1

PDO Database Connection Class Singleton Pattern

Posted: Sat Jun 05, 2010 10:18 pm
by shridharsomani06
First Create a file called dbparam.php like:-

Code: Select all


<?php

	define(HOST_NAME, "YourHostName");
	define(PORT_NUMBER, "YourPortNumber");
	define(DATABASE_NAME, "YourDatabaseName");
	define(USER_NAME, "YourUserName");
	define(PASSWORD, "YourPassword");
	define(PERSISTENT_CONNECTION, false);            //For Persistence connection set it to true

?>

Include this dbparam.php file into your header file so it will be available on every page.
Now create DBConnection.class.php like:-

Code: Select all


<?php

	class DBConnection
	{
		private static $mPdoInstance;
		private $mStatement;
		private $mResult;
		 	   
		//Constructor to create connection object.
		//Returns a PDO object on success. 
		public function __construct()
		{
			if(!is_object(self::$mPdoInstance)) 
			{
				try 
				{
					self::$mPdoInstance = new PDO('mysql:host='.HOST_NAME.';port='.PORT_NUMBER.';dbname='.DATABASE_NAME, USER_NAME, PASSWORD, array(PDO::ATTR_PERSISTENT =>  PERSISTENT_CONNECTION));
					
				} 
				catch (PDOException $e) 
				{
					die("PDO CONNECTION ERROR:- ".$e->getMessage()."<br/>");
				}
		 	}
			return self::$mPdoInstance;
		}
		
		//Destructor to destroy connection object.
		public function __destructor()
		{
			if(is_object(self::$mPdoInstance))
				self::$mPdoInstance = null;
		}
		
		//Execute an SQL statement.
		//Return the number of affected rows otherwise returns 0.
		public function executeQuery($pQuery)
		{
			return self::$mPdoInstance->exec($pQuery);
		}
		
		//Get Last InsertID.
		//Returns the ID of the last inserted row or sequence value.
		public function	getLastInsertId()
		{
			return self::$mPdoInstance->lastInsertId();
		}
		
		//Fetch Record from database.
		//Returns an array containing all of the remaining rows or one row in the result set.
		//$pFetchQuantityMode variable can have one of the two values either 'One'   OR   'All'(Default)
		//$pFetchMode variable can have one of the four values:- "FETCH_NUM"   OR   "FETCH_ASSOC"   OR   "FETCH_BOTH"   OR   "FETCH_OBJ"
		public function fetchData($pQuery, $pFetchMode, $pFetchQuantityMode = "All")
		{
			//Returns a PDOStatement object, or FALSE  on failure.
			$this->mStatement = self::$mPdoInstance->query($pQuery);
			
			$fqm = ($pFetchQuantityMode == "All") ? 'fetchAll' : 'fetch';
			
			switch($pFetchMode)
			{
				case "FETCH_NUM" :
				{					
					$this->mResult = $this->mStatement->$fqm(PDO::FETCH_NUM);
					break;
				}
				case "FETCH_ASSOC" :
				{
					$this->mResult = $this->mStatement->$fqm(PDO::FETCH_ASSOC);
					break;
				}
				case "FETCH_BOTH" :
				{
					$this->mResult = $this->mStatement->$fqm(PDO::FETCH_BOTH);
					break;
				}
				case "FETCH_OBJ" :
				{
					$this->mResult = $this->mStatement->$fqm(PDO::FETCH_OBJ);
					break;
				}
			}
			
			return $this->mResult;
		}
		
		//Get Column Count.
		//Returns the number of columns in the result set represented by the PDOStatement object otherwise returns 0.
		public function getColumnCount($pQuery)
		{
			$this->mStatement = self::$mPdoInstance->query($pQuery);
			return $this->mStatement->columnCount();
		}
		
		//Get Row Count.
		//Returns the number of rows in the result set represented by the PDOStatement object otherwise returns 0. 
		public function getRowCount($pQuery)
		{
			$this->mStatement = self::$mPdoInstance->query($pQuery);
			return $this->mStatement->rowCount();
		}
		
		//Get Error Code.
		//Returns NULL if no operation has been run on the database handle.
		public function getErrorCode() 
		{
			return self::$mPdoInstance->errorCode();
		}
		
		//Get Error Information.
		//Returns an array of error information about the last operation performed by this database handle.
		public function getErrorInfo() 
		{
			return self::$mPdoInstance->errorInfo();
		}
	
		//Get Attribute	Information Of Connection.
		//A successful call returns the value of the requested PDO attribute. An unsuccessful call returns null. 
		public function getAttribute($pAttribute) 
		{
			return self::$mPdoInstance->getAttribute($pAttribute);
		}
	
		//Set Attribute	Information For Connection.
		//Returns true on success or false on failure.
		public function setAttribute($pAttribute, $pValue)
		{
			return self::$mPdoInstance->setAttribute($pAttribute, $pValue);
		}
		
		//Initiates a transaction.
		//Returns TRUE on success or FALSE on failure.
		public function beginTransaction() 
		{
			return self::$mPdoInstance->beginTransaction();
		}
		
		//Commits a transaction.
		//Returns TRUE on success or FALSE on failure.
		public function commit() 
		{
			return self::$mPdoInstance->commit();
		}
		
		//Rolls back a transaction.
		//Returns TRUE on success or FALSE on failure.
		public function rollBack() 
		{
			return self::$mPdoInstance->rollBack();
		}			 
	}
?>


Re: PDO Database Connection Class Singleton Pattern

Posted: Sat Jun 05, 2010 10:25 pm
by Christopher
Moved to Code Critique.

Initial comment: I would prefer the settings to be properties and not defines.

Re: PDO Database Connection Class Singleton Pattern

Posted: Sun Jul 18, 2010 10:48 pm
by AbraCadaver
Just a very quick look, I'll look more later, but why the long switch in fetchData()? The string you are passing in is the same as the constant so why not pass the constant in and then:

Code: Select all

$this->mResult  = $this->mStatement->$fqm(PDO::$pFetchMode);
Or if you just want to pass in a string then:

Code: Select all

$this->mResult  = $this->mStatement->$fqm(PDO::constant($pFetchMode));
I didn't test either one so they may throw an error :cry: