Web developers must constantly cope with the pitfalls of integrating multiple platforms and technologies to produce seamless software solutions. PHP is an easy-to-learn and powerful open source scripting language. Microsoft SQL Server is a robust database product which can handle terabytes of data. It makes a great deal of sense to combine the two to create database-driven Web sites that can handle large amounts of data and traffic.
The goal of this article is to help you integrate PHP and Microsoft SQL Server. I will look primarily at two PHP functions:
- Unified ODBC functions
- MSSQL functions
The article includes a list of resources in case you want to deploy a solution hosted on the Linux platform. I've also included a section at the end to help you troubleshoot common integration problems.
The code in this article was tested using PHP version 4.3.4 running on the Apache Win32 v.2.0.48 and Microsoft SQL Server 2000 Developer Edition. The article skims over things such as SQL Server administration and security. The MSDN library is an essential resource if you need more information.
Setting up your Data Source Name (DSN)
If you want to connect to SQL Server using ODBC, you must first set up a DSN. A System DSN allows you to connect to a database server using an alias rather than writing out a long connection string. To launch the DSN wizard (Figure A), simply go to Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC).
First, select SQL Server as the driver to connect to your database (Figure B). Then select a name for the DSN (I chose "sqlserver" but you can pick any name you want). Finally, select what database you want to connect to.
|Selecting a data source name|
Next, are your security settings: In the examples in this article, I used SQL Server authentication using the default user account (username:sa, password:<blank>), as shown in Figure C. In a real world situation, you are advised to set up a new account with a strong username/password combination. Windows NT authentication can also be used but keep in mind that the database must reside on the same server as your PHP server to make it work.
Finally, you have the option to pick a default database to connect to. The examples in this article reference the Northwind database, as in Figure D, but you can easily customize the code to work with your own database.
|Selecting a default database|
Unified ODBC functions
Open Database Connectivity (ODBC) is a powerful API developed by Microsoft to allow developers to interface with any compliant database (which includes Microsoft SQL Server, Oracle, DB/2 and many others). ODBC is automatically enabled in most current PHP distributions. The complete list of Unified ODBC function methods and properties are available in the PHP manual.
Here is some sample code to connect to the default Northwind database using odbc_connect. First, you define three variables to store the DSN name, username, and password:
Then you call the odbc_connect function to connect to the database using the appropriate credentials. odbc_exec is then used to send a query to SQL Server:
$sqlquery="SELECT companyName FROM Customers;";
Finally, you extract all of the companies from the Customers table and display them onscreen in a list. Once the entire list has been displayed, the connection to the database is closed:
$companyName = odbc_result($process,"companyName");
echo "$companyName<br>"; }
PHP has a set of native functions to access Microsoft SQL Server. While the manual officially recommends that you install the extensions on the Windows platform, it is possible to make it function in Linux by using the FreeTDS libraries. Here is the entire list of properties and methods for this function.
Before you can use the mssql functions, you must enable the extensions. You can accomplish this in a few simple steps:
- Install the Microsoft SQL Client Tools on the same server as your Windows PHP installation. The tools are readily available on the SQL Server CD.
- Edit your php.ini file and uncomment (remove the semicolon from) the following line of code. If this line is missing in your php.ini, you must manually add it in:
- The php_mssql.dll file can be obtained in the PHP downloads for Win32. You must make sure that you place the dll in both the /extensions and /WINNT/system32 folder.
- Restart the Web server.
The following example is identical in functionality to the ODBC code above. The only difference is that you are using a DSN-less connection to access the database. You must specify a server and port rather than a database alias. Second of all, the methods used in this example are specific to the MSSQL function:
$sqlconnect=mssql_connect($server, $username, $password);
$sqlquery="SELECT companyName FROM Customers;";
What about Linux?
If you have PHP installed on a Linux box, there are many options available to you:
Linux ODBC Drivers: iODBC is an open source project to provide ODBC database functionality to platforms such as Linux, Solaris, FreeBSD and Apple. In conjunction with the PHP ODBC functions, you should have no problems connecting to a SQL Server. UnixODBC also produces Linux database drivers. Alternatively, if you are looking for a robust commercial solution OpenLink has designed Linux drivers for ODBC, JDBC, OLE DB, and ADO.NET.
Sybase & TDS: Sybase and Microsoft SQL Server both support the Tabular Data Stream (TDS) protocol. You can connect PHP to SQL Server by using the open source FreeTDS libraries. Here is a great tutorial covering this topic in detail.
Troubleshoot your SQL Server connection
What should you do if you get errors and are unable to connect to your SQL Server? Here are some guidelines to help you troubleshoot most of the connectivity problems you might encounter:
Configure SQL Server correctly: Before you try running queries on the server using PHP, try out your queries locally using the Query Analyzer. If your queries aren't yielding the desired results, then you have to go back and check your table and field names, structure, and so forth.
Test SQL Server's connections: You can verify that SQL Server is accepting connections using telnet. In a command window, type the following:
telnet <sql server name or ip address> 1433
If you get an error message such as "Could not open a connection to host on port 1433: Connect Failed…" then you should open the SQL Server Enterprise Manager and check the server and port setting. This problem can also be caused by a firewall or if the SQL Server service has been stopped unexpectedly. If you get a blank screen, it means that SQL Server is accepting incoming connections.
Check your PHP modules: If you get a message such as "Fatal error: Call to undefined function: mssql_connect()" it means that your MSSQL or ODBC modules have not been installed correctly. Check that the proper extension has been uncommented in your php.ini file, the appropriate dlls are copied to the /extensions and /WINNT/system32 folders, the right Linux drivers are installed, etc..
Set sufficient security privileges: If you get an error message along the lines of "Username not associated with a trusted SQL Server...", check your account permissions using the SQL Server Enterprise Manager.
Point to the correct database: Microsoft SQL Server 2000 can contain an untold number of databases. When you are setting up your DSN, you can specify a default database. If you plan to access multiple databases, you can programmatically specify the database using the mssql_select_db method.
Check your syntax: The MSSQL and ODBC functions differ in syntax—always refer to the PHP manual.