Connecting PHP to MS SQL via ODBC

The popularity of Java and .NET these days has relagated the lonely ODBC on the sidelines. To heck with ODBC? It is inherently slow and besides newer technologies for database connectivity are all over the place. In fact, they come bundled with the platform or framework or the SDK you are using.

Problem arises when you are not so free to use those fancy technologies. Now, for all its sake, read further as this one actually happens to a programmer I know and you might get a chance to experience the same as a working programmer.

Jeff was asked by his IT Department Head to develop an application to generate reports against an existing in-house developed HR Application with MS SQL Server at the backend. The reports will be used by Department Heads accross the office over their local area network. He is not to install anything on the already slow client machines.

Building an Intranet Solution around PHP is a natural approach for Jeff considering his experience of the language. He searched through the manual learned about the MS SQL Functions in PHP. This one is cool! The only drawback is he doesn’t want to install any third party library on the Server.

Using the pre-intalled PHP ODBC Functions seem to be a sensible alternative.
In this article, I will show you how to successfully connect PHP to an MS SQL Server using ODBC. We will use the undying Northwind Database that comes with the MS SQL Server installation.

If you have not done this before, the only really challenging task is to correctly an ODBC data source. Once this is done, the rest is easy. What follows is visual tour showing you how to establish your ODBC Connection to the Northwind Database.

STEP 1. Launch the Windows Admistrative Tool Panel

Here is a sample screen from Windows 2000 Professional.

Select and double click on the Data Sources Icon. You will then be taken to the next screen.

STEP 2. Select SQL Server

Really scroll down on the list until you see the SQL Server item. You have to select that one to be able to connect to MS SQL Server later. Click on the Finish Button, then you will be taken to the next screen.

STEP 3 . Create Northwind Data Source Name (DSN)

The parameters that you enter on the input boxes are very important. In the Name, put NorhtwindDSN. This name will be used later in the actual PHP Script. Next, specify the name of the Server where MS SQL is actually installed. In this case, I am using a local server, so I simply put (local) on the drop-down combo. The Description Field is actuall optional. When you are done, click on the Next Button.

STEP 4. Authentication

For simplcity, let us use Windows NT Authentication. This means that when you are logged to the Windows Server, you can also access MS SQL Server. Click on the Next Button.

STEP 5. Select A Database

Ensure that you are using the Northwind Database by selecting it from the drop-down list. Then, click on the Next Button.

STEP 6. Test The Connection.

From here, we can already test the ODBC Setup. Clicking on the Test Data Source Button will show you whether or not you have successfully setup the connection.

STEP 7. The Final Touch

We really have nothing to do here except click on the OK Button. But, before doing that, see to it that the name NorthwindDSN appers on the list. If it does, you are ready for the actual PHP Code.

The PHP Code

< ?php

$dsn = 'NorthwindDSN';
$dbuser = 'sa';
$dbpass = '';

$dbcon = odbc_pconnect($dsn, $dbuser, $dbpass);
if ($dbcon){
   $sql = ' SELECT ProductID, ProductName'
         . ' FROM Products '
         . ' ORDER BY ProductName';
   $rst = odbc_exec($dbcon, $sql);
 
   while ($row = odbc_fetch_object($rst)){
       print "$row->ProductID :: $row->ProductName";
   }
}
?> 

That’s it. Try and run it yourself. It should display on the browser the ProductID and ProductName values from the Products Table.

Please be warned that this is just a quick-and-dirty way of showing you how to connect PHP to MS SQL. For one, I am using the sa user account without any password. It works fine in my local box. But if you do this on a production server, be prepared to take some of those headache pills.

There are much better programming techniques to use other than this. You, for example, use a Database Wrapper Class to have all database manipulation operations collected under one class. I leave it all to you to search for those techniques.