Working with MSSQL and PHP

MSSQL Server (or SQL Server) is the Microsoft product of database. SQL Server 2000 has been a popular edition and with the inspiration and experience gained from it, Microsoft has released SQL Server 2005. SQL Server 2005 is also available in “light-weight” edition named, SQL Server 2005 Express Edition. This article focuses executing SQL queries and SQL Stored Procedures on SQL Server and SQL Express using PHP. This article assumes that your webserver is IIS. Any other webserver might also work.



Preparing the EnviromentIn php.ini
In order to use MSSQL support for PHP, you must enable MSSQL extension in php.ini. For this, uncomment extension=php_mssql.dll line from your php.ini.

Copying ntwdblib.dll
If you are running Apache or IIS, and having the windows version of PHP, then replace ntwdblib.dll in the php directory with the one taken from C:\WINDOWS\system32. So you should have the same ntwdblib.dll in two places. — system32 and your PHP installation folder (like C:\PHP5\). Or else, you might face difficulties in connecting with MSSQL Server.


Now the coding Part Connecting to SQL Server
Now the environment is ready. Its time to look out for the server to connect. If you don’t have SQL Server accesible to you, you can download and install SQL Server 2005 Express Edition freely available from Microsoft’s website onto your local machine and test out your scripts.

You can use mssql_connect() function to connect to SQL Server.

$dbhost=’154.25.68.15′;
$dbuser=’mssqluser’;
$dbpass=’mypass’;
$dbconn=mssql_connect($dbhost, $dbuser, $dbpass);

Connecting to SQL Express

For SQL Server 2005 Express Edition, the server runs with the service instance of “SQLEXPRESS“. So you need to connect to that service before issuing commands.You can do it like this.

$dbhost=’localhost\SQLEXPRESS’;
$dbuser=’sqlexpuser’;
$dbpass=’myexppass’;
$dbconn=mssql_connect($dbhost, $dbuser, $dbpass);

Selecting the Database
After connecting to server, you need to select a database to work on. You can do this by using mssql_select_db() function.

$mydb=’Northwind’; //sample database has been used for illustration
mssql_select_db($mydb, $dbconn); //


Executing Queries Queries can be executed using mssql_query() function.

$sql=’SELECT * FROM Categories’;
$result=mssql_query($sql, $dbconn);

Executing Stored Procedures

SQL Server is popular for support of Stored Procedures. Fortunately, PHP can also support this scenario. PHP has built in functions to execute stored procedures. Before using the procedure, you MUST initialize it by using mssql_init() and then execute the procedure using mssql_execute();

$proc=’Ten Most Expensive Products’;
$procinit=mssql_init($proc,$dbconn);
$result=mssql_execute($procinit);

For procedures involving parameters, you can use mssql_bind() to bind the variables and then execute.

$proc=’CustOrderHist’;
$procinit=mssql_init($proc,$dbconn);
mssql_bind($procinit, ‘@CustomerID’ , ‘ALFKI’, SQLVARCHAR);
$result=mssql_execute($procinit);

Note: mssql_bind() should be used on a “initalized” procedure.


Getting the resultsOnce the query or procedure is executed, the results can be extracted or “fetched” using mssql_fetch_array() function.

$row = mssql_fetch_array($result);
echo $row[‘ProductName’].’ – ‘.$row[‘Total’] ;

Cycling through the results
You can also cycle through the results and fetch the rows one after the other.

while($row=mssql_fetch_array($result))
echo $row[‘ProductName’].’ – ‘.$row[‘Total’].'<br />’;

Sample

To count the number of rows, use mssql_num_rows() function. To know the fields names, mssql_fetch_field() would be helpful. Using these two functions, the result can be displayed in a table, even if the field names are not known. Here is how we can do it.

while($res_fields=mssql_fetch_field($result))
$field_arr[]=$res_fields->name; //know the names of columns in the result.

echo ‘<table><tr>’; //start the table
foreach($field_arr as $col_name)
echo ‘<th>’.$col_name.'</th>’; //display the headings
echo ‘</tr>’;

while($row=mssql_fetch_array($result))
{
echo ‘<tr>’; //display as a row
foreach($field_arr as $col_name)
echo ‘<td>’.$row[$col_name].'</td>’; //use the column names noted previously.
echo ‘</tr>’;
}

There are many more MSSQL Server related functions which can be useful. Refer http://www.php.net/mssql.

PHP and MSSQL support has been widely available on Windows platform. This is because of the IIS relationship with SQL Server has been improved alot. For those who are passionate about PHP, but are working on Windows, this feature of PHP would be helpful.

Gửi phản hồi

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s

%d bloggers like this: