ADOdb Library for PHP Manual
Prev Code Initialization, High Speed ADOdb, Hacking ADOdb, PHP5 Features Next

Code Initialization and Connecting to Databases

When running ADOdb, at least two files are loaded. First is adodb/adodb.inc.php, which contains all functions used by all database classes. The code specific to a particular database is in the adodb/driver/adodb-????.inc.php file.

For example, to connect to a mysql database:

include('/path/to/set/here/adodb.inc.php');
$conn = &ADONewConnection('mysql');

Whenever you need to connect to a database, you create a Connection object using the ADONewConnection($driver) function. NewADOConnection($driver) is an alternative name for the same function.

At this point, you are not connected to the database (no longer true if you pass in a dsn). You will first need to decide whether to use persistent or non-persistent connections. The advantage of persistent connections is that they are faster, as the database connection is never closed (even when you call Close()). Non-persistent connections take up much fewer resources though, reducing the risk of your database and your web-server becoming overloaded.

For persistent connections, use $conn->PConnect(), or $conn->Connect() for non-persistent connections. Some database drivers also support NConnect(), which forces the creation of a new connection.

Connection Gotcha: If you create two connections, but both use the same userid and password, PHP will share the same connection. This can cause problems if the connections are meant to different databases. The solution is to always use different userid's for different databases, or use NConnect().

Data Source Name (DSN) Support

Since ADOdb 4.51, you can connect to a database by passing a dsn to NewADOConnection (or ADONewConnection, which is the same function). The dsn format is:

	$driver://$username:$password@hostname/$database?options[=value]

NewADOConnection() calls Connect() or PConnect() internally for you. If the connection fails, false is returned.

	# non-persistent connection
	$dsn = 'mysql://root:pwd@localhost/mydb'; 
	$db = NewADOConnection($dsn);
	if (!$db) die("Connection failed");
	
	# no need to call connect/pconnect!
	$arr = $db->GetArray("select * from table");
	
	# persistent connection
	$dsn2 = 'mysql://root:pwd@localhost/mydb?persist'; 

If you have special characters such as /:? in your dsn, then you need to rawurlencode them first:

	$pwd = rawurlencode($pwd);
$dsn = "mysql://root:$pwd@localhost/mydb"; $dsn2 = rawurlencode("sybase_ase")."://user:pass@host/path?query";

Legal options are:

For all drivers 'persist', 'persistent', 'debug', 'fetchmode'
Interbase/Firebird 'dialect', 'charset', 'buffers', 'role'
M'soft ADO 'charpage'
MySQL 'clientflags'
MySQLi 'port', 'socket', 'clientflags'
Oci8 'nls_date_format', 'charset'

For all drivers, when the options persist or persistent are set, a persistent connection is forced. The debug option enables debugging. The fetchmode calls SetFetchMode(). If no value is defined for an option, then the value is set to 1.

ADOdb DSN's are compatible with version 1.0 of PEAR DB's DSN format.

Examples of Connecting to Databases

MySQL and Most Other Database Drivers

MySQL connections are very straightforward, and the parameters are identical to mysql_connect:

	$conn = &ADONewConnection('mysql'); 
$conn->PConnect('localhost','userid','password','database');

# or dsn $dsn = 'mysql://user:pwd@localhost/mydb'; $conn = ADONewConnection($dsn); # no need for Connect() # or persistent dsn $dsn = 'mysql://user:pwd@localhost/mydb?persist'; $conn = ADONewConnection($dsn); # no need for PConnect() # a more complex example: $pwd = urlencode($pwd); $flags = MYSQL_CLIENT_COMPRESS; $dsn = "mysql://user:$pwd@localhost/mydb?persist&clientflags=$flags"; $conn = ADONewConnection($dsn); # no need for PConnect()

For most drivers, you can use the standard function: Connect($server, $user, $password, $database), or a DSN since ADOdb 4.51. Exceptions to this are listed below.

PDO

