|
9/24/2003 -- One of PHP’s strongest features as a scripting language for Web applications is its diverse database support: the “What Can PHP Do?” page lists over 20 database platforms that PHP can communicate with. The PHP-MySQL combination powers numerous high-profile Web sites worldwide. However, accessing other database platforms with PHP is occasionally frustrating, since the PHP documentation falters in less traveled areas, leaving it to the extensive user community to fill in the gaps.
In this article, we’ll introduce PHP’s database connectivity capabilities, access a Microsoft SQL Server 2000 database from Linux using PHP and some freely available utilities as a test case, and run a basic database query.
Building PHP
PHP can manipulate file-based databases, including the Berkeley Database, the GNU Database Manager, and cdb (constant database) formats. These are supported by PHP’s database abstraction layer (dba), a set of generalized functions for extracting information from file-based databases. The dba functions are useless, however, unless you choose one or more databases at compile time. The supporting libraries for the databases you specify must already be present on the system—otherwise the compilation will fail, and you’ll end up with an installation of PHP that can’t access any databases, except in theory. The following configuration line adds support for the Berkeley Database (version 4) to our PHP installation, and assumes that the Berkeley DB application and libraries already reside in the directory /usr/local/db:
./configure --with-db4=/usr/local/db
Multiple databases can be specified in the configure command. If, like most people, you’re using PHP in conjunction with Apache, and have built PHP as a Dynamic Shared Object (DSO), you can add additional database support later on by simply rebuilding PHP; however, if you’re not using dynamic modules, you might end up rebuilding Apache entirely. If all this sounds complex, not to worry—there’s a substantial PHP user community, and there are numerous installation kits that simplify the process (in fact, a Google search for “php installation kits linux” turns up several results, including this one). Most Linux distributions include their own build of PHP, as well, so that’s the first place to check.
Database Connectivity
File-based databases are stored on local disk space, and can be accessed without traversing the network. Full-fledged databases such as Oracle, however, typically reside on a dedicated server, and can only be accessed via the network. This raises issues of security as well as method:
- What communication protocols does the database support? This question refers to the language(s) the database uses to communicate, or the session, presentation, and application layers of the Open Systems Interconnect (OSI) model, generally speaking. There’s a standard query language to which most database products conform, called SQL (technically, the name is not an acronym, though it’s often assumed to stand for “Structured Query Language” or “Standard Query Language”). Most vendors extend the SQL language, to make their product more attractive. Thus, SQL Server 2000 uses a superset of the SQL language called Transact-SQL, Oracle uses PL-SQL, and so on.
- What network protocols can be used to contact the database? This question refers to the network and transport layers (3 and 4) of the OSI model. Our test case, SQL Server 2000, supports several network protocols, including the outdated NetBEUI, Novell’s IPX/SPX, and the most important candidate, TCP over IP, which we abbreviate to TCP/IP.
- Can the database be accessed securely, using some form of encryption?
Full-fledged databases, rather than being supported by the database abstraction layer already described, have their own supporting PHP module(s), which provide more specialized functions for interacting with the database. Similarly, these modules must be specified at compile time and the prerequisite applications installed; otherwise only PHP’s built-in MySQL support will be available in the final PHP build.
Database Clients: Microsoft SQL Server 2000
The OSI model, as a generalized reference model of network architecture, doesn’t always fit neatly onto real-world applications. As this Windows FAQ entry on SQL Server shows, the way in which SQL Server communicates with its clients is actually much more complex than this space will allow. Nonetheless, the OSI model is a useful rubric. For our purposes, the important point is that we plan to write queries using SQL, or some superset of SQL; we need to find a database client that can perform the remainder of the work—opening the network connection, sending the query, retrieving the data, and so on. All we care about is the data.
To include support for a particular database in PHP, the database client must be installed somewhere on the system. In some cases, this might be a commercial client, such as those provided by Oracle or Sybase, and installation and configuration is as simple (or as complex) as following the vendor’s instructions. In other cases, source code for the database client might be available (MySQL, PostgreSQL, etc.), and it might be necessary to compile the software yourself or locate a precompiled binary for your system on the Internet. In our sample case, we need a SQL Server 2000 client for Linux. Since Microsoft isn’t on the best of terms with the Linux community, it’s doubtful that we’ll find a suitable product straight from the vendor, so we’ll have to look elsewhere.
SQL Server happens to use the Tabular Data Stream (TDS) protocol to communicate with its clients (and as already mentioned, can transmit TDS messages over TCP/IP). According to the TDS reference (PDF format) published by Sybase, the TDS protocol resides at the application and presentation layers of the OSI model -- meaning that it basically insulates SQL programmers from having to deal with the lower-level tasks such as opening and closing TCP/IP connections cleanly and allows them to focus on optimizing their queries -- so this sounds like what we need.
Sybase also uses a version of the TDS protocol, and in point of fact Microsoft acquired both the Transact-SQL and TDS technologies from Sybase. Though commercial implementations of the TDS protocol are available via clients such as the Sybase Open Client or Microsoft’s own (Windows-based) utilities, an Open Source implementation of the TDS protocol for Unix systems is available: FreeTDS. Configuration and compilation of FreeTDS is straightforward with few prerequisites, but Linux packages (RPM format) are available.
Assuming that the FreeTDS installation is in /usr/local/freetds, the following configuration line will add Sybase support to a pre-4.3.0 PHP installation:
./configure --with-sybase=/usr/local/freetds
In a pre-4.3.0 PHP installation, Sybase functions can be used to successfully connect to a Microsoft SQL Server database. PHP 4.3.0 (released Dec. 27 2002) added direct Microsoft SQL Server support via the following configure command. The FreeTDS libraries are still required:
./configure --with-mssql=/usr/local/freetds
After successfully compiling PHP with FreeTDS support, the database client still needs to be configured before we can use PHP to execute queries.
Configuring FreeTDS
FreeTDS configuration is simple, consisting primarily of modifications to one file -- in our case, /usr/local/freetds/etc/freetds.conf:
[global]
tds version = 8.0
try domain login = no
try server login = yes
[myserver]
host = sqlserver.your.site.com
port = 1433
The foregoing is NOT a complete freetds.conf file; rather, I’ve only included the important directives. The “global” section contains directives that pertain to all servers you hope to attach to. Global settings can be modified and extended in the individual sections. Names for the individual servers (in bold, above) are arbitrary, and have no other import -- they don’t even reflect DNS names. However, they will be used in your PHP code when you issue a query.
- First, you’ll note the “tds version” directive. This specifies the version of the TDS protocol to be used (outlined here). We happen to be connecting to a SQL Server 2000, as indicated by the “8.0.”
- Microsoft SQL Server provides two methods of authentication: SQL Server authentication, which requires that a user be defined in SQL Server independent of Windows usernames, and integrated Windows authentication, which requires that a user be defined in Microsoft Windows itself. The next two directives indicate that we prefer SQL Server authentication to Microsoft Windows domain authentication. Unfortunately, this means that our authentication is occurring in clear-text, so blocking port 1433 (the SQL Server communication port) at the firewall is recommended.
A First Query
Now we’re ready to create our first SQL query. The best way to explain coding is by example, so here’s the PHP page in its entirety:
$dbh = sybase_connect("myserver","sa","password");
if (! sybase_select_db('master',$dbh))
{
echo("Database down!");
exit();
}
$q=sybase_query("
select name from sysobjects
where xtype='U'
",$dbh);
while($row=sybase_fetch_array($q))
{
printf("%s
",$row[0]);
}
?>
This PHP page, which performs a simple query to retrieve the names of all the system tables on the SQL Server 2000 installation, is divided into four relatively distinct sections (note that we’re using the Sybase functions rather than the SQL Server functions, indicating that this is an older PHP installation):
- The $dbh line opens the database connection, using the “myserver” name established previously in the freetds.conf file, followed by the username and password. All SQL Server installations have a built-in user, the “sa” or system administrator user, which has complete control over the database. Technically, it’s not a good idea to use this username for everyday tasks, so it would be a good idea to have the SQL Server administrator set up a user with more restricted access for this purpose. The same principle applies to any networked database -- grant exactly as much access as required, and no more.
- The “if” statement selects the default database. SQL Server has several built-in databases (master, model, msdb). If this operation fails, it’s probable that the database is down, or that our username and password have been disabled. In either case, the PHP engine issues an error and exits immediately.
- If we’ve successfully established connectivity, we run the query and store the output in the variable “$q.” The text in bold is our actual SQL query -- which, as you might have guessed, can only be constructed by knowing something about the structure of the database in advance. In this case, I know that SQL server has a table called “sysobjects,” that that table has at least two columns called “name” “xtype,” and that one of the values the “xtype” column can have in each row is “S,” indicating that the “name” table is a system table. A query with a value of “U” would give me the user tables, and so on. These column names and values are specific to SQL Server, of course.
- The last section of code parses the output into human-readable form. In this case, the results are a one-dimensional array, so we fetch the rows one at a time from the array and print out the first (and only) element of each. To make reading a bit easier, we print each row of output on a separate line by separating each with a break HTML tag (remember, PHP is typically used to create Web pages, so your code should generate valid HTML).
The results look like this in our browser:
sysobjects
sysindexes
syscolumns
systypes
syscomments
… and 14 more table names
It’s not a very prepossessing or important query, but it’s a start.
Moving On
SQL is a very powerful and complex language, and the various vendor extensions even more so. It’s entirely possible to make a career of nothing but database programming. For those just getting started, however, there’s still considerable value in picking up a smattering of the language. I’d suggest the following resources, in addition to the PHP manual itself:
- Microsoft SQL Server 2000 Books Online
- MySQL Documentation
- PostgreSQL Documentation
Additionally, links to the homepages for nearly all the databases supported by PHP are included on the “Obtaining PHP” section of the PHP FAQ. 
Questions? Comments? Post 'em below!
|
