project pages:  
PHPCoreStdfuncs PDOAccess CoreTable CoreGroup CoreForm CustomPrint
PHPCoreStdfuncs PDOAccess CoreTable CoreGroup
Personal Projects:
MyQuotes - Ihr persönlicher Aktienrechner
 

PDOAccess - A useful Library to simplify Database queries

The File pdo_access.inc.php contains a class also called pdo_access. It must be instantiated, pdo_access does not work in a static way. The constructor requires at least one argument, a dsn (Data Source Name), optional a username and a password to connect to the specified database.

Table of Contents

Sourceforge

Sourceforge Projectsite & Download

Credits

Corvent Ltd. is our company which made the development of this library possible

Requirements

PHP 5.1

top  

Connecting to the Database

The simple case, an sqlite File as Database

$db = new pdo_access("sqlite:demo.sqlite");
top  

Connecting to a mySQL Database

$db = new pdo_access("mysql:host=www.example.com;dbname=demo", "username", "pass");
top  

Querying the database

top  

getSQLResultAsArray

This will probably be the most used method. It just takes an sql query and returns the result from the query as numeric array of the matching records. The records are Hashtables haveing the columnname as key and the value as value.

$employees = $db->getSQLResultAsArray("SELECT * FROM employees");
debug::show($employees, "employees");
employees
0 id 1 name smith firstname bill salery 60000 department development
1 id 2 name oklahoma firstname joe salery 45000 department finance
2 id 6 name simpson firstname homer salery 24000 department security
3 id 8 name simpson firstname lisa salery 50900 department development
4 id 9 name simpson firstname marge salery 46000 department finance
5 id 13 name simpson firstname bart salery 32454 department finance
6 id 14 name newbie firstname john salery 45463 department sales

If you are not intrested in a numeric array you may define a unique column to use for the keys. It may be passed in the secound optional argument.

$employees = $db->getSQLResultAsArray("SELECT * FROM employees", "id");
debug::show($employees, "employees");
employees
1 name smith firstname bill salery 60000 department development
2 name oklahoma firstname joe salery 45000 department finance
6 name simpson firstname homer salery 24000 department security
8 name simpson firstname lisa salery 50900 department development
9 name simpson firstname marge salery 46000 department finance
13 name simpson firstname bart salery 32454 department finance
14 name newbie firstname john salery 45463 department sales

The column used as key needs not to be numeric, but unique, if its not you may get a result like this:

$employees = $db->getSQLResultAsArray("SELECT * FROM employees", "name");
debug::show($employees, "employees");
employees
smith id 1 firstname bill salery 60000 department development
oklahoma id 2 firstname joe salery 45000 department finance
simpson id 13 firstname bart salery 32454 department finance
newbie id 14 firstname john salery 45463 department sales
top  

getSQLResultAsHashtable

This method is nice whereever you need an association from e.g. an id to a name

$employees = $db->getSQLResultAsHashtable("SELECT id, name FROM employees");
debug::show($employees, "employees");
employees
1 smith
2 oklahoma
6 simpson
8 simpson
9 simpson
13 simpson
14 newbie

The first column given will be the key of your hashtable (here id). If you have exacly 2 columns in your query the second one will be the value.

These results can be nicely transformed into a HMTL-Selector Element

top  

getSQLResultFirstRow

This function is suitable to get a specific record from

$employees = $db->getSQLResultFirstRow("SELECT * FROM employees WHERE id='6'");
debug::show($employees, "employee id 6");
employee id 6
id 6
name simpson
firstname homer
salery 24000
department security

If no records match the method returns false

If more than one records match, by default the method returns false too (to indicate that maybe the database has not that counsistency that you assume), you may change this behaviour by passing the second argument "allowMultipleMatch" the value true, than you get the first record as result

$employee = $db->getSQLResultFirstRow("SELECT * FROM employees WHERE id=4");
debug::show($employee, "No match");

$employee = $db->getSQLResultFirstRow("SELECT * FROM employees WHERE id IN (6, 8)");
debug::show($employee, "Multiple match, default");