PDO, which only works with PHP5, accepts a driver specific connection string:

	$conn =& NewADOConnection('pdo');
	$conn->Connect('mysql:host=localhost',$user,$pwd,$mydb);
	$conn->Connect('mysql:host=localhost;dbname=mydb',$user,$pwd);
	$conn->Connect("mysql:host=localhost;dbname=mydb;username=$user;password=$pwd");

The DSN mechanism is also supported:

	$conn =& NewADOConnection("pdo_mysql://user:pwd@localhost/mydb?persist"); # persist is optional

PostgreSQL

PostgreSQL 7 and 8 accepts connections using:

a. the standard connection string:

	$conn = &ADONewConnection('postgres');
$conn->PConnect('host=localhost port=5432 dbname=mary');

b. the classical 4 parameters:

	$conn->PConnect('localhost','userid','password','database');

c. dsn:

	$dsn = 'postgres://user:pwd@localhost/mydb?persist';  # persist is optional
$conn = ADONewConnection($dsn); # no need for Connect/PConnect

LDAP

Here is an example of querying a LDAP server. Thanks to Josh Eldridge for the driver and this example:

require('/path/to/adodb.inc.php');

/* Make sure to set this BEFORE calling Connect() */
$LDAP_CONNECT_OPTIONS = Array(
	Array ("OPTION_NAME"=>LDAP_OPT_DEREF, "OPTION_VALUE"=>2),
	Array ("OPTION_NAME"=>LDAP_OPT_SIZELIMIT,"OPTION_VALUE"=>100),
	Array ("OPTION_NAME"=>LDAP_OPT_TIMELIMIT,"OPTION_VALUE"=>30),
	Array ("OPTION_NAME"=>LDAP_OPT_PROTOCOL_VERSION,"OPTION_VALUE"=>3),
	Array ("OPTION_NAME"=>LDAP_OPT_ERROR_NUMBER,"OPTION_VALUE"=>13),
	Array ("OPTION_NAME"=>LDAP_OPT_REFERRALS,"OPTION_VALUE"=>FALSE),
	Array ("OPTION_NAME"=>LDAP_OPT_RESTART,"OPTION_VALUE"=>FALSE)
);
$host = 'ldap.baylor.edu';
$ldapbase = 'ou=People,o=Baylor University,c=US';

$ldap = NewADOConnection( 'ldap' );
$ldap->Connect( $host, $user_name='', $password='', $ldapbase );

echo "<pre>";

print_r( $ldap->ServerInfo() );
$ldap->SetFetchMode(ADODB_FETCH_ASSOC);
$userName = 'eldridge';
$filter="(|(CN=$userName*)(sn=$userName*)(givenname=$userName*)(uid=$userName*))";

$rs = $ldap->Execute( $filter );
if ($rs)
	while ($arr = $rs->FetchRow()) {
	     print_r($arr);	
	}

$rs = $ldap->Execute( $filter );
if ($rs) 
	while (!$rs->EOF) {
 		print_r($rs->fields);	
		$rs->MoveNext();
	} 
	
print_r( $ldap->GetArray( $filter ) );
print_r( $ldap->GetRow( $filter ) );

$ldap->Close();
echo "</pre>";

Using DSN:

$dsn = "ldap://ldap.baylor.edu/ou=People,o=Baylor University,c=US";
$db = NewADOConnection($dsn);

Interbase/Firebird

You define the database in the $host parameter:
	$conn = &ADONewConnection('ibase'); 
$conn->PConnect('localhost:c:\ibase\employee.gdb','sysdba','masterkey');

Or dsn:

	$dsn = 'firebird://user:pwd@localhost/mydb?persist&dialect=3';  # persist is optional
$conn = ADONewConnection($dsn); # no need for Connect/PConnect

SQLite

Sqlite will create the database file if it does not exist.
	$conn = &ADONewConnection('sqlite'); 
$conn->PConnect('c:\path\to\sqlite.db'); # sqlite will create if does not exist

Or dsn:

	$path = urlencode('c:\path\to\sqlite.db');
