anyDB

Class class for easy and unified database access.


Content

  1. Scope
  2. Features
  3. Usage/Examples
  4. Interface
  5. Notes
  6. History/Changes

1. Scope

to top
If you believe that database abstraction is a good thing, but you don't want to join the religous fights about the best one out there, then we have a common interest.

This abstract wrapper class provides easy and simple class methods to access the most important db function.
Classes implementing the functionality for native PHP functions, PEAR, PHPLIB, ADOdb and METABASE enables you to switch between these db abstraction layers by changing only a single line of code.

Now you are able to use a unifed abstraction layer for database access and switch between the underlaying layers.


2. Features

to top

Download the latest version at sourceforge.net, phpclasses.org

Rate this script @ www.hoscripts.com


3. Usage

to top
The following examples use a database table called 'users' containing the following data:

idnameemail
1peterpeter@peter.de
2uschiuschi@uschi.de

ID is the primary key of this table.

Connecting and Disconnecting

to top

require_once '../anyDB.php';
require_once 
'../addon/DBHelper.php';
require_once 
'../addon/QueryHelper.php';

$database 'lens';
$host 'localhost';
$user '';
$password '';
$dbType 'mysql';
$persistent false;

// create a new db layer
$db anyDB::getLayer('MYSQL',''$dbType);
    
//$db = anyDB::getLayer('POSTGRESQL', '', 'pgsql');
    //$db = anyDB::getLayer('SQLITE', '', 'sqlite');
    //$db = anyDB::getLayer('DBX', '../../../inc/dbx/', 'dbx');

    //$db = anyDB::getLayer('PEAR', 'c:/php4/pear/', $dbType);
    //$db = anyDB::getLayer('PHPLIB', '../../../inc/phplib-7.2d/', $dbType);
    //$db = anyDB::getLayer('METABASE', '../../../inc/metabase/', $dbType);
    //$db = anyDB::getLayer('ADODB', '../../../inc/adodb/', $dbType);

//connect to db
$db->connect($host$database$user$password$persistent);

// do something here

$db->free();
$db->disconnect();

echo 
$db->error;

The 'mysql' database layer is selected and an database object is returned. In the commented lines all possible db layers are listed.
With PEAR, PHPLIB, METABASE and ADODB you can access different types of databases, thats why you have to provide a $dbType. The apropriate type is explained in their documentation.


Submit a query

to top

// submit a query
if ($db->query("SELECT name, email FROM users where id=2")) {
    echo 
'OK!';
} else {
    echo 
$db->error;
}

The query() function will submit a sql query to the database. It will return TRUE upon success otherwise FALSE.
If a function returned FALSE, most likely an error occured and the most recent error string can be found in the $error variable.
All functions of this class will return FALSE in case of an error!


Get the next result

to top

// get the next result set
if ($db->query("SELECT name, email FROM users where id=2")) {
    
$res $db->getNext();
    echo 
implode(' - '$res) . '<br>';
} else {
    echo 
$db->error;
}

Output:
uschi - uschi@uschi.de

getNext() returns the next row from the result set. If no rows are available it will return FALSE.


Get the next result and displaying it in a table

to top

// get the next result and put it in a html table
if ($db->query("SELECT name, email FROM users where id=2")) {
    
$res $db->getNext();
    echo 
DBHelper::dumpNext($restrue);
} else {
    echo 
$db->error;
}

Output:
nameuschi
emailuschi@uschi.de

The DBHelper class contains different functions for displaying data as HTML.
dumpNext() will display a result row in a table and can optionally display a (user definable) header.


Get the next results in a numeric array

to top

// get the next result set as a numeric array
if ($db->query("SELECT name, email FROM users where id=2")) {
    
$res $db->getNext(ANYDB_RES_NUM);
    echo 
DBHelper::dumpNext($restrue);
} else {
    echo 
$db->error;
}

Output:
0uschi
1uschi@uschi.de

You can define what type of result you want to get with getNext(). It can be an associative array (the default), a numeric array or both combined into one, or you can request a result obj. This is done by passing a constant to the function.
The constants are:


Get the next result in an array with numeric and associative entries

to top

// get the next result set as an array with numeric and associative entries
if ($db->query("SELECT name, email FROM users where id=2")) {
    
$res $db->getNext(ANYDB_RES_BOTH);
    echo 
DBHelper::dumpNext($restrue);
} else {
    echo 
$db->error;
}

Output:
0uschi
nameuschi
1uschi@uschi.de
emailuschi@uschi.de

Metabase returns the numeric entries first for ANYDB_RES_BOTH and PEAR returns the alphanumeric entries first. The other layers will do it as shown in the example.


Get all results

to top

// get all results
if ($db->query("SELECT name, email FROM users")) {
    
$results $db->getAll();
    foreach (
$results as $res) {
        echo 
implode(' - '$res) . '<br>';
    }
} else {
    echo 
$db->error;
}

