Readme.html:
anyDB Manual
.new, .update {
text-transform: uppercase;
letter-spacing: .1em;
margin-left: 10px;
padding-left: 2px;
padding-right: 2px;
background: black;
color: white;
font-weight: bold;
font-size: 8pt;
}
anyDB
Class class for easy and unified database access.
Content
Scope
Features
Usage/Examples
Connecting and disconnecting
Submit a query
Get the next result
Get the next result and displaying it in a table
Get the next results in a numeric array
Get the next results as an array or an objectupdate
Get all results
Get all results with execute()
Dump all results
Get one result column
Dump one result column
Dump one result column in a Selectbox
Set the submit value for a Selectboxnew
Specify your result array indexnew
Get one result valueupdate
Insert data into the database
Get the ID of the last inserted datasetnew
Check for data in the databasenew
Connect to another databasenew
Make data ready for submissionupdate
More about errors
getCount, getMax, getMin
num and affected rows
Build a paging mechanism
Get table names
Export table data to csv
Export database data to sql
How to use QueryHelper
Interface
Notes
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
Can access MYSQL, POSTGRESQL, SQLITE and ODBC databases
Works with php4 and php5
Unified interface for different databases
A very light and small interface - less code to write
Query results can be single values, arrays or objects
Optional helper classes provide additional functionality, like paging, generating form elements, etc.
Download the latest version at sourceforge.net, phpclasses.org
Rate this script @ www.hoscripts.com
Excellent!
Very Good
Good
Fair
Poor
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($res, true);
} 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($res, true);
} 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:
ANYDB_RES_ASSOC (default)
ANYDB_RES_NUM
ANYDB_RES_BOTH
ANYDB_RES_OBJ
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($res, true);
} 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 deuschi - 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 deuschi - 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($results, true, 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($res, true, 'all names');
echo '<br>';
echo DBHelper::dumpColumn($res, false, '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($res, 0, 'mybox');
}
Output:
peter
uschi
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($res, 0, 'mybox');
}
Output:
peter
uschi
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:
ANYDB_INT
ANYDB_FLOAT
ANYDB_DOUBLE
ANYDB_BOOL
ANYDB_STRING
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)
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:
usersINSERT 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
to top
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
to top
See interface.txt for the current interface description
5. Notes
to top
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
to top
See changes.txt in the root directory
Other Database Tools Scripts:
