Please Note: This class has long been replaced with a truly object oriented solution that utilizes the sqlsrv driver. I am only leaving it here for posterity (and to embarrass myself). Use at your own peril 🙂

This PHP class file is designed to take care of day to day database operations for the rare but not unheard of combination of PHP with IIS and MSSQL.

I’m not a fan of reinventing the wheel, but after extensive searching had been unable to find any existing classes that suited the needs of a WIMP combo. Not much of a surprise considering the majority of PHP use falls under the LAMP model, but it is certainly frustrating.

I hope anyone in a similar situation can find this of use. The goals are simple and straight forward:

• Reduce repetitive code and increase portability. This is an obvious goal – reusable, portable code not only saves time, but cuts down on human error (typos, misplaced values, etc.).
• Move to an object oriented model. While a function based model can accomplish many of the listed goals here, and an object oriented coding model carries many other advantages, the discussion of which is beyond the scope of this article.
• Quick implementation. To reduce clutter in pages and functions utilizing the class, class functions should accomplish common tasks with few calls as possible. To accomplish this goal, functions are created as basic building blocks with a cascading operation order in mind. This allows creation of master functions which can in turn run all of the basic building blocks for a common task with a single call.
• Block SQL injection. This is achieved by designing for and stressing the use of parameters. Used properly this will effectively stops SQL injection attempts cold.
• Robust error trapping. In the event of an error, as much information as possible should be collected and sent to webmaster. Additionally, an attempt should be made to log collected information into another remote database for easy dissemination.

Elements

Dependencies

Global Constants

The following values are used from global constants list.

class constants
{
	/*
	constants
	Damon Vaughn Caskey
	2012_12_18

	Global constants.
	*/

	/* Basic values */
	const	c_cAdmin		= "";										//Default "all access" user accounts.
	const	c_cDateF		= "Y-m-d H:i:s";							//Default date format.
	const	c_cWMName		= "Damon V. Caskey";						//Default webmaster name.
	const	c_cWMEmailIn	= "";										//Default webmaster email address (sending mail to webmaster)
	const	c_cWMEmailOut	= "";										//Default address when server sends mail.
	const	c_cVFGuid		= "00000000-0000-0000-0000-000000000000";	//False guid.

	/* Database */
	const 	c_cDBHost		= "";										//Default DB Host.
	const 	c_cDBLName		= "";										//Default DB logical name.
	const 	c_cDBUser		= "";										//Default DB user.
	const 	c_cDBPword		= "";										//Default DB password.
	const 	c_cDBEHost		= "";										//Error log DB host.
	const 	c_cDBELName		= "";										//Error log DB logical name.
	const 	c_cDBEUser		= "";										//Error log DB user.
	const 	c_cEDBPword		= "";										//Error log DB password.
	const 	c_cDBEEmailSub	= "Database Failure";						//Error email subject line.
	const 	c_iDBFetchType	= SQLSRV_FETCH_BOTH;						//Default row array fetch type.

	/* Media & Icons */
	const	c_cIconExcel	= "";										//MS Excel icon.
	const	c_cIconPDF		= "";										//PDF icon.
	const	c_cIconPPT		= "";										//MS Powerpoint icon.
	const	c_cIconWord		= "";										//MS Word icon.
	const	c_cPathImage	= "";										//Default path for image files.
}

• c_cDateF: This is the default format for dates. If you reconfigure this value, make sure it is one that MSSQL date fields will accept; they can get fairly picky.
• c_cWMEmailIn: This is the webmaster email address. It is where any alerts will be sent, so make sure to include working addresses that won’t flag an alert as spam.
• c_cWMEmailOut: Address used in the “from” tag of mails sent to webmaster.
• c_cDBHost: The connection string or IP of your primary database host (or just the one you use most often).
• c_cDBLName: Your most often used logical database name on your primary database host.
• c_cDBUser: The user account name to access your primary database host.
• c_cDBPword: The user account password to access your primary database host.
• c_cDBEHost: The connection string or IP of on alternate database host to record errors.
• c_cDBELName: Logical database name where error log table is located on error database host.
• c_cDBEUser: The user account name to access your error database host.
• c_cEDBPword: The user account password to access your error database host.
• c_cDBEEmailSub: The  e-mail subject line of alert mails sent when an error occurs.
• c_iDBFetchType: The row key fetch type. This determines how the line row array can be accessed. See here for more information.

