PHP —

Interacting with Other DBMSs Using PHP

Interacting with other relational DBMSs is similar to using MySQL. In this section, we outline the key functions to access Microsoft SQL Server, ODBC-compliant, Oracle, and PostgreSQL DBMSs. We illustrate how to interact with different DBMSs by presenting four rewritten versions of Example 4-1 that include different DBMS functionality.

Functions for accessing other databases, including Informix and Sybase, can be found in the PHP manual. For DBMSs that are not supported natively by PHP, ODBC can usually be used; we discuss ODBC later in this section.

Microsoft SQL Server

Similarly to the MySQL function library, there are many functions for connecting to, querying, and extracting results from Microsoft SQL Server DBMSs.

SQL Server can be used under the Microsoft Windows operating system by making minor changes to THE configuration of PHP in the php.ini file; these changes are discussed in the online PHP manual. SQL Server can also be accessed from a Linux platform by installing the FreeTDS package available from http://www.freetds.org and recompiling PHP with the -with-sybase option; this enables both Sybase and SQL Server support. SQL Server databases can also be accessed using the ODBC library discussed in the next section.

Six functions are listed here, and Example 4-12 shows these implemented in a modified version of Example 4-1.

resource mssql_connect(string host, string username, string password)

Establishes a connection to a SQL Server DBMS. On success, the function returns a connection resource handle that can access databases through subsequent commands. Returns false on failure.

The parameters (all of which are optional) and their use are identical to those of the mysql_connect( ) function.

int mssql_select_db(string database, resource connection)

Uses the database on the connection, where the connection is a resource returned from mssql_connect( ).

resource mssql_query(string SQL_command, resource connection)

Runs an SQL command through the connection created with mssql_connect( ) on the database selected with mssql_select_db( ). Returns a resource-a result handle used to fetch the result set-on success and false on failure.

array mssql_fetch_row(resource result_set)

Fetches the result set data, row-by-row, following an mssql_query( ) command using the result_set resource returned by the query. The results are returned as an array, and use is again identical to mysql_fetch_row( ). false is returned when no more rows are available.

int mssql_num_fields(resource result_set)

Returns the number of attributes in a result_set resource handle, where the result_set handle is returned from mssql_query( ).

int mssql_close(resource connection)

Closes a SQL Server connection opened with mssql_connect( ).

Example 4-12. Connecting to a Microsoft SQL Server database with PHP
<!DOCTYPE HTML PUBLIC
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Wines</title>
</head>
<body><pre>
<?php
<?
   // (1) Open the database connection and select the
   // winestore
   $connection = mssql_connect("localhost","fred","shhh");
   mssql_select_db("winestore", $connection);
   // (2) Run the query on the winestore through the
   // connection
   $result = mssql_query("SELECT * FROM wine",
                          $connection);
   // (3) While there are still rows in the result set
   while ($row = mssql_fetch_row($result))
   {
      // (4) Print out each attribute in the row
      for ($i=0; $i<mssql_num_fields($result); $i++)
         echo $row[$i] . " ";
      // Print a carriage return to neaten the output
      echo "\n";
   }
   // (5) Close the database connection
   mssql_close($connection);
?>
</pre>
</body>
</html>