Wiki -VoIP Topics

PHP MS SQL connection



PHP is a first class citizen on Windows OS and often used with MS SQL database.
This article explains how to connect from PHP to MS SQL.

The SQLSRV PHP extension allows you to access Microsoft SQL Server database when running PHP on Windows.

The SQLSRV extension can be used on the following operating systems:
-Windows Vista Service Pack 2 or later
-Windows Server 2008 Service Pack 2 or later
-Windows Server 2008 R2
-Windows 7 

Download the Microsoft Drivers for PHP for SQL Server from here.

Once installed, specify the directory location of the driver file in the PHP configuration file (php.ini), using the extension_dir option. For example: if you will put the driver file in your c:\php\ext directory, use this option: extension_dir = "c:\php\ext"

To enable the SQLSRV driver, modify php.ini by adding the following line to the extension([ExtensionList]) section, or modifying the line that is already there: 
extension=php_sqlsrv_X_ts.dll (X is your PHP version)
 
You will need to install also the Microsoft ODBC Driver for SQL Server, which provides native connectivity from Windows to MSSQL Server. Download from here.

Restart your webserver.

Check if your MSSQL Server is running (at windows services look for the "SQL Server" service), if not start it. By default it is listening on the TCP port 1433, but if somehow you wish to change its port, you can perform it from SQL Server Configuration Manager/ SQL Server Network Configuration/ Protocols/ TCP/IP (your changes will have effect just after you will restart the service).

In order to avoid the command line usage for SQL Server management it is recommended to install the SQL Server Management Studio.
Download Microsoft SQL Server 2012 Express from here. (You can also use MS SQL 2016 or other versions).

This client tool will allow you to easily create/modify/delete databases, tables or perform any SQL statements.

In case that you are a novice in SQL field, I propose to have a look at this following tutorial.

Code example:
Below you can find an example, how to connect from PHP to a MSSQL server specifying the username and password and displaying from the "mserver" database, "tb_users" table the username and credit of the user with id=12 (the texts after // are comments):

<?php
$serverName = "Testserver";
//Testserver is the serverName and the port number is not specified, because the SQL server is listening on the default 1433 port
//In case that the port of SQL server would be 2255, it should be specified like this: $serverName = "Testserver, 2255";
$connectionInfo = array( "Database"=>"mserver", "UID"=>"sa", "PWD"=>"testXyZ987"); //database name, username, password
$conn = sqlsrv_connect( $serverName, $connectionInfo); //open a connection to the mserver database
if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}

$sql="SELECT username,credit FROM tb_users WHERE id=12"; //the query which will be executed
$stmt = sqlsrv_query($conn, $sql); //prepate and execute the query

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

if( sqlsrv_fetch( $stmt ) === false) {    // make the row of the result available for reading
     die( print_r( sqlsrv_errors(), true));
}


$username = sqlsrv_get_field( $stmt, 0); //get the first column of the row. Indexes start at 0 and must be retrieved in order.
echo "Username: ".$username; //display the value of the first column
echo "<br>";
$credit = sqlsrv_get_field( $stmt, 1); //get the second column of the row
echo " Credit: ".$credit; //display the value of the second column
?>




View Topic History
Home   |   Software   |   Solutions   |   Services   |   Support   |   Company