DBAL Manual

For Version 2.2 (Database.php Version 1)

Contents

About
Dbal Class
Dbtal Class
DBInteraction Class
Database Class
Syntax

About

( contents )

These classes were developed by Sam Stevens, and are licenced under the MIT licence (see files for details).

At the moment these classes are only compatible/tested with MySql. they may work with other databases that supports the "Show Tables" and "Describe Table" features

If you have found a problem with these or have any queryies please contact me at sam@xnet.tk

Changes in 2.2

Fixed multiple bugs and made improvements to speed and functionality,
Added DBInteraction class
Added Caching to DBAL and DBTAL Classes

Caching

To enable caching of stored queries and table structures by the DBTAL, $dbalCacheDir Must be set to the caching directory and must have a trailing slash (Directory must be writable from the http user, www-data in linux).

DBAL Class

( contents )

AKA Database Abstraction Layer Class.

Debug

This supports debug messages, set debug to true on construction or use function Debug(true,<level>)

There are four debug levels : 1 = all information, 2=less information, 3=warnings, 4 =errors.
Lower settings will contain messages from higher levels (ie. level 1 will contain level 4 messages )

Function Listing

Constructor ($host,$user,$pass, $database, $debug=false,$level=4)

Fairly straight forward, set debug to true to get debug messages (quite verbose).
Can throw exceptions!

Debug ($newValue,$level=4)

$newValue must either be true or false. (debug on or off)
$level as described above

DebugText ($text,$level=4)

Used to *report* debug messages of a specified serverity/level
this isnt private since DBTAL uses it.

RawQuery ($query)

Performs an SQL query directly on the database and returns the results

SelectTable ($table)

Select the table to be used when interacting with *this* class (ie. not when using a dbtal directly)
Selecting a non-existant table will result in a level 3 debug message

GetDBTAL ($table)

Returns a DBTAL class object, for the specified table
Selecting a non-existant table will result in a level 3 debug message

TableSelected ()

Returns true or false depending if SelectTable has been used (and suceeded...).

Insert ($values)

Insert a row into the database (see Syntax)

Update ($where, $values)

Updates row(s) in the database (see Syntax)

Delete ($where)

Deletes row(s) in the database (see Syntax)

Select ($where,$cols="",$order="",$limit="")

You guessed it, Selects row(s) from a database (see Syntax)
Be sure to use the limit field if you are only getting one/limited number of row(s)

DBTAL Class

( contents )

AKA Database Table Abstraction Layer Class

Please note that the default limit on select queries is 1

Function Listing

Constructor ($tableName, $inDb, $owner=null)

This is usually called from the dbal class.
$tableName, the table to use (string)
$inDb, the database to use (must be initialised)
$owner, the DBAL class owner (to report debug messages, not required)

GetDefaultLimit ()

This returns the current default limit on select queries

SetDefaultLimit ($newLimit)

Sets a new Limit on select queries.
Must be numeric.

Private GenerateSelect/Insert/Update/Delete ()

These generate the stored queries required for the operation of the class.

Insert ($values)

Insert a row into the database (see Syntax)

Update ($where, $values)

Updates row(s) in the database (see Syntax)

Delete ($where)

Deletes row(s) in the database (see Syntax)

Select ($where,$cols="",$order="",$limit="")

You guessed it, Selects row(s) from a database (see Syntax)
Be sure to use the limit field if you are only getting one/limited number of row(s)

DBInteraction Class

( contents )

This is a helper class to be used to simplify the interaction with the database and provides methods to get, modify, delete and list records.
It is used by inheritence, since all functions are protected.

Function Listing

protected initialise ($tableName,$limit="")

This must be called by the extending class to initialise the class, this will also set the default limit on queries

initialised ()

This returns true or false depending on weather the class is working.

protected getStructure ()

This gets the table structure. (See DBAL->GetStructure)

protected getRecord ($value,$col="pri",$where=array(), $limit = 1)

This gets 1 or more records given a value and a column, defaults to using the primary column. You may also specify additional where clauses.
Example:

$this->getRecord(1); //Gets record 1
$this->getRecord("Bob","Name",array("Title"=>"Mr")); //Gets the record with name equal to bob and where title is Mr

Returns the record or false on error.

protected addRecord ($values)

Adds a new record and returns the new index number, or false on error.
Takes an array as input, ie array("name=>"bob")

protected modifyRecord ($id,$values)

Modifys a record, given the ID (of primary key) and the values to change.
$values is an array.
Returns true or false

protected deleteRecord ($id)

Deletes a record given the ID (of primary key)
Returns true or false

protected listRecords ($cols="",$where=array())

Lists records, All by default, you can specify the columns to display and provide a where clause
Returns true or false

Database Class

( contents )

This supports stored queries and parameter substitution.

This is for MySql Database Servers Only.

Stored Queries

When saving a query, it must be given a name so it can be used, if the name is the same as on that already exists, it will be replaced.
{} can be use for substitution when it is used.
Example Sql : Select {columns} From some_table Where {where}
the {columns} and {where} can be replaced when used the StoredQuery function

Example:
$Db->SaveQuery("someQuery","Select {columns} From some_table Where {where}");
$Db->StoredQuery("someQuery",array("columns"=>"*","where"=>"id = 1"));
Will execute the query "Select * From some_table Where id = 1"

Function Listing

Constructor ($host,$user,$password,$database)

Can throw exceptions.

SaveQuery ($name,$text)

This will save a query for future use with a specified name (see above)

Query ($query)

This performs a database query.
Errors while performing the query will kill the script.

PerformedQueries ()

Returns the number of queries performed on the database upto now.

StoredQuery ($name,$values)

Performs a query that was stored earlier. (see above)
will return false if the query name was not found
$values, must be an array, with the value(s) to be replaced as the keys and the replacement value as the values
ie. array("columns"=>"*","where"=>"id = 1")

Syntax

( contents )

Select

Select ($where,$cols="",$order="",$limit="")

Returns the mysql result.

$where

Must be an Array.
Keys must be names of columns (you only need to specify the ones you need).
use "pri" as key to use the primary column

$cols if empty "*"(all columns) is used.
if "pri" is used primary key is used.
seperate columns with a ", "
$order use sql order syntax ie "[column name] ASC/DESC[, next ordering statement]
$limit this is the limit on the amount of returned queries (1 by default)
Example:
$db->Select(array("pri"=>1),"","col1 ASC",""); //Select * From table Where ID = 1
$db->Select() // Select * From table

Insert

Insert ($values)

Returns the last insert id if successful.

$values

Must be An Array.
Keys must be the column names,
ie. array("col1"=>123,"col2"=>"abcd")

Example:
$db->Insert(array("col1"=>123,"col2"=>"abcd"));

Update

Update ($where, $values)

Returns fals if no rows affected, otherwise mysql result

$where The id of the record to update (primary key column)
Must be numeric
$values See Insert
Example:
$db->Update("1",array("col1"=>123,"col2"=>"abcd");

Delete

Delete ($where)

$where

The id (primary key) of the record to delete

Example:
$db->Delete(1);