Windows, PHP, and MS SQL: A match made in...well...

The vast majority of PHP sites are built using a LAMP setup. For the uninitiated, this indicates a server running a Linux operating system, Apache web server software, a MySQL database, and the PHP development language. There are several reasons why this set of software is so popular, but the key ones are cost (free), security, reliability, and a wide open-source development community. However, in a recent custom project we were tasked with setting up a new intranet site which was effectively married to a Windows/IIS server running MS SQL. The site was initially built with ASP Classic/VBScript but we felt it prudent to get the site running PHP as it is a more modern language and is still being developed, unlike ASP Classic.

The SQLSRV Libraries

PHP has had drivers for MS SQL for some time, but the majority of the documentation is for the older mssql libraries which were built for PHP 4 and below. With the advent of PHP 5, Microsoft developed the sqlsrv libraries, thus changing all the previously commonplace database calls that had been used. In order to allow your Windows/PHP server to see these new options, you need to edit your php.ini file like so:

extension=php_pdo_sqlsrv_53_nts_vc9.dll
extension=php_sqlsrv_53_nts_vc9.dll

This is assuming you're using PHP 5.3 and the non-thread safe VC9 drivers -- your mileage may vary depending on your server setup. For this and other downloads, see here. Once you've made this change to php.ini, save it and restart your IIS for the changes to take effect

Interacting with MS SQL

Once your sqlsrv libraries are installed you can set about collecting data from the database. For simplicity, I like to store the database connection information as constants like so:

// DB Constants
define("DB_SERVER", "SERVER\INSTANCE");
$connection_info = array("UID" => "username", "PWD" => "password", "Database"=>"db_name", "ReturnDatesAsStrings" => 1);
define("DB_LOGIN_INFO", serialize($connection_info));

It is the constant DB_LOGIN_INFO that will be used in the custom database query function that I'll outline next. While a custom function is not necessary, it can certainly simplify things and save a lot of coding. Here is the database function:

// $sproc = stored procedure name; $params = array of parameters; $rs_count = expected number of recordsets
function db_query($sproc, $params = '', $rs_count = 1){
  $conn = sqlsrv_connect(DB_SERVER, unserialize(DB_LOGIN_INFO));
  if($conn){
	$row = 0;
	$field = 0;
	$param_options = '';
	// Build parameter string
	$new_params = array();
	if($params != ''){
	  $param_count = 1;
	  while($param_count <= count($params)){
		${'param'.$param_count} = $params[$param_count - 1];
		array_push($new_params,${'param'.$param_count});
		$param_options == ('') ? $param_options = ' ?' : $param_options = $param_options . ', ?';
		$param_count++;
	  }
	}else{
	  $param_options = '';
	}
	
	// Set up initial db query
	$query = "exec " . $sproc . $param_options;
	$stmt = sqlsrv_query($conn, $query, $new_params);
	$results = array();
	$result = array();
	// Cycle the recordsets
	for($rs = 1; $rs <= $rs_count; $rs++){
	  // Get next recordset (if more than 1)
	  if($rs != 1){
		$row = sqlsrv_next_result($stmt);
	  }
	  // Save rs data to an array
	  $row_counter = 0;
	  while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)){
		if(!is_null($row['0'])){
		  for($x = 1; $x <= count($row); $x++){
			$result[$row_counter][$x - 1] = $row[$x - 1];
		  }
		}else{
		  break;
		}
		$row_counter++;
	  }
	  // If there are valid results, add them to the array
	  if($result){
	    array_push($results,$result);
		$result = array();
	  }else{
	    array_push($results,NULL);
	  }
	}
	sqlsrv_free_stmt($stmt);
  }
  sqlsrv_close($conn);
  // Return an array of data (if necessary)
  if($rs_count >= 1){
    return $results;
  }
}

With this function in place, making a call to a database and retrieving data (SELECT) or modifying data (INSERT/UPDATE/DELETE) is as simple as one line of code. There are a few parameters passed to the function db_query: stored procedure name, an array of variables, and the number of expected recordsets to be returned (in this case, the function is an insert so we expect zero recordsets.)

db_query('sproc_session_save',array($sessionid,$data),0);

A Note on Stored Procedures

The above function was written with stored procedures in mind -- while it's not required it is good practice for the sake of both speed and security. Stored procedures are pre-compiled so they do not need to be evaluated at runtime unlike inline SQL, and since they accept only parameterized input they are not subject to SQL injection attacks that are so prevalent these days.