$dsn = "sqlite://$path/?persist"; # persist is optional
$conn = ADONewConnection($dsn); # no need for Connect/PConnect

Oracle (oci8)

With oci8, you can connect in multiple ways. Note that oci8 works fine with newer versions of the Oracle, eg. 9i and 10g.

a. PHP and Oracle reside on the same machine, use default SID.

	$conn->Connect(false, 'scott', 'tiger');

b. TNS Name defined in tnsnames.ora (or ONAMES or HOSTNAMES), eg. 'myTNS'

	$conn->PConnect(false, 'scott', 'tiger', 'myTNS');

or

 	$conn->PConnect('myTNS', 'scott', 'tiger');

c. Host Address and SID

	# with adodb 5.06 or 4.991 and later
	$conn->Connect('192.168.0.1', 'scott', 'tiger', "SID=$SID");
	
	# OR with all versions of ADOdb
	$conn->connectSID = true;
	$conn->Connect('192.168.0.1', 'scott', 'tiger', $SID);
	

d. Host Address and Service Name

	$conn->Connect('192.168.0.1', 'scott', 'tiger', 'servicename');

e. Oracle connection string:

	$cstr = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$host)(PORT=$port))
(CONNECT_DATA=(SID=$sid)))";
$conn->Connect($cstr, 'scott', 'tiger');

f. ADOdb dsn:

	$dsn = 'oci8://user:pwd@tnsname/?persist';  # persist is optional
$conn = ADONewConnection($dsn); # no need for Connect/PConnect

$dsn = 'oci8://user:pwd@host/sid';
$conn = ADONewConnection($dsn);

$dsn = 'oci8://user:pwd@/'; # oracle on local machine
$conn = ADONewConnection($dsn);

You can also set the charSet for Oracle 9.2 and later, supported since PHP 4.3.2, ADOdb 4.54:

	$conn->charSet = 'we8iso8859p1';
$conn->Connect(...);

# or
$dsn = 'oci8://user:pwd@tnsname/?charset=WE8MSWIN1252';
$db = ADONewConnection($dsn);

DSN-less ODBC (Access, MSSQL and DB2 examples)

ODBC DSN's can be created in the ODBC control panel, or you can use a DSN-less connection.To use DSN-less connections with ODBC you need PHP 4.3 or later.

For Microsoft Access:

	$db =& ADONewConnection('access');
$dsn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=d:\\northwind.mdb;Uid=Admin;Pwd=;"; $db->Connect($dsn);
For Microsoft SQL Server:
	$db =& ADONewConnection('odbc_mssql');
$dsn = "Driver={SQL Server};Server=localhost;Database=northwind;";
$db->Connect($dsn,'userid','password');
or if you prefer to use the mssql extension (which is limited to mssql 6.5 functionality):
	$db =& ADONewConnection('mssql');