Next, you will need to initialize the database class in whatever script you would like to use it in. This is fairly simple:

Variables

			
	public	$cDBLine		= NULL;									//Line array.
	public 	$cDBFMeta		= NULL;									//Line metadata array.
	public 	$iDBFCount		= NULL;									//Line field count.
	public	$iDBRowCount	= NULL;									//Table row count.
	public	$iDBRowsAffect	= NULL;									//Number of rows affected by an action query.
	public 	$rDBConn 		= NULL;									//Database connection ID resource.
	public 	$rDBResult		= NULL;									//Database query result ID resource.
	public 	$rDBStatement	= NULL;									//Database prepared query statement ID resource.		

The following public variables are made available upon initialization. These are set by the various function calls and will be the main means of actually extracting values returned from your database.

• cDBLine: An array of the fields (columns) found in a given table row.
• cDBFMeta: An array of metadata. This contains information on the table fields themselves (size, name, type, etc.).
• iDBFCount: Simple count of the number of fields returned from a query.
• iDBRowCount: How many rows were returned by a select query.
• iDBRowsAffect: How many rows were affected by an action query.
• rDBConn: ID of the connection to a database host.
• rDBResult: ID of the data returned from a query.
• rDBStatement: ID of a prepared query (SQL string and parameters).

Functions

db_basic_action

function db_basic_action($cQuery, $cParams, $cHost = constants::c_cDBHost, $cDB = constants::c_cDBLName, $cUser = constants::c_cDBUser, $cPword = constants::c_cDBPword)
	{
		/*
		db_basic_action
		Damon Vaughn Caskey
		2012_11_13
		
		Connect and execute an action query with single call.
		
		$cQuery: 	SQL string.
		$cParams:	Parameter array.
		$cHost:		DB Host.
		$cDB:		DB logical name.
		$cUser:		DB user.	
		$cPword:	DB password.
		*/
				
		/* Connect to DB */
		$this->db_connect($cHost, $cDB, $cUser, $cPword);
		
		/* Execute query. */
		$this->db_query($cQuery, $cParams);		
		
		/* Set rows affected. */
		$this->iDBRowsAffect = sqlsrv_rows_affected($this->rDBResult);		
		
		return $this->iDBRowsAffect;
	}

This function is designed to run simple action queries with a single call. There is no need to run a separate call to connect, prepare, and query execute. Simply pass the query, parameters and (if necessary) connection values.

Sets and returns iDBRowsAffect.

db_basic_select

function db_basic_select($cQuery, $cParams, $iLine=FALSE, $cHost = constants::c_cDBHost, $cDB = constants::c_cDBLName, $cUser = constants::c_cDBUser, $cPword = constants::c_cDBPword)
	{
		/*
		db_basic_select
		Damon Vaughn Caskey
		2012_11_13
		
		Connect, query and populate common variables from database with a single call.
		
		$cQuery: 	SQL string.
		$cParams:	Parameter array.
		$iLine:		Populate line array with first row?
		$cHost:		DB Host.
		$cDB:		DB logical name.
		$cUser:		DB user.	
		$cPword:	DB password.
		*/
				
		/* Connect to DB */
		$this->db_connect($cHost, $cDB, $cUser, $cPword);
		
		/* Execute query. */
		$this->db_query($cQuery, $cParams);
		
		/* Get row count */
		$this->db_count();
		
		/* Get field count. */
		$this->db_field_count();
		
		/* Get metadata. */
		$this->db_field_metadata();	
		
		if($iLine===TRUE)
		{
			$this->db_line();			
		}
		
		return $this->cDBLine;
	}

