Book HomePHP CookbookSearch this book

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.



Library Navigation Links

Copyright © 2003 O'Reilly & Associates. All rights reserved.