PDO Database Connection Class Singleton Pattern

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

Post Reply
shridharsomani06
Forum Newbie
Posts: 1
Joined: Mon May 17, 2010 8:55 am

PDO Database Connection Class Singleton Pattern

Post 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();
		}			 
	}
?>

User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: PDO Database Connection Class Singleton Pattern

Post by Christopher »

Moved to Code Critique.

Initial comment: I would prefer the settings to be properties and not defines.
(#10850)
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: PDO Database Connection Class Singleton Pattern

Post 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:
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply