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);
?>