Web Development

Matches made in heaven: Integrating PHP with database servers

Here's a walk through connecting PHP to a back-end database using database-specific PHP functions or via ODBC.

One of the more impressive features of PHP is its near-seamless integration with almost any database server—through either database-specific PHP functions or PHP's ODBC connectivity functions. Laid out a bunch of money for Oracle? No problem. Have Microsoft SQL Server running for some reason? Go on, connect it up. Using the ultra-fast MySQL or ever-popular PostgreSQL? You're in luck. A simple compilation option (or DLL installation, if you're on Windows) and you're good to go, with a wealth of functions ready and waiting for you.

In this article, I'll introduce you to the process of interfacing PHP scripts with the database of choice. We won't go in-depth into the functions for each database type—those can be found in the PHP Manual, in the "Function Reference" section. Just look for your specific database to which there are PHP functions for connecting among the following common database servers:

Remember, PHP is Open Source, so if there's a function you're just dying to have, just jump in and create it (or ask a developer very nicely to do so and make a good case for including it).

The configuration process
Before setting out to make PHP recognize your choice of database server, make sure you have a working database server to which you can connect via your shell or DOS prompt. Then, if you haven't already created a user for your system, do so, and fiddle around with it a little bit to make sure it works. You will know that if everything's working without using PHP you can eliminate the "is it PHP or is it my database server?" question that may arise should your later configuration with PHP not work.

It's also important to have working database server when building PHP with database support because several database-related configurations require you to point directly at the database installation directory, and, if they're not there first, nothing will work out—not even your PHP build. So bear all of those things in mind and get ready to start. The process of building is not so bad, actually, and on a Windows system, others will have already done the difficult part for you.

Configuring on a Windows system
Unless you actually compile PHP for Windows yourself, you must rely on the kindness of the PHP Development Team and other developers, to provide the DLL files required to activate specific database functions. In PHP4, MySQL and ODBC support is already built-in to the distribution. You don't have to do a darn thing—no additional DLL files necessary and no modifications required to your php.ini file.

Starting with PHP4.0.1, the DLL file for Microsoft SQL Server support is also included in the distribution. However, in this case you have to do a few things: move the php_mssql70.dll file to your extensions directory, then find a chunk of text in your php.ini file that starts with
;Windows Extensions

This is the area where you uncomment the extensions you want to use, or add one to the list if it doesn't already exist. "Uncomment" in this case means "tell PHP to use it". So, to use the php_mssql70.dll file, you'd add a line like this:
extension=php_mssql70.dll

You can check your work by using the phpinfo() function to display information about your installation. Simply create a file called phpinfo.php (or whatever file extension you use on your system) and in it type
<? phpinfo(); ?>

Place the file in the document root of your Web server, and access it via your Web browser: http://yourdomain/phpinfo.php You should see a pre-formatted page providing a long list of configuration and environment variables, and hopefully will include the database support you want.

Configuring on a Linux/UNIX system
When you build the PHP module (or CGI binary) on a Linux/UNIX system, it is necessary first to run the ./configure script to check your system and prepare your Makefiles. At this time, simply use configuration flags to enable or disable specific elements of PHP, such as —enable-track-vars (enable variable tracking) or —disable-debug (compile without debugging symbols). It's at this time that you specify the database server(s) you want to use with PHP, usually by the —with-[some db name] flag.

When you configure PHP to use a particular database, you're really saying "activate and build those functions that I'll need in order to interface with [your database type here]". Here are some example configuration options:
  • To use Informix, use —with-informix=[install directory]
  • To use InterBase, use —with-interbase=[install directory]
  • To use mSQL, use —with-msql=[install directory]
  • To use MySQL, use —with-mysql=[install directory]
  • To use Oracle 8, use —with-oci8=[Oracle home directory]
  • To use PostgreSQL, use —with-pgsql=[install directory]
  • To use Sybase, use —with-sybase=[install directory]

If you want to use ODBC functions, you have a few options, depending on what you want to install, such as iODBC, unixODBC, and so on. Review all your options by executing ./configure —help, which will show the PHP configuration help file.

After the configuration process is finished, it's the usual "make" and "make install" to get your PHP module or binary built with the proper database support. After PHP is up and running, you can check your work by using the phpinfo() function to display information about your installation. Simply create a file called phpinfo.php (or whatever file extension you use on your system) and in it type:
<? phpinfo(); ?>

Place the file in the document root of your Web server, and access it via your Web browser: http://yourdomain/phpinfo.php You should see a pre-formatted page providing a long list of configuration and environment variables, and hopefully will include the database support you want.

Making a connection
Once PHP is properly configured and your database server is running, you're ready to make a connection using PHP. Since this article is an overview of working with databases in PHP, it assumes you have a username and password all set up for your database and you know a little bit about SQL.

No matter which database you're using, the sequence of functions is similar:
  1. Connect to database server (or login)
  2. Select a specific database on the server (if necessary)
  3. Issue a SQL query
  4. Receive a result
  5. Do something with the result (usually some formatting)
  6. Free result resources and close the connection (optional)

The PHP developers have done a good job of trying to standardize the function names across different database servers, within the bounds of the database server API. For example, the initial connection function for MySQL is mysql_connect(), while for Microsoft SQL Server it's mssql_connect(). Oracle is slightly different, as it uses OCILogon() for the initial connection.

Let's take a closer look at some basic connection code for a few database types: MySQL, PostgreSQL, Microsoft SQL Server and Oracle 8, and find the similarities and differences.

First, MySQL:
<?
// open connection
$connection = mysql_connect
   ("servername", "username", "password")
     or die("Couldn't make connection.");
?>

Next, PostgreSQL:
<?
// open connection
$connection = pg_connect
   ("host=Hostname dbname=DBName
   user=Username password=Password")
     or die("Couldn't make connection.");
?>

Some Microsoft SQL Server for good measure:
<?
// open connection
$connection = mssql_connect("servername", "username", "password")
      or die("Couldn't make connection.");
?>

Finally, a little Oracle:
<?
// open connection
$connection = ocilogon("username", "password")
      or die("Couldn't logon");
?>

Notice a pattern?

In all of these instances, the $connection variable will contain a link or connection identifier—if the connection was established sucessfully. If the initial connection fails for some reason, the die() function will print the "Couldn't make connection." message, or whatever you decide to use.

Usually, the next step is to select a particular database on the server to which you've just connected. Unless, of course, you've done that in the initial connection; the PostgreSQL connection example has the "dbname=DBName" parameter in the connection function, and the PHP connection to Oracle uses the environment variables ORACLE_SID or TWO_TASK to determine the target database for the given connection.

So, for MySQL and Microsoft SQL Server, you need one more step:
<?
// selecting a database in MySQL
$db = mysql_select_db("DBName", $connection)
      or die("Couldn't select database.");
?>
 
<?
// selecting a database in MS-SQL Server
$db = mssql_select_db("DBName", $connection)
      or die("Couldn't select database.");
?>

They look remarkably similar. Note the use of the $connection variable within the *_select_db() function. These functions are essentially saying "Look to the open connection identified by $connection, and select the database "DBName" within that connection."

Issuing queries
Before getting all crazy with sample code, assume that your database contains a table called "systems". In this table, you have a single field called "os". I'll use this simple table, shown in Figure A, as the basis for the example query and results-gathering.

Figure A


The goal will be to issue a SQL statement that returns this list of systems, ordered by the "os" field. This would put FreeBSD first and Win NT last, in the alphabetical order.

The basic SQL statement would be "SELECT os FROM systems ORDER BY os ASC". Now we need to get that query to the database somehow. First, stick the statement in a variable, so the code looks clean and easy to follow:
$sql = "SELECT os FROM systems ORDER BY os ASC";

MySQL, Microsoft SQL Server and PostgreSQL use a simple one-step query function, while Oracle uses two functions to accomplish the task (one verifies the query is valid, the other executes the query). The idea is the same, however: get the statement to the server.
<?
// issue query in MySQL
$sql_result = mysql_query($sql, $connection)
      or die("Couldn't execute query.");
?>
 
 
 
<?
// issue query in MS-SQL Server
$sql_result = mssql_query($sql, $connection)
      or die("Couldn't execute query.");
?>
 
 
<?
// issue query in PostgreSQL
$sql_result = pg_exec($connection, $sql)
      or die("Couldn't execute query.");
?>
 
 
<?
// issue query in Oracle 8
$sql_statement = ociparse($connection, $sql)
      or die("Couldn't parse query.");
 
ociexecute($sql_statement)
      or die("Couldn't execute statement.");
 
?>

If your functions didn't fail, then you should have a valid result identifier ($sql_result) for the MySQL, Microsoft SQL Server and PostgreSQL examples. This result identifier will be used in the next step, to process what's actually _in_ the result. With the Oracle 8 statement, you have essentially the same thing, just without an explicit name. While $sql_result variable holds a concrete result identifier, the result of the Oracle statement execution is held internally, in a result "buffer". When you paying a lot of money for Oracle, you get "automagical" features like result buffers (and commit and rollback transaction functions, but that's a different story).

Getting results
After the queries are executed, you'll have result identifiers hanging around the ether, waiting to have their contents extracted and displayed. This next set of functions shows how this is done.

With MySQL and Microsoft SQL Server, there's a "fetch array" function, that does just that: gets all the results in an associative array, which you can easily loop through when displaying results. The data in these arrays are stored using both numerical indices (0, 1, etc.) and using the field names as the keys (in this case, one key: "os"). Here are the relevant examples:
<?
// Using MySQL, loop through result set.
// Display results on their own line.
 
while ($row = mysql_fetch_array($sql_result)) {
      $os  = $row["os"];
      echo "$os <br>";
}
 
?>

 

 
<?
// Using MS-SQL, loop through result set.
// Display results on their own line.
 
while ($row = mssql_fetch_array($sql_result)) {
      $os  = $row["os"];
      echo "$os <br>";
}
 
?>

Both of these examples will produce 14 lines of text (corresponding to the 14 rows in the table), with the value of $os on each line. The HTML tag "<br>" is responsible for the line break.

The PostgreSQL example is slightly different, because one of the required parameters of the pg_fetch_array function is "row number". So, in order to tell the function, explicitly, which row you need to fetch, you must first get the total number of rows in the result set, then manually increment through the result set. Not as time-consuming or difficult as it sounds:
<?
// Get number of rows in a PostgreSQL result set
$num = pg_numrows($sql_result);
 
// start a counter
$i = 0;
 
while ($i < $num ) {
      $row = pg_fetch_array($sql_result, $i);
      $os = $row["os"];
      echo "$os <br>";
 
      // increment your counter so the row moves to the
      // next row, in the next iteration
      $i++;
}
 
?>

This example will produce the same 14 lines of text as the MySQL and Microsoft SQL Server example. Now on to the Oracle 8 example, which again puts its own twist on the fetching issue. First, you get the number of columns, because you have to count across your result set (fields) as well as down (rows). Reading rows, in this case, is achieved by using octfetch($sql_statement) within a while loop, to continue reading rows until the function returns false (no more rows).
<?
// Get number of columns in an Oracle 8 result set
 
$num = ocinumcols($sql_statement);
 
while (ocifetch($sql_statement)) {
 
      for ($i = 0; $i < $num; $i++) {
             $os = ociresult($sql_statement, $i);
             echo "$os <br>";
      }
 
}
 
?>

All of the database connectivity functions are variations on the same theme. The result of these examples is all the same:
  • FreeBSD
  • Irix
  • Linux (Caldera)
  • Linux (Debian)
  • Linux (Mandrake)
  • Linux (Other)
  • Linux (Red Hat)
  • Linux (Slackware)
  • Linux (SuSE)
  • Mac OS
  • Solaris
  • Win 2000
  • Win 95/98
  • Win NT

Final notes
This article barely scratches the surface of the database connectivity functions within PHP. For each database type supported by PHP (and through ODBC connections), there are 10, 20 even 30 or more database-specific functions already in the can. Most people will never use all of them, but they're there, and more are added as users contribute their code, knowledge and experiences with existing functions. Gotta love open source.
0 comments