Output:
peter - peter@peter.de
uschi - uschi@uschi.de

getAll() returns a two dimensional array which represents the complete result set. You can also define (like for getNext()) if the result array should contain numeric, associative keys, both or be an array of objects.

In this example $res[1]['name'] is equal to "uschi"


Get all results with execute()

to top

// get all the results with execute
$results $db->execute("SELECT name, email FROM users");
foreach (@
$results as $res) {
    echo 
implode(' - '$res) . '<br>';
}

Output:
peter - peter@peter.de
uschi - uschi@uschi.de

execute() combines the query() and the getAll() function. It passes the query to the database and returns the complete result set.


Dump all results

to top

// get all the results and print them in a html table
$results $db->execute("SELECT name, email FROM users");
echo 
DBHelper::dumpAll($resultstrue, array('Name','Email Adress'));

Output:
NameEmail Adress
peterpeter@peter.de
uschiuschi@uschi.de

Just like dumpNext() does dumpAll() convert the returned array from getAll() or execute() into a html table.


Get one result column

to top

// get one column
if ($db->query("SELECT name FROM users")) {
    
$results $db->getColumn();
    foreach (
$results as $res) {
        echo 
$res '<br>';
    }
} else {
    echo 
$db->error;
}

Output:
peter
uschi

If you want only one column of data ('name' in the example above) you can use getColumn() to retrieve it.
It will return a one dimensional array or FALSE.


Dump one result column

to top

// get all one column and print them in a table
if ($db->query("SELECT name FROM users")) {
    
$res $db->getColumn();
    echo 
DBHelper::dumpColumn($restrue'all names');
    echo 
'<br>';
    echo 
DBHelper::dumpColumn($resfalse'name');
} else {
    echo 
$db->error;
}

Output:
all names peteruschi

name
peter
uschi

dumpColumn() will display the column data horizontally or vertically. A header can be specified as well.


Dump one result column in a Selectbox

to top

// display a column in a select box
if ($db->query("SELECT name FROM users")) {
    
$res $db->getColumn();
    echo 
DBHelper::selectBox($res0'mybox');
}

Output:

The first parameter for selectBox() is the result array. The second specifies what entry is selected and the third the name of the selectbox. There are more parameters but you will rarely use them, I think...


Set the submit values for a Selectbox

to top

// display a column in a select box
if ($db->query("SELECT name, id FROM users")) {
    
$res $db->getAll();
    echo 
DBHelper::selectBox($res0'mybox');
}

Output:

The selectbox looks the same but the result is different in this example.
If you specify two columns in your SQL query selectBox() will use the first column for the display and wil submit the second column as the submit value. In the example the selectbox would either submit '1' for 'peter' or '2' for 'uschi'.


Specify your result array index

to top
// uses the ID column as a result index
$db->query("SELECT * FROM users LIMIT 2");
$mapped = $db->getMapped('name');

Result:

$mapped = array[2] {
    'peter' => array[2] {
        id => integer(1)
        email => string[14]('peter@peter.de')
    }
    'uschi' => array[2] {
        id => integer(2)
        email => string[14]('uschi@uschi.de')
    }
}

getMapped() uses one of your result columns as an index for the result set. In the example the 'id' column is used.
If you specify only two result columns in your SQL query you will get a one dimensional array containing the single result column instead.
If you specify a column with non unique data, entries will get overwritten. So it makes sense to use database key columns.

Get one result value

to top

// get a single value from the database
if ($db->query("SELECT count(*) FROM users")) {
    
$count $db->getValue(ANYDB_INT);
    echo 
$count;
} else {
    echo 
$db->error;
}

Output:
2

This will by default return a string. But you can choose to get a different result type by passing a constant to the function. The possible constants are:


Insert data into the database

to top

// insert data in the database
if ($db->query("INSERT INTO users (name, email) VALUES ('joost','joost@joost.de')")) {
    echo 
'ok';
} else {
    echo 
$db->error;
}

Output:
ok

The function is query() as for all other SQL queries and it will return TRUE if your query was successful.


Get the ID of the last inserted dataset

to top

// insert data in the database
if ($db->query("INSERT INTO users (name, email) VALUES ('lennart','lennart@lennart.de')")) {
    echo 
$db->getInsertId();
} else {
    echo 
$db->error;
}
Output:
4

getInsertId() will return the index of the result of the last INSERT statement. Here it should return 4 because 'uschi' has the id 2 and we added something in the previous example.
This function is not supported by the ODBC class.


Check for data in the database

to top
$data = array('name' => 'uschi', 'email' => 'uschi@uschi.de');
   // check if data already exists
$id = $db->getDataId('users', $data);
if ($id === false) {
   echo "new dataset!";
} else {
   echo "dataset exists (id=$id)!";
}

Output:
dataset exists (id=2)!

