Example output (with more entries)
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.
| id | name | |
|---|---|---|
| 1 | peter | peter@peter.de |
| 2 | uschi | uschi@uschi.de |
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
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 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 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:
| name | uschi |
|---|---|
| uschi@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 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:
| 0 | uschi |
|---|---|
| 1 | uschi@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 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:
| 0 | uschi |
|---|---|
| name | uschi |
| 1 | uschi@uschi.de |
| uschi@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
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 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.
// 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:
| Name | Email Adress |
|---|---|
| peter | peter@peter.de |
| uschi | uschi@uschi.de |
// 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.
// 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 | peter | uschi |
|---|
| name |
|---|
| peter |
| uschi |
// display a column in a select box
if ($db->query("SELECT name FROM users")) {
$res = $db->getColumn();
echo DBHelper::selectBox($res, 0, '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...
// 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:
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'.
// uses the ID column as a result index
$db->query("SELECT * FROM users LIMIT 2");
$mapped = $db->getMapped('name');
Result:
// 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 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.
// 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:
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.
$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.
$res = $db->setDB('test2');
if (!$res) {
echo $db->error;
}
Output:
Unknown database 'test2'
testDB() connects to a different database.
Make data ready for submission
$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.
// 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 all tables
$tables = $db->getTables();
foreach ($tables as $table) {
echo $table . '<br>';
}
Output:
users
// 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 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') ;
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