PHP CookBook

Recipe 10.4 Connecting to a SQL Database

10.4.1 Problem

You want access 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:

http://pear.php.net/package-info.php?package=DB

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.