 |  |
10.4. Connecting to a SQL Database
10.4.2. Solution
Use the connect( ) method of PEAR DB:
require 'DB.php';
$dsn = 'mysql://david:foo!bar@localhost/test';
$dbh = DB::connect($dsn);
if (DB::isError($dbh)) { die ($dbh->getMessage()); }10.4.3. Discussion
To use PEAR DB, you must download it from PEAR at:
After loading the DB functions from DB.php, connect to the database with DB::connect( ), execute the query with $dbh->query( ) , and retrieve each row with $sth->fetchRow( ). The Solution example connects to MySQL. To connect to Oracle instead, you just need to change $dsn. This variable holds the data source name (DSN), a string that specifies which database to connect to and how to connect to it. Here's the value for Oracle:
$dsn = 'oci8://david:foo!bar@ORAINST';
For PostgreSQL, $dsn is:
$dsn = 'pgsql://david:foo!bar@unix(/tmp/.s.PGSQL.5432)/test';
The PostgreSQL DSN is a little more complicated because it specifies that the connection should be made using a local Unix socket (whose pathname is /tmp/.s.PGSQL.5432) instead of a TCP/IP connection. In general, the form of a data source name is:
database_interface://user:password@hostname/database
The database_interface part of the DSN is the kind of database you're using, such as Oracle, MySQL, etc. Currently, PEAR supports 10 database backends, as listed in Table 10-1.
Table 10-1. PEAR DB backends
|
Name |
Database |
|---|---|
|
fbsql |
FrontBase |
|
ibase |
Interbase |
|
ifx |
Informix |
|
msql |
Mini-SQL |
|
mssql |
Microsoft SQL Server |
|
mysql |
MySQL |
|
oci8 |
Oracle (using the OCI8 interface) |
|
odbc |
ODBC |
|
pgsql |
PostgreSQL |
|
sybase |
Sybase |
To use a particular PEAR DB backend, PHP must be built with support for the database that corresponds to the backend. Note that to use the Oracle OCI8 backend, PHP must have the OCI8 extension (--with-oci8 when building). The older PHP oracle extension (--with-oracle) isn't compatible with PEAR DB.
user and password are the username and password to use to connect to the database. hostname is usually the hostname that the database is running on, but it can also be the name of an instance (for Oracle) or the special syntax used previously to indicate a local socket. database is the name of the logical database to use, such as what you'd specify with the dbname parameter in pg_connect( ) or the argument to mysql_select_db( ).
PEAR DB is by no means the only database abstraction layer available for PHP. We've chosen to focus on it because it's easy to use and widely available. Other database abstraction layers include ADOdb (http://php.weblogs.com/ADODB), Metabase (http://en.static.phpclasses.org/browse.html/package/20.html), the DB_Sql class in PHPLib (http://phplib.sourceforge.net/), and MDB (http://pear.php.net/package-info.php?package=MDB).
10.4.4. See Also
Recipe 10.5 for querying a SQL database; Recipe 10.7 for modifying a SQL database; Pear DB at http://pear.php.net/package-info.php?package=DB; documentation on DB::connect( ) at http://pear.php.net/manual/en/core.db.tut_connect.php and http://pear.php.net/manual/en/core.db.connect.php; information on DSNs at http://pear.php.net/manual/en/core.db.tut_dsn.php.
|  | |
| 10.3. Using DBM Databases |  | 10.5. Querying a SQL Database |
Copyright © 2003 O'Reilly & Associates. All rights reserved.