Sysadmins of the North
Share now!





PHP logo

Connect to MS SQL Server with PHP 5.3+

Connect to an 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 MS 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.

PHP logo
PHP logo

Protip: 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 5.3 and up, to an SQL Server with sqlsrv_connect() is:

<?php
/*
 * Follow me on Twitter: @HertogJanR
 * Donate: https://www.paypal.me/jreilink
 */

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!";
	// do stuff here, then close the SQL Server connection
	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 connect function for that specific version, like:

<?php
/*
 * Follow me on Twitter: @HertogJanR
 * Donate: https://www.paypal.me/jreilink
 *
 * Pseudo code for illustration only
 */

function connectMssql($hostname, $username, $dbname, $passwd) {
	if(strnatcmp(phpversion(),"5.3.2") >= 0) {
		/*
		 * PHP versie 5.3.2 or higher needs sqlsrv-driver:
		 * http://msdn.microsoft.com/en-us/library/cc296161%28SQL.90%29.aspx
		 */

		$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); }
	}
}

// execute the function
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)

This may interest you:   Check website availability with PHP and cURL

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
/*
 * Follow me on Twitter: @HertogJanR
 * Donate: https://www.paypal.me/jreilink
 *
 */

$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.


buy me a coffee
Buy Me A Coffee

About the Author Jan Reilink

My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing my daily thing at Vevida in the Netherlands. With over 15 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, WordPress, websites & optimization. Want to support me and donate? Use this link: https://paypal.me/jreilink.

follow me on:


Thank you!

Leave a Comment:

Skip to content