• Index
  •  » Articles
  •  » Abstract access to the database using ADODB

Abstract access to the database using ADODB

Abstract access to the database using ADODB

Few words about ADODB

ADODB is an abstract class for accessing the database, written on PHP.

For example you have written the script for MySQL. But customer says that there are changes in the hosting and there is only PostgreSQL. If you haven’t used the class of the abstract access to the database you would have to:

  • change all the code from MySQL to PostgreSQL
  • rewrite SQL-queries

ADODB is not the only class of the abstract access to the database. The most famous competitor are:

  • Pear::DB
  • Pear::MDB

As I know other classes have week functionality, but work faster. Some developers say that using such classes as ADODB or Pear::DB adversely affects the productivity. But:

  • Speed is not always the most important factor
  • Using of these classes increases the productivity of the programmer
  • If you use phpAccelerator productivity drop won’t be so evident
  • ADODB developers have written the php-extension that accelerates the class working

Installation

Download the archive from the http://php.weblogs.com/adodb and extract it (for example, ./adodb folder).

That’s it. You can use the class. If you want to use that class you have to include file /adodb/adodb.inc.php

Simple examples


Example 1.

<?php 
// include the class  
include_once("adodb/adodb.inc.php"); 
// indicate the database type 
$conn = &ADONewConnection("mysql"); 
// connect to the database 
$conn->Connect("localhost", "root", "password", "scripts"); 
// debug mode&nbsp;&#151; is true 
$conn->debug = true; 
$conn->setFetchMode(ADODB_FETCH_ASSOC); 

?>

That example demonstrates the connection to the database. In the line

<?php $conn = &ADONewConnection("mysql"); ?>

There is created the object of connection with database. Exactly via these fields and methods of that object there will be carried out the working with database.

What about debug mode, it is disabled by default. If it is enabled browser will display SQL queries and text of the errors <?php $conn->setFetchMode(); ?> method indicates the method of recording data to the array. There should be set one of the values (0,1,2,3). Here is an example:

<?php 
   define("ADODB_FETCH_DEFAULT",0); 
   define("ADODB_FETCH_NUM",1); 
   define("ADODB_FETCH_ASSOC",2); 
   define("ADODB_FETCH_BOTH",3); 
?>

ADODB_FETCH_DEFAULT == ADODB_FETCH_BOTH


Now let’s create the query to the database:

<?php 
// create the query to the database 
$res = $conn->Execute("SELECT id, title, description FROM tab"); 
// if there are some records in the table 
if ($res && $res->RecordCount() > 0) { 
   // pront them in the cicle 
   while (!$res->EOF) { 
      echo "ID = ".$res->fields["id"]."\n"; 
      echo "title = ".$res->fields["title"]."\n"; 
      echo "description".$res->fields["description"]; 
      // go to the next record 
      $res->MoveNext(); 
   } 
} 

?>

It is the simplest query to the database. $conn->Execute() method executes the query and returns recordset. Recordset in ADODB is the separate object that has its fields and methods for working with received records. Some of them are used in that example:

  • <?php $res->EOF; ?> — equals true if all records are handled
  • <?php $res->fields; ?> — stores the associative array of the current record values
  • <?php $res->RecordCount(); ?> — returns the amount of lines received while executing the query
  • <?php $res->MoveNext(); ?> — goes to the next


Method $conn->Execute() can be used for any queries:

<?php 
// insert the line 
$conn->Execute("INSERT INTO tab(name, value) VALUES ("name", "ha ha ha")"); 
// get the insertion’s identifier 
// àíàëîã mysql_insert_id(); 
$id = $conn->Insert_ID();  


$conn->Execute("DELETE FROM tab WHERE id = ".$id); 

?>

Here we have other useful methods:

  • <?php $conn->getRow($sql); ?> — returns the array with first record’s value
  • <?php $conn->getAll($sql); ?> — returns two-dimensional array with all found records



Practical use

Paginal output and SELECT-queries restriction

Actually not all databases can execute queries like: SELECT * FROM tab LIMIT 0, 10. Methods of queries executing are different:

MySQL:
SELECT * FROM tab LIMIT 0, 10

PostgreSQL:
SELECT * FROM tab OFFSET 0, LIMIT 10

FireBird:
SELECT FIRST 10 SKIP 0 * FROM tab


Adodb class can carry out the restricted fetches creating proper SQL-queries for different databases which support limited SELECT-queries.


<?php $res = $conn->SelectLimit("SELECT * FROM tab", 10, 0); ?>

Method <?php $conn->SelectLimit(); ?> can create right SQL-query. Functions of the paginal fetch in ADODB works on the base of that method.


<?php 

// define the current page 
$start = max(1, intval($_GET["start"])); 
// records per page 
$rows_per_page = 10; 
$res = $conn->PageExecute("SELECT * FROM tab", $rows_per_page, $start); 

// get the found amount of the records 
$records_amount = $res->MaxRecordCount(); 

?>

Method $conn->PageExecute() creates LIMIT-query as well as creates LIMIT-query as well as query like: SELECT COUNT(*) FROM tab.

Thus it learns itself the amount of lines found by executing that query. That amount can be counted be means of method $res->MaxRecordCount();


