PHP and MSSQL

Christian Wenz

When talking about creating server-side scripts using PHP, the MySQL database often comes into play. The reason for that is historical, as the first database system PHP ever supported was MySQL. However, there are some known limitations on MySQL. Sub-SELECT queries are missing, for example, and MySQL also lacks transaction support, at least as of version 3.x. With the forthcoming version 4 (alpha version already available), some of these limitations are supposed to be fixed. A lot of other excellent database systems exist out there, many of which are supported by PHP -- PostgreSQL, for example, is currently popular.

However, the Windows world also has some fine applications to offer (if you are willing to put ideology aside, that is). Recent versions of Microsoft SQL Server offer a lot of functionality and performance, if you are willing to pay the price.

The combination of a Microsoft client with a Linux SQL server is quite well known, but the use of a Linux client with a Microsoft SQL server has received less attention. Many heterogeneous networks contain both Linux/UNIX servers and Windows servers, so accessing a Microsoft SQL server (MSSQL) from UNIX or Linux is a logical step. A recent survey in a well-respected German magazine (iX, "The Winner Is" by Christoph Gitschel, Henning Behme, Cristoph Klaja, and Andreas Pelzner) showed that the combination of PHP on Linux and Microsoft SQL beat PHP and MySQL in performance. The survey measured response time of PHP and ASP scripts that retrieved various data from a database and generated an HTML output. Even if you prefer to serve SQL from UNIX/Linux, you may still have occasion to connect to a Microsoft SQL server. For instance, if you wish to migrate the MSSQL server to, say, PostgreSQL, the migrations can be simplified by connecting directly to the MSSQL machine from the UNIX/Linux box. In this article, I will show how to connect to an existing MSSQL installation from a UNIX/Linux client. I'll also describe how to access this data source from within PHP.

Installation

Microsoft didn't develop MSSQL from scratch. Formerly, this was a Sybase product; however, Microsoft now does the sole further development for the software. To access a MSSQL database, you need special libraries. The most common ones are called FreeTDS and are available from http://www.freetds.org. The acronym TDS stands for Tabular DataStream, which is a protocol that MSSQL (and Sybase products) uses to talk to its own clients. By implementing this protocol for UNIX/Linux, these machines can connect to MSSQL, too.

FreeTDS is a set of C libraries distributed under the LGPL license, so no license fees are involved. The software is available free of charge from the FreeTDS Web site. At the time of writing, the must current version was 0.53. The first step is to download the current FreeTDS version. As this currently is 0.53, the filename is freetds-0.53.tgz; if you should be lucky enough to get a more recent version, change the filename in the following instructions accordingly.

The next step is to unpack the contents of the archive:

$ tar -xzvf freetds-0.53.tgz && cd freetds-0.53

TDS version 4.2 -- Works with all versions, however does not support new MSSQL functionality after version 6.5.

TDS version 5.0 -- When used with Sybase systems.

TDS version 7.0 -- Introduced with MSSQL 7.0, includes support for Unicode and for text fields longer than 255 characters.

TDS version 8.0 -- Introduced with MSSQL 2000; as of time of writing, only partially supported by TDS.

A general recommendation is to use the old version 4.2, as this is the lowest common denominator. If you need some of the TDS 7.0 extensions (e.g., long text fields or Unicode support), try version 7.0. Whatever version you choose, it must be provided as a parameter to configure:

$ ./configure --prefix=/usr/local/ftds --with-tdsver=4.2 --enable-dbmfix
$ make
$ make install

[ServerName]
host = 192.168.0.77   # IP of the MSSQL installation
port = 1433   # default port, modify if you chose another one
tds version = 4.2   # used TDS version
client charset = UTF-8   # only useful when using TDS version >= 7.0

$ tar -xzvf php-4.2.2.tar.gz && cd php-4.2.2

PHP can now be configured. Here you see the required configure switches for MSSQL support via FreeTDS, you may add any other switches you find appropriate (e.g., include the GD library for generating graphics on-the-fly):

$ ./configure --with-mssql=/usr/local/freetds --with-sybase=/usr/local/freetds --with-apache=../apache_1.3.26
$ make
$ make install

