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.
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 " "; } // (5) Close the database connection mssql_close($connection); ?> </pre> </body> </html>
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.
resource odbc_connect(string
datasource
, string
username
, string
password
,
[int
cursor_type
])
Establishes a connection to an ODBC data source. On success, the
function returns a connection resource handle that can access
databases through subsequent commands. The first parameter is a DSN
to indicate the data source to connect to. The DSN parameter can
require some experimentation; it depends on the DBMS being accessed.
The DSN can sometimes be prefixed with DSN=
and
sometimes this can be omitted. The second and third parameters, as
well as the return value (a connection resource), are the same as for
mysql_connect( )
. The fourth parameter is often
unnecessary; however, if problems are encountered using ODBC, try
passing through a fourth parameter of
SQL_CUR_USE_ODBC
.
resource odbc_exec(resource
connection
, string
query
)
Runs an SQL query
on the
connection
returned from
odbc_connect( )
. Returns a result resource handle on success
and false
on failure.
int odbc_fetch_row(resource
result_set
)
Fetches the result-set data, row-by-row, following an
odbc_exec( )
command using the
result_set
identifier returned by the
query. The results are returned as an array, and the use is identical
to mysql_fetch_row( )
. false
is returned when no more rows are available.
int odbc_num_fields(resource
result_set
)
Returns the number of attributes associated with a
result_set
handle, where the
result_set
handle is returned from
odbc_exec( )
.
int odbc_close(resource
connection
)
Closes an ODBC data source opened with odbc_connect( )
.
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 " "; } // (5) Close the connection odbc_close($connection); ?> </pre> </body> </html>
Oracle 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:
resource OCILogon(string
username
, string
password
, string
database
)
Establishes a connection to an Oracle DBMS. On success, the function
returns a connection handle that can access databases through
subsequent commands. Parameters are the same as those for
mysql_connect( )
.
resource OCIParse(resource
connection
, string
SQL_command
)
Returns a query resource handle that can subsequently be executed, or
returns false
on error. The
connection
resource created with
OCILogon( )
is passed as a parameter, along with an
SQL_command
. The function
doesn’t execute the
query—OCIExecute( )
does that—but this function is required to set up the query for
execution.
int OCIExecute(resource
query_handle
)
Runs the query set up with OCIParse( )
,
taking the return value of OCIParse( )
as the
only parameter. Results are subsequently fetched with
OCIFetch( )
. Returns true
on success
and false
on failure.
int OCIFetch(resource
query_handle
)
Buffers a row from the last OCIExecute( )
call specified with the query_handle
returned from OCIParse( )
. Returns
true
if a row is retrieved and
false
when no more rows are available. Attributes
are fetched from this buffer with OCIResult( )
.
int OCINumCols(resource
query_handle
)
Returns the number of attributes associated with the query specified
in OCIParse( )
.
mixed OCIResult(resource
query_handle
, int
attribute_number
)
Fetches the value of attribute_number
from
the current row retrieved with OCIFetch( )
.
Takes the return result of OCIParse( )
as the
first parameter.
int OCILogoff(resource
connection
)
Closes an Oracle connection opened with OCILogon( )
.
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 " "; } // (6) Close the database connection OCILogoff($connection); ?> </pre> </body> </html>
PostgreSQL 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.
resource pg_connect(string
connection_details
)
Establishes a connection to a PostgreSQL DBMS. On success, the
function returns a connection resource handle that can access
databases through subsequent commands. It returns
false
on failure.
The parameters are similar to those of the mysql_connect( )
function, but the parameters are concatenated into a
single string that usually includes the keywords
host
, dbname
,
user
, and password
. For
example, to connect to localhost, use the
winestore database, and log in as
fred with password shhh,
the format is:
$connection = pg_connect("host=localhost dbname=winestore user=fred password=shhh");
resource pg_exec(resource
connection
, string
SQL_command
)
Runs an SQL command through the connection created with
pg_connect( )
(the database is selected with
pg_connect( )
). Returns a resource—a
result handle used to fetch the result set—on success, and
false
on failure.
array pg_fetch_row(resource
result_set
)
Fetches the result-set data, row by row, following a
pg_exec( )
command using the
result_set
resource returned by the query.
The results are returned as an array, and the use is identical to
mysql_fetch_row( )
. false
is
returned when no more rows are available.
int pg_num_fields(resource
result_set
)
Returns the number of attributes in a
result_set
resource handle, where the
result_set
handle is returned from
pg_exec( )
.
int pg_close(resource
connection
)
Closes a PostgreSQL connection opened with pg_connect( )
.
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 " "; } // (5) Close the database connection pg_close($connection); ?> </pre> </body> </html>
3.147.6.243