This function is designed to run simple select queries with a single call. There is no need to run a separate call to connect, prepare, and query execute. Simply pass the query, parameters and (if necessary) connection values.

Note the iLine parameter. If this is passed as TRUE, cDBLine will be populated with the first row from your query result. This option is very useful for times when you expect and only need a single row result; one call to this function is all you need. However, you will want to be careful with multiple rows as the row cursor will now be at the next record. This means a subsequent call to db_line() will start at the second record, NOT the first.

Returns cDBLine.

db_close

function db_close()
	{
		/*
		db_close
		Damon Vaughn Caskey
		2012_11_13
		
		Close current conneciton. Normally not needed.
		*/
		
		/* Close DB conneciton. */
		sqlsrv_close($this->rDBConn);
		
		/* Return TRUE. */
		return TRUE;
	}

Closes connection with rDBConn.

db_connect

function db_connect($cHost = constants::c_cDBHost, $cDB = constants::c_cDBLName, $cUser = constants::c_cDBUser, $cPword = constants::c_cDBPword)
	{		 
		/*
		db_connect
		Damon Vaughn Caskey
		2012_11_13
		
		Connect to database host and store reference to public variable.
		
		$cHost:		DB Host.
		$cDB:		DB logical name.
		$cUser:		DB user.	
		$cPword:	DB password.		
		*/
			
		$db_cred = NULL; //Credentials array.
	
		/* Only bother connecting to DB host if no previous connection is established. */
		if(!$this->rDBConn)
		{	
			/* Set up credential array. */
			$db_cred = array("Database"=>$cDB, "UID"=>$cUser, "PWD"=>$cPword);
									
			/* Establish database connection. */
			$this->rDBConn = sqlsrv_connect($cHost, $db_cred);		
		}
		
		/* False returned. Database connection has failed. */
		if($this->rDBConn === FALSE)
		{
			/* Stop script and log error. */
			$this->db_error("db_connect", NULL, NULL, TRUE);	
		}	
	}

Opens a connection to database host; defaults to primary host and database using connection pooling.

db_count

function db_count()
	{
		/*
		db_line
		Damon Vaughn Caskey
		2012_11_13
		
		Return number of records from query result.
		*/
		
		/* Get row count. */
		$this->iDBRowCount = sqlsrv_num_rows($this->rDBResult);	
		
		$this->db_error("db_count");	
		
		/* Return count. */
		return $this->iDBRowCount;
	}

Set and return iDBRowCount with record count from last query (rDBResult).

db_error