The PHP archive also contains a file php.ini-dist that contains the standard settings for PHP. Copy this file to /usr/local/etc/php.ini (up to version 4.0.4pl1, this file had to be put to /usr/local/lib/php.ini; in PHP 4.0.5, the default folder changed). In this file php.ini, look for "sybase.interface_file" and set this as follows:

sybase.interface_file = /usr/local/freetds/interfaces

$ cd ../apache_1.3.26
$ ./configure --activate-module=src/modules/php4/libphp4.a
$ make
$ make install

AddType application/x-httpd-php .php .php4

Testing and Working with MSSQL

To create a connection to a MSSQL database, the PHP function mssql_connect() must be used. This function expects the following parameters:

1. The MSSQL server (name, IP address)

2. The user that wants to connect to the database

3. The password for the user

All parameters are optional. The mssql_connect() function returns a numeric identifier for the connection to the MSSQL server; this value is used for other MSSQL methods later in a PHP script. One of these functions is mssql_select_db(), which selects one of the databases residing within the MSSQL server.

The connection to MSSQL is closed automatically when the script terminates (no need for manual garbage collection). However, if you want to do this by yourself, close mssql_close() and provide the connection identifier as parameter:

<?php
  $conn = mssql_connect("192.168.0.77", "sa", "topsecret");
  mssql_select_db("name_of_database", $conn);
  // do some stuff with the DB
  mssql_close($conn);
?>

To execute an SQL statement against the database, mssql_query() can be used. First parameter is the SQL statement, and the second parameter is the connection ID:

<?php
  $conn = mssql_connect("192.168.0.77", "sa", "topsecret");
  mssql_select_db("name_of_database", $conn);
  
  mssql_query("CREATE TABLE users ( id INT IDENTITY (1,1) NOT NULL, \
    name VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL)", $conn);
  
  mssql_close($conn);
?>

<html>
<head>
<title>PHP &amp; MSSQL</title>
</head>
<body>
<?php
  if (!empty($_POST["submit"])) {
    $conn = mssql_connect("192.168.0.77", "sa", "topsecret");
    mssql_select_db("name_of_database", $conn);
    $sql = "INSERT INTO users (name, email) VALUES (";
    $sql .= "'" . $_POST["name"] . "', ";
    $sql .= "'" . $_POST["email"] . "')";
    mssql_query($sql, $conn);
    mssql_close($conn);
    echo("<p>Entry added to database!</p>");
  }
?>
<form method="post">
Name: <input type="text" name="name" /><br />
Email: <input type="text" name="email" /><br />
<input type="submit" name="submit" value="Add to database" />
</form>
</body>
</html>

<html>
<head>
<title>PHP &amp; MSSQL</title>
</head>
<body>
<table cellpadding="5">
<tr><th>id</th><th>name</th><th>email</th></tr>
<?php
  $conn = mssql_connect("192.168.0.77", "sa", "topsecret");
  mssql_select_db("name_of_database", $conn);
  $result = mssql_query("SELECT * FROM users", $conn);
  while ($row = mssql_fetch_array($result)) {
    echo("<tr><td>" . $row["id"] . "</td>");
    echo("<td>" . $row["name"] . "</td>");
    echo("<td>" . $row["email"] . "</td></tr>\n");
  }
  mssql_close($conn);
?>
</table>
</body>
</html>

I have shown only a small excerpt of the MSSQL functions of PHP; however, the focus of this article was to show how to enable the connection to the MSSQL database, not how to exploit the software for maximum effect.

I concentrated on the TDS protocol because this currently is the most widely used one to cross the browser from UNIX/Linux and PHP to access Windows-based data sources. An alternative would be to use Microsoft's ODBC (Open Database Connectivity) standard. iODBC (available at: http://www.iodbc.org) is an open source implementation of the ODBC specification. PHP also supports access to ODBC data sources. For more information, see: http://www.php.net/odbc.

For installation issues and further information on FreeTDS, PHP, and MSSQL, have a look at the following Web resources:

http://www.freetds.org -- FreeTDS homepage

http://www.php.net/manual -- Online manual for PHP, including user annotations

http://www.php.net/mssql -- MSSQL functions of PHP

Christian Wenz is a Germany-based author of more than 25 books on various topics of Web development and programming. He regularly speaks at international conferences where he tries to express his loathing of stubborn ideology (beware those who do not agree with him on this topic). He can be reached at: chw@hauser-wenz.de.