4.4. Interacting with Other DBMSs Using PHPInteracting 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. 4.4.1. Microsoft SQL ServerSimilarly 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.
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> 4.4.2. Open DataBase Connectivity (ODBC)For DBMSs that are not supported natively by PHP—such as Microsoft Access— Open DataBase Connectivity (ODBC) functions are available to connect to, query, and retrieve results. ODBC also offers database-tier flexibility where, for example, a low-end DBMS such as Access can be replaced with a high-end DBMS such as Oracle without modifying the middle-tier PHP scripts. In addition, selected DBMSs—including IBM DB2, Adabas D, and Sybase SQL Anywhere—use ODBC functions for direct access; that is, they don't have their own function libraries but use ODBC natively as a function library. An ODBC client is required for the DBMS if ODBC is to be used. For example, MySQL can be used with ODBC by installing the MyODBC client described in Section 12 of the MySQL manual; the MyODBC client is available from http://www.mysql.com. Five key ODBC functions are listed here, and Example 4-13 shows these implemented in a modified version of Example 4-1.
Example 4-13. Connecting to an ODBC data source 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 $connection = odbc_connect("DSN=winestore","fred","shhh"); // (2) Run the query on the winestore through the // connection $query = odbc_exec($connection, "SELECT * FROM wine"); // (3) While there are still rows in the result set while ($row = odbc_fetch_row($result)) { // (4) Print out each attribute in the row for ($i=0; $i<odbc_num_fields($result); $i++) echo $row[$i] . " "; // Print a carriage return to neaten the output echo "\n"; } // (5) Close the connection odbc_close($connection); ?> </pre> </body> </html> 4.4.3. Oracle 7 and 8 Through the OCI8 InterfaceOracle is well-supported with PHP functions, and seven key functions are listed here. Example 4-14 shows these functions implemented in a modified version of Example 4-1. The functions require that Oracle 8 client libraries be installed and the functions use the Oracle 8 Call Interface (OCI8). Support for previous versions of Oracle is available through a separate function library we don't discuss here. Oracle access is a six-step process. A connection is opened, and then a query is first prepared with OCIParse( ) and executed with OCIExecute( ). Then, each row is retrieved with OCIFetch( ) and individual attributes are retrieved from the row with OCIResult( ). Last, the connection is closed. Our treatment of Oracle functions is brief, and more detail can be found in the PHP manual. The key functions are:
Example 4-14. Connecting to an Oracle data source 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 connections $connection = OCILogon("fred","shhh", "winestore"); // (2) Setup the query on the winestore through the // connection $query = OCIParse($connection, "SELECT * FROM wine"); // (3) Run the query OCIExecute($query); // (4) Output the results while (OCIFetch($query)) { // (5) Print out the attributes in this row for($x=1;$x<=OCINumCols($query);$x++) echo OCIResult($query,$x); echo "\n"; } // (6) Close the database connection OCILogoff($connection); ?> </pre> </body> </html> 4.4.4. PostgreSQLPostgreSQL DBMSs are accessed in much the same way as MySQL and Microsoft SQL Server DBMSs. Again, there are many—often functionally overlapping—functions for connecting to, querying, and extracting results from a PostgreSQL DBMS. The five key functions are listed here, and Example 4-15 shows these implemented in a modified version of Example 4-1.
Example 4-15. Connecting to a PostgreSQL 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 connections $connection = pg_connect("host=localhost user=fred password=shhh dbname=winestore"); // (2) Run the query on the winestore through the // connection $result = pg_exec($connection,"SELECT * FROM wine"); // (3) While there are still rows in the result set while ($row = pg_fetch_row($result)) { // (4) Print out each attribute in the row for ($i=0; $i<pg_num_fields($result); $i++) echo $row[$i] . " "; // Print a carriage return to neaten the output echo "\n"; } // (5) Close the database connection pg_close($connection); ?> </pre> </body> </html> Copyright © 2003 O'Reilly & Associates. All rights reserved. |
|