function db_error($cLocation = NULL, $cSql = NULL, $cParams = NULL, $bFatal = FALSE)
	{
		
		/*
		db_error
		Damon Vaughn Caskey
		2012_06_08
		
		Wrapper for sqlsrv_errors(). Record any errors into an alternate database and send email to webmaster.
		
		$cLocation:	Code location where error trap was called. Aids in dubugging.
		$cSql:		SQL string passed at time of error.
		$cParams:	Parameter array passed at time of error.
		$bFatal:	If error triggers, should process be terminated?
		*/
		
		$cMBody			= NULL;	//Mail message.
		$aErrors		= NULL;	//Errors list array.
		$aError			= NULL;	//Error output array.
		$mysqli			= NULL;	//Connection reference to DB error log.
		$query			= NULL; //Error query string.
		$stmt			= NULL;	//Prepared query reference.
		$val			= NULL;	//Array of error values.
		$cParam			= NULL;	//Individual item from parameter array.
		
		/* Get error collection */
		$aErrors = sqlsrv_errors();
		
		/* Any errors found? */
		if($aErrors)					
		{	
			/* Connect to error log database (obviously this should be an alternate DB from the one that failed). */	
			$mysqli = new mysqli(constants::c_cDBEHost, constants::c_cDBELName, constants::c_cDBEUser, constants::c_cEDBPword);
			
			/* Loop through error collection. */
			foreach($aErrors as $aError)
			{
				/*
				Ignore these codes; they are informational only:
					0:		Cursor type changed.
					5701:	Changed database context.
					5703:	Changed language setting.
				*/
				if($aError['code'] != 0 && $aError['code'] != 5701 && $aError['code'] != 5703)
				{
					
					$val[0] = $cLocation;				//Function & Line location. Manually set in execution code to aid debugging; not part of error routine.
					$val[1] = $aError['SQLSTATE'];		//Status of DB host.
					$val[2] = $aError['code'];			//Error code.
					$val[3] = $aError['message'];		//Error message.
					$val[4] = $cSql;					//SQL string (if any) attempting to be executed.
					$val[5] = NULL;						//Parameter array.
					$val[6] = $_SERVER["PHP_SELF"];		//Calling PHP file.
					$val[7] = $_SERVER['REMOTE_ADDR'];	//Client IP address.
					
					/* Dump parameter array into single string. */
					if(isset($cParams))								//Parameter array passed?
					{
						foreach($cParams as $cParam)				//Loop array collection.
						{
							$val[5] .= $cParam .", ";				//Add to error parameter string.
						}
					}
					
					/* Build mail body string */
					$cMBody .= 
					"\n Code Location: ".	$val[0]. 
					"\n SQLSTATE: ".		$val[1].
					"\n Code: ".			$val[2].
					"\n Message: ".			$val[3].
					"\n SQL String: ".		$val[4].
					"\n Parameters: ".		$val[5].
					"\n File: ".			$val[6].
					"\n Client IP: ".		$val[7].	
					"\n";
					
					/* If the error log database connection was successful, insert each error to table. */
					if (!$mysqli->connect_error) 
					{	
						/* Build query string. */		
						$query = "INSERT INTO tbl_query_errors (codel, state, code, msg, query, params, source, ip) VALUES (?,?,?,?,?,?,?,?)";
						$stmt = $mysqli->prepare($query);
						
						/* Bind parameters. */
						$stmt->bind_param("ssssssss", $val[0], $val[1], $val[2], $val[3], $val[4], $val[5], $val[6], $val[7]);
						
						/* Execute and close query. */
						if($stmt != false)
						{							
							/* Execute and close query. */
							$stmt->execute();
							$stmt->close();
						}
					}
				}
			}
			
			/* Close DB connection. */
			$mysqli->close();
			
			/* Send EMail alert. */
			mail(constants::c_cWMEmail, constants::c_cDBEEmailSub, $cMBody, constants::c_cWMEmailOut);
			
			/* If error is fatal, stop PHP execution immediately. */
			if($bFatal)
			{
				die("A fatal database error has occurred. Please contact the webmaster immediately.");
			}
			
			return true;
		}
		
		return false;	
	}

Capture, log and return any errors encountered by the various other functions. If errors are found, they will be sent to webmaster via e-mail Additionally an attempt is made to log the errors to another database.

My method is to use the MySQL database in my personal web portal to log various activities from production sites I am responsible for using an INSERT only account. This ensures I am always aware of potential issues and have organized debugging info for problems that arise. For this reason, the error function is written primarily for MySQL. It would be easy enough to alter this for your own needs.

Note the bFatal parameter. If this is set to TRUE, the entire script (i.e. the page being generated) will fail and be replaced with an alert. This is less than user friendly, but in some cases may be a preferable result.

db_execute

function db_execute()
	{
		/*
		db_execute
		Damon Vaughn Caskey
		2012_11_13
		
		Execute prepared query.
		*/
	
		/* Execute statement. */
		$this->rDBResult = sqlsrv_execute($this->rDBStatement);
		
		/* Error trapping. */
		$this->db_error("db_execute");
		
		/* Set rows affected. */
		$this->iDBRowsAffect;
		
		/* Return ID resource. */
		return $this->rDBResult;		
	}

