Tags

,

WAMP stands for Windows – Apache – MySQL – PHP but for me, today, I need to say WASP which is Windows – Apache – SQL Server – PHP. Yes its bit painful and for second day, working hard on finishing my part of our project for Microsoft Imagine Cup. Our server guy, Prabath M Peiris have done the backend in MS SQL Server and I needed to connect the DB with PHP as we could not find a free ASP host.

sqlserver2008

WAMP does not come with SQL Server connectivity drivers so we have to download it from http://www.microsoft.com/en-us/download/details.aspx?id=20098 and extract it in the “ext” folder of WAMP PHP. In my PC that is (C:\wamp\bin\php\php5.4.3\ext) then we need to add the extensions to PHP.ini file. I advise you to read the readme.html file comes with SQL Server drivers we have downloaded. Since I am using PHP 5.4, I used

extension=php_sqlsrv_54_ts.dll
extension=php_pdo_sqlsrv_54_ts.dll

to add them. It differs with the PHP version. Then we need to restart all services in WAMP.

Now you can see ‘sqlsrv’ extension and ‘pdo_sqlsrv’ extension in your WAMP homepage. This means, you are ready to use PHP sqlsrv function to connect SQL Server DB in PHP.

Lets move to the coding part. Since SQL Server uses Windows Authentication and SQL Server Authentication to connect, we have two separate syntaxtes for connecting

Connection using Windows Authentication

<?php
$serverName = "serverName\sqlexpress"; //serverName\instanceName

// Since UID and PWD are not specified in the $connectionInfo array,
// The connection will be attempted using Windows Authentication.
$connectionInfo = array( "Database"=>"dbName");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
?>

Connection using SQL Server Authentication

<?php
$serverName = "serverName\sqlexpress"; //serverName\instanceName
$connectionInfo = array( "Database"=>"dbName", "UID"=>"userName", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
?>

In here, SQL Server authentication way, the user ID and password (UID and PWD connection attributes in the SQLSRV driver) must be set when you try to establish a connection. The user ID and password must map to a valid SQL Server user and password.

Okie, lets see the code to retrieve all Table names in our SQL Server DB. The code goes as

<?php
$serverName = "***********ng.com"; 
$connectionInfo = array( "Database"=>"DB_******_****arts", "UID"=>"DB_******_****arts_****admin", "PWD"=>"**********");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}

$sql = "select table_name from information_schema.tables";
$stmt = sqlsrv_query( $conn, $sql);

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

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

Here I am using

$sql = "select table_name from information_schema.tables";
$stmt = sqlsrv_query( $conn, $sql);

to make query. Please note that there are several possible ways to list the Table names, one good example is like

SELECT *
FROM sys.Tables

but this

SELECT 
table_name FROM
information_schema.tables

is the best possible solution to retrieve the table names later through

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

Thats all folks 🙂 happy coding

Advertisements