Connect to a Microsoft SQL Server database with PHP 5.3+ using the SQLSRV API and sqlsrv_connect. As of PHP 5.3.2 you have to use the SQLSRV API functions to connect to an Microsoft SQL Server database from PHP. For example, use sqlsrv_connect() to create a connection resource and open a connection. The main difference with the older mssql functions of PHP is that SQLSRV requires an Array() with connection information, instead of strings.

Starting with PHP version 5.3.2, PHP dropped support for the MSSQL Functions. You now have to rely on a new driver created by Microsoft: SQLSRV. In the article mentioned below you'll learn how to install the SQLSRV driver and PHP extension, this article showcases how you connect to an SQL Server database with PHP.

Learn how to install the SQLSRV driver and PHP extension on Windows Server.

PHP connect to SQL Server

An example you can use to connect from PHP to SQL Server with sqlsrv_connect() is:

<?php


function connectMssql($hostname, $username, $dbname, $passwd) {
	$connectionInfo = array("UID" => $username, "PWD" => $passwd, "Database" => $dbname);
	$link = sqlsrv_connect($hostname, $connectionInfo);
	if ($link) {
		return TRUE;
	} else {
		return FALSE;
	}
}
 
if(connectMssql("sql.domain.tld", "user", "deebaase", "passw0rd")) {
	echo "We're connected!";

	sqlsrv_close($link);
}
?>

Support for multiple PHP versions

Suppose you want to support both older and newer versions of PHP. You can use phpversion() to check the version you're currently using. Then use the SQL Server sqlsrv/mssql_connect() function for that specific version, like:

<?php


function connectMssql($hostname, $username, $dbname, $passwd) {
	if(strnatcmp(phpversion(),"5.3.2") >= 0) {
		

		$connectionInfo = array("UID" => $username, "PWD" => $passwd, "Database"=> $dbname);
		$link = sqlsrv_connect($hostname, $connectionInfo);
		if ($link) {
			return TRUE;
			sqlsrv_close($link);
			exit;
		}
	}

	else {
		$link = mssql_connect($hostname, $username, $passwd);
		if ((!$link) || (!mssql_select_db($dbname, $link))) {
			return FALSE;
		}
		else { return TRUE; }
		if ($link) { mssql_close($link); }
	}
}


connectMssql("sql.domain.tld", "user", "deebaase", "passw0rd");
?>

SQLSRV Driver API Reference

You can find more information on the SQLSRV API on Microsofts MSDN website: SQLSRV Driver API Reference (Microsoft Drivers for PHP for SQL Server)

List all table names in an SQL Server database - sqlsrv_query() show tables example

You can use this PHP example, using sqlsrv_query(), to perform a SHOW TABLES query:

<?php


$connectionInfo = array("UID" => "db-user", "PWD" => "passw0rd", "Database" => "deebaase");
$link = sqlsrv_connect("sql.domain.tld", $connectionInfo);
$sql = "SELECT table_name FROM information_schema.tables";

$stmt = sqlsrv_query( $link, $sql);
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
	echo $row['table_name']."<br />";
}

if( $stmt === false ) {
	die( print_r( sqlsrv_errors(), true));
}
?>

sqlsrv_query - Prepares and executes a query.

Conclusion connecting PHP 5.3+ to Microsoft SQL Server

In this article you learned you no longer can use mssql_connect() in PHP 5.3 and up. You have to use a newer driver and API called SQLSRV with, for example, sqlsrv_connect() function. The main difference with the older mssql functions of PHP is that SQLSRV requires an array with connection information instead of strings.

1 Comment

Comments are closed