Executes rDBStatement, then populates and returns rDBResult with the results. Also sets iDBRowsAffect with the number of rows affected by execution. Useful for efficiently running a single action query where only the parameters (if anything) change.

db_field_count

function db_field_count()
	{
		/*
		db_field_count
		Damon Vaughn Caskey
		2012_11_13
		
		Get number of fields from query result.
		*/
		
		/* Get field count. */
		$this->iDBFCount = sqlsrv_num_fields($this->rDBResult);
		
		/* Error trapping. */
		$this->db_error("db_field_count");
		
		/* Return field count. */
		return $this->iDBFCount;
	}

Sets and returns iDBFCount with the number of fields returned by rDBResult.

db_field_metadata

function db_field_metadata()
	{
		/*
		db_field_metadata
		Damon Vaughn Caskey
		2012_11_13
		
		Fetch table rows metadata array (column names, types, etc.).
		*/
		
		/* Get metadata array. */
		$this->cDBMeta = sqlsrv_field_metadata($this->rDBResult);
		
		/* Error trapping. */
		$this->db_error("db_field_metadata");
		
		/* Return metadata array. */
		return $this->cDBMeta;
	}

Sets and returns cDBMeta with field meta data from rDBResult.

db_line

function db_line($iFetchType = constants::c_iDBFetchType)
	{
		/*
		db_line
		Damon Vaughn Caskey
		2012_11_13
		
		Fetch line array from table rows.
		
		$iFetchType: Row key fetch type
		*/
		
		/* Get line array. */
		$this->cDBLine = sqlsrv_fetch_array($this->rDBResult, $iFetchType);		
				
		/* Error trapping. */
		$this->db_error("db_line");
		
		/* Return line array. */
		return $this->cDBLine;
	}

Sets and returns cDBLine with row array data from rDBResult. This is the function you would place inside of a while loop to fetch the entire row list from a record set.

db_prepare

function db_prepare($cQuery, $cParams, $cOptions = array("Scrollable" => SQLSRV_CURSOR_STATIC))
	{
		/*
		db_prepare
		Damon Vaughn Caskey
		2012_11_13
		
		Prepare query statement.
		
		$cQuery: 	Basic SQL statement to execute.
		$cParams:	Parameters to pass with query (prevents SQL injection).
		$cOptions:	Options for cursor array, etc.
		*/
	
		/* Execute query. */
		$this->rDBStatement = sqlsrv_prepare($this->rDBConn, $cQuery, $cParams, $cOptions);
		
		/* Error trapping. */
		$this->db_error("db_prepare", $cQuery, $cParams);
		
		/* Return query ID resource. */
		return $this->rDBStatement;		
	}

Prepare a query statement from SQL string and parameters, then place into rDBStatement. Use this function to prepare action queries you need to execute repeatedly.

db_query

function db_query($cQuery, $cParams, $cOptions = array("Scrollable" => SQLSRV_CURSOR_STATIC))
	{
		/*
		db_query
		Damon Vaughn Caskey
		2012_11_13
		
		Prepare and execute query.
		
		$cQuery: 	Basic SQL statement to execute.
		$cParams:	Parameters to pass with query (prevents SQL injection).
		$cOptions:	Options for cursor array, etc.
		*/
	
		/* Execute query. */
		$this->rDBResult = sqlsrv_query($this->rDBConn, $cQuery, $cParams, $cOptions);
		
		/* Error trapping. */
		$this->db_error("db_query", $cQuery, $cParams);
		
		/* Return query ID resource. */
		return $this->rDBResult;		
	}

Prepare a query statement from SQL string and parameters, then execute immediately. Populates and returns rDBResult with result. This function is useful when you want to run a single query with no functional add-ons.