$employee = $db->getSQLResultFirstRow("SELECT * FROM employees WHERE id IN (6, 8)", true);
debug::show($employee, "Multiple match, ingnoring multiple Result");
No match = false
Multiple match, default = false
Multiple match, ingnoring multiple Result
id 6
name simpson
firstname homer
salery 24000
department security
top  

getSQLResultFirstCol

Nice if you are intrested in a valueList of a column

$ids = $db->getSQLResultFirstCol("SELECT id FROM employees");
debug::show($ids, "all ids");
all ids
0 1
1 2
2 6
3 8
4 9
5 13
6 14
top  

getSQLResultFirstCell

Nice if you are intrested in one single Cell-value

$name = $db->getSQLResultFirstCell("SELECT name FROM employees WHERE id=6");
debug::show($name, "name");
name = simpson
  top

Data manipulation

pdo_access provides also methods to insert, update and delete records

  top

insertValues

insertValues($tableName, $values);

$newEntry = array("name" => "newbie", "firstname" => "john", "salery" => 45463, "department" => "sales");
$newId = $db->insertValues("employees", $newEntry);
debug::show($newId, "newId");
newId = 15

The return-value is the new generated id for the new recort (if there is an auto-increment column) otherwise it's the number of affected rows, or false in case of faliure

All Table Content now:

employees
0 id 1 name smith firstname bill salery 60000 department development
1 id 2 name oklahoma firstname joe salery 45000 department finance
2 id 6 name simpson firstname homer salery 24000 department security
3 id 8 name simpson firstname lisa salery 50900 department development
4 id 9 name simpson firstname marge salery 46000 department finance
5 id 13 name simpson firstname bart salery 32454 department finance
6 id 14 name newbie firstname john salery 45463 department sales
7 id 15 name newbie firstname john salery 45463 department sales
top  

updateValues

updateValues($tableName, $condition, $values);

The return value represents the number of changed rows, or fals on failure

All Table Content now:
employees
0 id 1 name smith firstname bill salery 60000 department development
1 id 2 name oklahoma firstname joe salery 45000 department finance
2 id 6 name simpson firstname homer salery 24000 department security
3 id 8 name simpson firstname lisa salery 50900 department development
4 id 9 name simpson firstname marge salery 46000 department finance
5 id 13 name simpson firstname bart salery 32454 department finance
6 id 14 name newbie firstname john salery 45463 department sales
7 id 15 name newbie firstname john salery 45463 department sales
top  

deleting records

delete($tableName, $condition);

$result = $db->delete("employees", "id=15");
debug::show($result, "result");
result = 1

For security reasons the condition must not be an empty string, to delete all records you may pass something like "1=1" as codition

return-value is the number of deleted records

All Table Content now:
employees
0 id 1 name smith firstname bill salery 60000 department development
1 id 2 name oklahoma firstname joe salery 45000 department finance
2 id 6 name simpson firstname homer salery 24000 department security
3 id 8 name simpson firstname lisa salery 50900 department development
4 id 9 name simpson firstname marge salery 46000 department finance
5 id 13 name simpson firstname bart salery 32454 department finance
6 id 14 name newbie firstname john salery 45463 department sales
top  

Misc. Functions

pdo_access provides 3 additional function to get simple statistic information from a table

top  

countRecords

countRecords($tableName[, $condition]);

$result = $db->countRecords("employees", "salery > 50000");
debug::show($result, "result");
result = 2

This is a shortcut for "getSQLResultFirstCell("SELECT COUNT(*) FROM $tableName WHERE $condition");"

top  

getDistinctValues

getDistinctValues($tableName, $column, [, $condition]);

$departments = $db->getDistinctValues("employees", "department");
debug::show($departments, "departments");
departments
0 development
1 finance
2 sales
3 security

This is a shortcut for "getSQLResultFirstCol("SELECT DISTINCT($column) FROM $tableName WHERE $condition");"

top  

countDistinctValues

countDistinctValues($tableName, $column, [, $condition]);

$departments = $db->countDistinctValues("employees", "department");
debug::show($departments, "departments");
departments = 4

This is a shortcut for "getSQLResultFirstCell("SELECT CONT(DISTINCT($column)) FROM $tableName WHERE $condition");"