ADOdb - PHP Database Abstraction

August 27, 2005

Anyone who has used more than one database (MySQL, PostgreSQL, SQL Server, Oracle, etc) with PHP realizes it is cumbersome. In fact the developers of PHP have recently realized this as well. In 5.1 they are planning to include PHP Data Objects (PDO). However, in the meantime I recommend ADOdb.

Unfortunately, PHP's database access functions are not standardised. Every database extension uses a different and incompatibile API. This creates a need for a database class library to hide the differences between the different databases (encapsulate the differences) so we can easily switch databases.

Example connection to mySQL:

PLAIN TEXT
PHP:
  1. $db = NewADOConnection('mysql'); // type of connection
  2. $db->SetFetchMode(ADODB_FETCH_ASSOC); // sets the query results to come back as assoc arrays
  3. $db->PConnect('mysql_db_host', 'some_user', 'some_pass', 'the_db'); // make the connection

Example connection to Oracle:

PLAIN TEXT
PHP:
  1. <?php
  2. $db = NewADOConnection('oci8'); // type of connection
  3. $db->SetFetchMode(ADODB_FETCH_ASSOC); // sets the query results to come back as assoc arrays
  4. $db->PConnect('oracle_db_host', 'jpseudo', 'his_password', 'oracle_sid'); // make the connection
  5. ?>

Comparing the two examples it's clear how similar connecting to either database becomes. Also notice we're setting the fetch mode to associative so we get back friendly associative arrays. The following is an example of how to query and get back results:

PLAIN TEXT
PHP:
  1. <?php
  2. $query = "SELECT * FROM tablename";
  3. $res = $db->Execute($query);
  4. while($row = $res->FetchRow())
  5. {
  6.      // $row is an associative array, indexed by fieldname from the table
  7.      print_r($row);
  8. }
  9. ?>

This will get back a bunch of rows of data, You might want less, like a single row or single field of a single row.

PLAIN TEXT
PHP:
  1. <?php
  2. $row = $db->GetRow("SELECT * FROM tablename WHERE id_field='single_value'"); // this returns an associative array of the single row
  3. $a_field = $db->GetRow("SELECT a_field FROM tablename WHERE id_field='single_value'"); // this returns the value of single field of the single row
  4. ?>

More Resources:

ADOdb Database Abstraction Library for PHP Main Site
ADOdb Documentation
Alternate Abstraction Library: PEAR DB

adodb, database, database abstraction, databases, mysql, oracle, pdo, php, programming

Tags: , , , , , , , ,

Related:

Posted by zbtirrell | Filed Under Technology Bits 


Comments

One Response to “ADOdb - PHP Database Abstraction”

  1. Tony Landis on June 3rd, 2008 11:54 pm

    Thanks… for those interested, I did a benchmark of PDO and the ADOdb library on my site.

Got something to say?





User contributed tags: t (224) - adodb fetchRow (200) - adodb php (162) - adodb fetch (158) - adodb fetch array (122) - adodb fetch assoc (100) - PDO vs adodb (93) - adodb getRow (76) - php adodb (76) - adodb assoc (74) - ADOdb vs PDO (70) - php getRow (69) - php adodb getRow (68) - php fetchrow (57) - PHP ADODB Fetchrow (53) - FetchRow adodb (53) - getrow php (49) - adodb php getrow (47) - php-adodb API (33) - adodb fetcharray (33) - SetFetchMode(ADODB_FETCH_ASSOC) (29) - php + adodb + fetch (27) - ADODB setFetchMode (26) - adodb php +examples (25) - adodb pconnect (24) - PHP ADODB fetch array (23) - adodb adodb_fetch_assoc (23) - adodb associative (21) - "adodb" examples (21) - adodb php fetchrow (20) - adodb getrow example (20) - FetchRow php (18) - adodb associative array (18) - adodb php api (18) - php adodb select (17) - adodb getarray (17) - adodb fetch mode (17) - php adodb example (17) - pdo vs ado (16) - adodb php fetch array (16) - php adodb examples (16) - getrow adodb (16) - php adodb ASSOC (14) - ado vs. pdo (14) - php adodb array (14) - adodb pdo (14) - php database abstraction (14) - mysql getrow (13) - adodb fetch field (12) - fetch adodb (12) - adodb fetchrow assoc (12) - adodb fetch rows (12) - adodb.php (12) - pdo adodb (12) - adodb php example (12) - fetch array adodb (11) - php pdo vs adodb (11) - fetchRow() in PHP (11) - adodb in php (10) - php ADODB_FETCH_ASSOC (10) - adodb php select (10) - PHP ADOdb PConnect (10) - pdo VS pear (10) - adodb vs pear (9) - nosheep net (9) - adodb get row (9) - php adodb samples (9) - adodb php fetch (9) - adodb getrow php (9) - adodb get array (9) - adodb vs peardb (9) - getrow adodb php (8) - ado getrow (8) - adodb "switch database" (8) - php mysql getrow (8) - php ADODB Fetch Assoc (8) - FetchRow adodb php (8) - adodb php access example (8) - adodb php ASSOC (8) - example of adodb (8) - getrow example (7) - adodb php access (7) - adodb array (7) - adodb get (7) - php ado getRow (7) - adodb fetch row (7) - adodb php SetFetchMode (7) - php fetchrow example (7) - adodb fetchrow php (7) - fetcharray adodb (7) - Fetch Assoc adodb (7) - adodb fetch example (6) - adodb single row (6) - php adodb getarray (6) - php com adodb (6) - mysql 5 adodb pconnect (6) - PHP SetFetchMode (6) - adodb assoc array (6) - database (6) - php adodb setfetchmode (6) - adodb fetch results (6) - php adodb sample (6) - adodb+FetchRow() (6) - adodb 5 (6) - PHP ADO connection (6) - adodb fetch fields (6) - php pdo ado (6) - pdo versus adodb (5) - adodb select php (5) - php ADODB.Connection (5) - "php ado (5) - pdo or adodb (5) - ADODB php array (5) - ado fetch array (5) - GetArray adodb php (5) - PEAR::DB vs PDO (5) - ADO php FetchRow (5) - PDO vs PEar DB (5) - all (5) - adodb samples (5) - db getrow assoc (5) - ado fetchrow (5) - php adodb pdo (5) - f (5) - php adodb fetcharray (5) - pear db getrow assoc (5) - ADODB_FETCH_ASSOC (5) - pdo fetch assoc (5) - php adodb fields (5) - getrow ado (5) - adodb fetch_assoc execute (4) - adodb php fetching (4) - Database Abstraction Library for PHP (4) - adodb examples php (4) - ADODB examples for PHP (4) - adodb oracle php (4) - php adodb ADODB_FETCH_ASSOC (4) - php data base abstraction (4) - pear db vs adodb (4) - php adodb fetch field (4) - pdo vs. pear::db (4) - adodb php get array (4) - pdo fetchrow (4) - php ADODB get array (4) - php ado pdo (4) - db->getRow (4) - php database abstraction mysql (4) - fetch array php adodb (4) - adodb fetch examples (4) - adodb php query (4) -