Also you can use the following methods:

  • <?php $res->AbsolutePage(); ?> — returns the current page
  • <?php $res->AtFirstPage(); ?> — returns true if the current page is first
  • <?php $res->AtLastPage(); ?> — returns true if the current page is last
  • <?php $res->LastPageNo(); ?> — returns the number of the last page



INSERT/UPDATE queries generation


At first let’s look at the example:

<?php 

// example of INSERT-query generation
// array that should be inserted to the table 
$frm = array("field1"=>"value1", "field2"=>"value2"); 
// create the empty query 
$res = $conn->Execute("SELECT * FROM tab WHERE id = -1"); 
// form SQL-query
$sql = $conn->GetInsertSQL($res, $frm); 
// execute the query 
$conn->Execute($sql) 

// example of UPDATE-query generation


// get the data about line that should be updated 
$res = $conn->Execute("SELECT * FROM tab WHERE id = 17"); 
$sql = $conn->GetUpdateSQL($res, $frm); 
// execute the query 
$conn->Execute($sql) 

?>

So, the idea is that all data that should be inserted must be recorded to the associative array. After that we have to create the query to the database for getting table fields’ names and construct the SQL query according to the data.


Transactions


Here is an example:

<?php 
   $conn->StartTrans(); 
   $conn->Execute("update table1 set val=$val1 where id=$id"); 
   $conn->Execute("update table2 set val=$val2 where id=$id"); 
   $conn->CompleteTrans(); 
?>

Method $conn->CompleteTrans() will check the presence of any errors.


Consecutions


When we work with tables we need to set the unique identifier for every record; that identifier will be used as a primary key. But not all database management systems support that feature. On practice it looks like:

<?php 
 $uid = $conn->GenID("site_users"); 
   $conn->Execute("INSERT INTO site_users(uid, login, password) VALUES 
        (".$uid.", "$login", "$password")"); 

?>

Query caching


ADODB supports server caching of the queries. The matter is that results of the first query are put to the cache-file. All data will be taken from the file when the next query is executed.

Here is an example of caching work:

<?php 
   $ADODB_CACHE_DIR = "/tmp/ADODB_cache"; 
   $rs = $conn->CacheExecute("SELECT * FROM tab"); 

?>

By default cache-files exist only 1 hour, but that time can be changed.

<?php
   $conn->cacheSecs = 24*3600 // 24 hours 
   $rs = $conn->CacheExecute("SELECT * FROM tab"); 


   // or: 
   $rs = $conn->CacheExecute(24*3500, "SELECT * FROM tab"); 
?>

Statistics


ADODB allows to count the amount of the queries to the database.

<?php 

function CountExecs($conn, $sql, $inputarray) { 
   global $EXECS; 
   $EXECS++; 
}


function CountCachedExecs($conn, $secs2cache, $sql, $inputarray) { 
   global $CACHED; 
   $CACHED++; 
} 


$conn = NewADOConnection("mysql"); 
$conn->Connect(...); 
$conn->fnExecute = "CountExecs";
$conn->fnCacheExecute = "CountCachedExecs"; 


... 

// print the statistics 
echo "All queries to the database: <b>".$EXECS+$CACHED."</b><br />"; 
echo "Queries taken from the cache : <b>".$CACHED."</b>"; 

?>

Given functions are called before query that’s why you can rewrite the SQL query using these functions.


ADODB & PEAR


The main class of working with databases in PEAR is PEAR::DB. A lot of PEAR-classes use it.

  • DB::NestedSet
  • DB::DataObject
  • DB::Pager
  • DB::QueryTool
  • HTML::Select
  • XML::sql2xml
  • Auth
  • Cache
  • Log
  • LiveUser
  • Mail::Queue
  • Translation

ADODB has adodb-pear.inc.php file that is an emulation of the PEAR::DB class and other classes can be adjusted to the working with ADODB (sometimes it can be enough to change require_once("DB.php"); line to the require_once("adodb-pear.inc.php");)

Thus, ADODB can be used with pear-classes without any problems. Let’s view the example of ADODB using with pear::XML::sql2xml class. That class transforms the result of SELECT query to the XML string.

<?php 
require_once("adodb/adodb-pear.inc.php"); 
require_once("XML/sql2xml.php"); 

$db = DB::connect("mysql://root@localhost/lot"); 
$sql2xml = new xml_sql2xml();                        
$result = $db->getAll("select * from lot_sessions");         
$xmlstring = $sql2xml->getXML($result); 
?>

Those who have ever worked with XML_sql2xml more often use the following codes:

<?php 
require_once("XML/sql2xml.php"); 
$sql2xml = new xml_sql2xml("mysql://root@localhost/lot"); 
$sql2xml->Add("select * from lot_sessions");                         
$xmlstring = $sql2xml->getXML(); 
?>

and

<?php 

$db = DB::connect("mysql://root@localhost/lot"); 

$sql2xml = new xml_sql2xml();                        

$result = $db->query("select * from lot_sessions"); 

$xmlstring = $sql2xml->getXML($result); 

?>

But these two codes won’t work and you will have to correct XML_sql2xml class.


 

  • Top