$db->Execute("localhost', 'userid', 'password', 'northwind');
For DB2:
	$db =& ADONewConnection('db2');
$dsn = "driver={IBM db2 odbc DRIVER};Database=sample;hostname=localhost;port=50000;protocol=TCPIP;".
"uid=root; pwd=secret";
$db->Connect($dsn);
DSN-less Connections with ADO
If you are using versions of PHP earlier than PHP 4.3.0, DSN-less connections only work with Microsoft's ADO, which is Microsoft's COM based API. An example using the ADOdb library and Microsoft's ADO:
<?php
include('adodb.inc.php');
$db = &ADONewConnection("ado_mssql");
print "<h1>Connecting DSN-less $db->databaseType...</h1>";

$myDSN="PROVIDER=MSDASQL;DRIVER={SQL Server};"
. "SERVER=flipper;DATABASE=ai;UID=sa;PWD=;" ;
$db->Connect($myDSN); $rs = $db->Execute("select * from table"); $arr = $rs->GetArray(); print_r($arr); ?>

High Speed ADOdb

ADOdb is a big class library, yet it consistently beats all other PHP class libraries in performance. This is because it is designed in a layered fashion, like an onion, with the fastest functions in the innermost layer. Stick to the following functions for best performance:

Innermost Layer

Connect, PConnect, NConnect
Execute, CacheExecute
SelectLimit, CacheSelectLimit
MoveNext, Close
qstr, Affected_Rows, Insert_ID

The fastest way to access the fields is by accessing the array $recordset->fields directly. Also set the global variables $ADODB_FETCH_MODE = ADODB_FETCH_NUM, and $ADODB_COUNTRECS = false before you connect to your database.

Consider using bind parameters if your database supports it, as it improves query plan reuse. Use ADOdb's performance tuning system to identify bottlenecks quickly.

Installing the ADOdb C extension will speed up GetAll() and GetArray() by 100%, and GetAssoc() by 30%. It will also speed up oci8 select statements by 30%.

Lastly make sure you have a PHP accelerator cache installed such as APC, Turck MMCache, Zend Accelerator or ionCube.

Some examples:

Fastest data retrieval using PHPFastest data retrieval using ADOdb extension
$rs =& $rs->Execute($sql);
while (!$rs->EOF) {
var_dump($rs->fields);
$rs->MoveNext();
}
$rs =& $rs->Execute($sql);
$array = adodb_getall($rs);
var_dump($array);


Advanced Tips

If you have the ADOdb C extension installed, you can replace your calls to $rs->MoveNext() with adodb_movenext($rs). This doubles the speed of this operation. For retrieving entire recordsets at once, use GetArray(), which uses the high speed extension function adodb_getall() internally.

Execute() is the default way to run queries. You can use the low-level functions _Execute() and _query() to reduce query overhead. Both these functions share the same parameters as Execute().

If you do not have any bind parameters or your database supports binding (without emulation), then you can call _Execute() directly. Calling this function bypasses bind emulation. Debugging is still supported in _Execute().

If you do not require debugging facilities nor emulated binding, and do not require a recordset to be returned, then you can call _query. This is great for inserts, updates and deletes. Calling this function bypasses emulated binding, debugging, and recordset handling. Either the resultid, true or false are returned by _query().

For Informix, you can disable scrollable cursors with $db->cursorType = 0.

Hacking ADOdb Safely

You might want to modify ADOdb for your own purposes. Luckily you can still maintain backward compatibility by sub-classing ADOdb and using the $ADODB_NEWCONNECTION variable. $ADODB_NEWCONNECTION allows you to override the behaviour of ADONewConnection(). ADOConnection() checks for this variable and will call the function-name stored in this variable if it is defined.

In the following example, new functionality for the connection object is placed in the hack_mysql and hack_postgres7 classes. The recordset class naming convention can be controlled using $rsPrefix. Here we set it to 'hack_rs_', which will make ADOdb use hack_rs_mysql and hack_rs_postgres7 as the recordset classes.

class hack_mysql extends adodb_mysql {
var $rsPrefix = 'hack_rs_';
/* Your mods here */
}

class hack_rs_mysql extends ADORecordSet_mysql {
/* Your mods here */
}

class hack_postgres7 extends adodb_postgres7 {
var $rsPrefix = 'hack_rs_';
/* Your mods here */
}

class hack_rs_postgres7 extends ADORecordSet_postgres7 {
/* Your mods here */
}

$ADODB_NEWCONNECTION = 'hack_factory';

function& hack_factory($driver)
{
if ($driver !== 'mysql' && $driver !== 'postgres7') return false;

$driver = 'hack_'.$driver;
$obj = new $driver();
return $obj;
}

include_once('adodb.inc.php');

Don't forget to call the constructor of the parent class in your constructor. If you want to use the default ADOdb drivers return false in the above hack_factory() function.

PHP5 Features

ADOdb 4.02 or later will transparently determine which version of PHP you are using. If PHP5 is detected, the following features become available:


Prev Home Next
Installation Guide Up Supported Databases

Sponsored by phpLens