| 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:
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!
|
