Using PHP With SQL Server

In our last technical post, I covered using Visual Studio Code to debug PHP code. Today, I’m going to cover integrating with SQL Server.

Step One: Get Drivers

Assuming that you followed the instructions for setting up PHP in the link above, you have a version of PHP running in C:\xampp\php. From there, you will want to download the Microsoft Drivers for PHP for SQL Server from Microsoft. The latest version of the drivers as of this post is 5.3, which covers PHP versions 7.0 through 7.2. This means that if you are using PHP 7.3, your version of PHP does not have official Microsoft support, so stick with 7.2 if this is a greenfield project.

Download the right version of the drivers given your operating system. For this post, I’ll assume that you’re running this on a Windows development box.

After you download the drivers, run the downloaded executable and extract to some temp directory. Then, copy the php_sqlsrv_72_ts_x86.dll file to C:\xampp\php\ext\ (assuming you installed Xampp to that directory and are running PHP 7.2) and modify your php.ini file to add the following at the bottom:

extension=php_sqlsrv_72_ts_x86.dll

Once you do that, restart Apache and you should be able to access SQL Server.

Step Two: Write Code

Querying SQL Server in PHP is pretty easy. Here is some overly simplistic code to execute a stored procedure. I’m going to give you bad PHP code (seriously, who takes PHP development advice from a database guy hasn’t really written PHP in a decade?) but at least I’m not going to make it SQL injectable…

private function executeQuery($query, $returnResult){
	$dbconnection = null;
		try {
			$serverName = "MYSERVER";
			$databaseName = "MYDATABASE";
			$username = "MYUSERNAME";
			$password = "MYPASSWORD";
			$connectionOptions = array("Database" => $databaseName, "UID" => $username, "PWD" => $password);
			$dbconnection = sqlsrv_connect($serverName, $connectionOptions);

			if($dbconnection){
				$preparedStatement = sqlsrv_prepare($dbconnection, $query['query'], $query['parameters']);
				$isSuccessful = sqlsrv_execute($preparedStatement);
				
				if($returnResult &&  isset($preparedStatement) && $isSuccessful){
					$finalResults = array();
					while($row = sqlsrv_fetch_array($preparedStatement, SQLSRV_FETCH_ASSOC))  
					{  
						$finalResults[] = $row;
					}  
					return $finalResults;
				}

				return null;
			} else {
				throw new Exception('Unable to connect to database');
			}
		} catch(Exception $error){
			throw new Exception($error);
		}
		$dbconnection = null;
}

In the code above, we take in a query, which has the query text and a set of parameters. We connect to SQL Server given various parameters, which you’d want to store in a secure location so please don’t copy my code verbatim; do it the right way.

If we want a result set back, set $returnResult to true. Otherwise, we don’t expect a result set back.

Given that structure, here’s how you’d call it.

private function createInsertActionLogDBQuery($logLevelID, $stackTrace, $errorMessage){
	return array("query" => "EXECUTE dbo.ActionLog_InsertActionLog
								@LogLevelID = ?,
								@StackTrace = ?,
								@ErrorMessage = ?;",
				"parameters" => array(
					array($logLevelID, SQLSRV_PARAM_IN),
					array($stackTrace, SQLSRV_PARAM_IN),
					array($errorMessage, SQLSRV_PARAM_IN)
				));
}

public function sendActionLog($logLevelID, $stackTrace, $errorMessage){
	$this->executeQuery($this->createInsertActionLogDBQuery($logLevelID, $stackTrace, $errorMessage), false);
}

We have one function call to build up our query object and another function which acts as the intermediary. That way, if we need to translate data from our app to SQL Server, we can do it in the public function without exposing that we’re sending data to SQL Server or the mechanisms behind the insert operation.

Step Three: Disclaim Knowledge

It was nice to take a jaunt back into a language I hadn’t seen in a very long time. I am by no means a fan of PHP and do not want to develop in it, but it was worthwhile spending some time to understand how things work in a non-.NET space. As I mentioned, I am absolutely not an expert in PHP, so you don’t want to take my advice on it seriously, but this process did end up working out, so I’m pretty happy about it.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s