Accessing Microsoft SQL Server (mssql) from PHP under Apache on Unix or Linux

Accessing Microsoft SQL Server (mssql) from PHP under Apache on Unix or Linux

PHP is an open-source scripting language used to create database-driven web applications. PHP supports a number of database extensions that enable PHP scripts embedded within web pages to access data stored in relational databases and display the results. PHP includes ODBC support through its Unified ODBC database extension.

This tutorial shows you how to use ODBC to access remote SQL Server databases from PHP scripts running under the Apache web server.

Connect PHP to SQL Server 2000, SQL Server 2005 and SQL Server Express

When developing this tutorial, we accessed SQL Server 2000, 2005 and Express databases from PHP on UNIX and Linux. The solutions and examples described in the tutorial will also work with earlier (7.0) and later (2008) versions of SQL Server.

PHP and Linux

To access SQL Server from PHP on Linux, we used Easysoft ODBC drivers with PHP on RedHat and Ubuntu (Edgy Eft, Feisty Fawn, Gutsy Gibbon and Hardy Heron). Easysoft ODBC drivers should work with any recent 32-bit or 64-bit Linux distribution—CentOS, Debian GNU/Linux, Fedora, Kubuntu, Mandrake/Mandriva, OpenSUSE/SUSE, RedHat Enterprise Linux (RHEL), Slackware and so on.

PHP SQL Server Pre-requisites

To connect PHP on UNIX/Linux with SQL Server, we used:

• PHP 4.2 or later.
• Apache 2.0 or later.
• SQL Server ODBC driver.

PHP 4.2 or Later

To check which version of PHP you are running, use the php -v command from the shell prompt.

If you get a "command not found" error, there is no PHP on your PATH (perhaps because you have not installed it). If PHP is installed, you should see something like "4.2.2".

If you have an older version of PHP than the required one, go to http://www.php.net, and get an up to date distribution. Documentation and installation instructions can be found both in the README that is included in the distribution and at http://www.php.net/docs.php.

We tested Easysoft ODBC drivers with PHP 5.2.3 and PHP 4.2.2.

Installing PHP

We used a package manager to install PHP on our Linux client machines. A package manager is a program that installs and uninstalls software, and keeps track of the components each piece of software needs. On Ubuntu, we used the Synaptic package manager to install PHP. On RedHat, we used the redhat-config-packages package manager to install PHP from RPMs.

Installing PHP from Packages on Ubuntu

1. On the System menu, choose Administration, and then choose Synaptic Package Manager.
2. Type the administrative (sudo) password when prompted.
3. In Synaptic Package Manager, mark php5 and php5-odbc for installation.

Accept when prompted to install dependent packages (libapache2-mod-php5, php5-common and unixodbc).

4. If you want to run PHP from the command line as well as from under Apache, mark php-cli for installation.
5. Click the Apply button.

Installing PHP from RPMs on RedHat

1. Click the RedHat icon, and then click Add/Remove Applications from the System Settings menu.
2. Type the root password when prompted.
3. Add the php and php-odbc Web Server packages.
4. Click the Update button.

Testing PHP

The first PHP script you create should call the phpinfo function, as this function allows you to test that your PHP distribution is working correctly. The phpinfo function displays extensive configuration information about PHP and the system on which PHP is running. To create this PHP script, you need to do something like this:

1. Create a file called phpinfo.php.
2. Add these lines to phpinfo.php:

3.  #!/bin/bash

4.  # This is a simple one liner that amongst other things returns relevant PHP information

5.  <?

6.      phpinfo()

7.  ?>

8. Save this file, and then from the command line, type:

php phpinfo.php > newhtmlfile.html

This redirects the output from phpinfo into an HTML file called newhtmlfile.html.

Apache 2.0 or Later

When developing this tutorial, we ran PHP under Apache 2.2 and Apache 2.0. To find out which version of Apache you are running, enter the following command at the shell prompt:

httpd_bin_dir/httpd -V

–Or–

httpd_bin_dir/apache2 -V

where httpd_bin_dir is the directory where the http daemon is installed. For example, on Linux, the default location is normally /usr/sbin. The -V option displays information about the Apache installation. For example:

Server version: Apache/2.2.4 (Ubuntu)

Server built:   Oct  4 2007 22:47:20

Server's Module Magic Number: 20051115:5

Server loaded:  APR 1.2.7, APR-Util 1.2.7

Compiled using: APR 1.2.7, APR-Util 1.2.7

Architecture:   32-bit

Server MPM:     Prefork

  threaded:     no

    forked:     yes (variable process count)

Server compiled with....

 -D APACHE_MPM_DIR="server/mpm/prefork"

 -D APR_HAS_SENDFILE

On Ubuntu, make sure that /etc/apache2/apache2.conf contains an entry similar to:

LoadModule php5_module modules/libphp5.so

The entry enables Apache to dynamically load the PHP module.

On RedHat, check /etc/httpd/conf.d/php.conf for this entry.

We created both .php and .phtml sample files for this tutorial. To tell Apache which module it should use to process the .php and .phtml files, we added this entry to the Apache httpd.conf file (apache2.conf on Ubuntu):

# Use the PHP module to process .php and .phtml files.

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

Note All the examples in this tutorial will work even if you do not have a web server installed. You can also run the example PHP scripts from the shell prompt.

SQL Server ODBC Driver

We used our UNIX/Linux ODBC driver for SQL Server 7.0, 2000, 2005, 2008 and Express to connect PHP to remote SQL Server databases.

1. Download the SQL Server ODBC driver for your PHP client platform. (Registration required.)

If the SQL Server ODBC driver is not currently available for your platform, check the list of ODBC-ODBC Bridge Client platforms. The ODBC-ODBC Bridge is an alternative SQL Server solution from Easysoft, which you can download from this site.

2. Install and license the SQL Server ODBC driver on the machine where PHP is installed.

For installation instructions, see the ODBC driver documentation. Refer to the documentation to see which environment variables you need to set (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH, SHLIB_PATH depending on the driver, platform and linker).

3. Create an ODBC data source in /etc/odbc.ini that connects to the SQL Server database you want to access from PHP. For example, this SQL Server ODBC data source connects to a SQL Server Express instance that serves the Northwind database:

4.  [MSSQL-PHP]

5.  Driver                  = Easysoft ODBC-SQL Server

6.  Server                  = my_machine\SQLEXPRESS

7.  User                    = my_domain\my_user

8.  Password                = my_password

9.  # If the database you want to connect to is the default

10.# for the SQL Server login, omit this attribute

Database                = Northwind

11. Use isql to test the new data source. For example:

12.cd /usr/local/easysoft/unixODBC/bin

./isql -v MSSQL-PHP

At the prompt, type "help" to display a list of tables. To exit, press return in an empty prompt line.