getDataId() checks is data, provided in an array, exists in a database table. If it does, it returns the index or FALSE.
If you have an array with entries you don't want to check, you can pass a third parameter (an array) with the names of the keys you want to check.
In an optional fourth parameter you can specify which database column the function should return. The default is set to 'ID'.

This function is very useful for checking data submitted through a web form.


Connect to another database

to top
$res = $db->setDB('test2'); if (!$res) {
   echo $db->error;
}

Output:
Unknown database 'test2'

testDB() connects to a different database.


Make data ready for submission

to top
$str = "INSERT INTO users (name, email) VAULES ('test', 'test@test.de')";
$res1 = $db->escapeStr($str);

Output:
$res1 = string[67]('INSERT INTO users (name, email) VAULES (\'test\', \'test@test.de\')')

escapeStr() makes a query ready for executing. You can pass a string or an array of strings to the function. It will then escape the string(s).
If you only want certain entries of the array to be escaped you can pass the array keys to be escaped in a second array.


Build a paging mechanism

to top

// create a new page widget
require_once '../addon/PageWidget.php';
// widget settings
$entries 2;
$table 'users';
// which rows to display
$rows = array('name''email');
$widget = new PageWidget($db'mysqlLimitQuery'$table$entries);

echo 
"<h2>Page $widget->page</h2>";
echo 
$widget->getPageDropdown();
echo 
"Entries $widget->start -  $widget->end of $widget->total<p>";
echo 
'[' $widget->getIndex('] [') .']<p>';
echo 
$widget->getOrderDropdown($rows);
echo 
$widget->getNextLink();
echo 
'<br>';
echo 
$widget->getPrevLink();
// get the data
echo DBHelper::dumpAll($widget->get($rows), true);

Output:
Example output (with more entries)

example output


Get table names

to top

// get all tables
$tables = $db->getTables();
foreach (
$tables as $table) {
    echo
$table . '<br>';
}

Output:
users


Export table data to csv

to top

// export table content as csv data
$csv = Exporter::getTable($db, 'users', ANYDB_DUMP_CSV);
echo
nl2br($csv);

Output:
id name login email
1 peter peter peter@peter.de
2 uschi uschi uschi@uschi.de


Export database data to sql

to top

// export table content as sql statements
$sqlData = Exporter::getDB($db, ANYDB_DUMP_SQL);

foreach(
$sqlData as $key => $data) {
    echo
"$key<br>";
    echo
nl2br($data);
}

Output:
users
INSERT INTO users (id, name, login, email) VALUES ('1', 'peter', 'peter', 'peter@peter.de') ;
INSERT INTO users (id, name, login, email) VALUES ('2', 'uschi', 'uschi', 'uschi@uschi.de') ;


How to use QueryHelper


require_once '../addon/QueryHelper.php';
echo QueryHelper::insert('peter', array('a',4,2)) . '<br>';
echo 
QueryHelper::insert('peter', array('id' => 2,'name' =>'peter')) . '<br>';
echo 
QueryHelper::insert('peter', array('id' => 2,'name' =>"'peter'"), false) . '<br>';

echo 
QueryHelper::delete('peter', array('id'=>2,'name' =>'peter')) . '<br>';
echo 
QueryHelper::replace('peter', array('id' => 2,'name' =>'peter')) . '<br>';

echo 
QueryHelper::select(array('name''test'), 'peter', array('id' =>5,'test'=>'2'), true'LIMIT 10''DISTINCT') . '<br>';
echo 
QueryHelper::select(array('name''test'), 'peter''id=3'false'LIMIT 10') . '<br>';

echo 
QueryHelper::update('peter', array('name' =>'peter''id'=>4), 'id=10') . '<br>';
echo 
QueryHelper::update('peter', array('name' =>'peter''id'=>2), 'id=10'false) . '<br>';

Output:
INSERT INTO peter VALUES ('a', 4, 2)
INSERT INTO peter (id, name) VALUES (2, 'peter')
INSERT INTO peter (id, name) VALUES (2, 'peter')
DELETE FROM peter WHERE id=2, name='peter'
REPLACE INTO peter (id, name) VALUES (2, 'peter')
SELECT DISTINCT name, test FROM peter WHERE id=5, test='2' LIMIT 10
SELECT name, test FROM peter WHERE id=3 LIMIT 10
UPDATE peter SET name='peter', id=4 WHERE id=10
UPDATE peter SET name=peter, id=2 WHERE id=10


4. Interface

See interface.txt for the current interface description

5. Notes

  • The default result type is an associative array
  • Metabase converts the keys in an associative array to lower keys
  • Metabase returns the numeric entries first for 'ANYDB_RES_BOTH'
  • PEAR returns the alphanumeric entries first for 'ANYDB_RES_BOTH'
  • Native mysql, PHPLIB, ADOdb return the numeric, alphanumeric pairs for 'ANYDB_RES_BOTH'

6. History/Changes

See changes.txt in the root directory