\myMVC_GenericSQL

Generic class to query the database

class testDB extends myMVC_BaseDB { public function construct() { parent::construct('test', array('id' => 'testDB::checkId', 'firstname' => 'testDB::checkName', 'lastname' => 'testDB::checkName', ), array('id' => 'autoincrement'), // Unique Key: firstname+lastname array(array('firstname', 'lastname'))); }

public static checkId($type, $value) { return true; } ... }

Summary

Methods
Properties
Constants
__construct()
__call()
callFinder()
callIterator()
callCounter()
findAll()
iterateAll()
getCountAll()
findByPK()
isRecordUnique()
save()
delete()
deleteAll()
sqlExportByParameter()
now()
nowFormatted()
checkAutoIncrementalId()
checkSignedIntNotNull()
checkUnsignedIntNotNull()
checkSignedIntNull()
checkUnsignedIntNull()
checkSignedMediumIntNotNull()
checkUnsignedMediumIntNotNull()
checkSignedMediumIntNull()
checkUnsignedMediumIntNull()
checkSignedSmallIntNotNull()
checkUnsignedSmallIntNotNull()
checkSignedSmallIntNull()
checkUnsignedSmallIntNull()
checkSignedTinyIntNotNull()
checkUnsignedTinyIntNotNull()
checkSignedTinyIntNull()
checkUnsignedTinyIntNull()
checkBoolNotNull()
checkBoolNull()
checkString255NotNull()
checkString255Null()
checkTextNotNull()
checkTextNull()
checkEMailNotNull()
checkEMailNull()
checkURLNotNull()
checkURLNull()
checkDomainnameNotNull()
checkDomainnameNull()
checkDateNotNull()
checkDateNull()
checkTimeNotNull()
checkTimeNull()
checkDateTimeNotNull()
checkDateTimeNull()
checkIPNotNull()
checkIPNull()
checkMacNotNull()
checkMacNull()
checkNetworkNotNull()
checkNetworkNull()
checkMaskNotNull()
checkMaskNull()
executeQuery()
quoteSql()
quoteSqlForLike()
No public properties found
No constants found
registerFinder()
registerCounter()
saveValues()
createWhere()
findByParameter()
getCountByParameter()
findBySQLQuery()
checkFields()
checkUniques()
errno()
error()
getDatabaseConnection()
quoteSqlString()
quoteSqlStringForLike()
execute()
executeSelect()
getValueArrayFromResult()
getValuesFromResult()
getRowsNum()
freeResult()
$table
$pk
$fields
$uniques
$autoincrement
$values
$oldValues
$engine
$finders
$counters
$server
$login
$password
$database
N/A
No private methods found
No private properties found
N/A

Properties

$table

$table : 

Type

$pk

$pk : 

Type

$fields

$fields : 

Type

$uniques

$uniques : 

Type

$autoincrement

$autoincrement : 

Type

$values

$values : 

Type

$oldValues

$oldValues : 

Type

$engine

$engine : 

Type

$finders

$finders : 

Type

$counters

$counters : 

Type

$server

$server : 

Type

$login

$login : 

Type

$password

$password : 

Type

$database

$database : 

Type

Methods

__construct()

__construct() 

Constructor.

__call()

__call(  $name,   $args) 

Dynamic get|set method If the function does not exists (unknown get|set Key), an error will be displayed

Parameters

$name

The name of the unknown called function

$args

The arguments

callFinder()

callFinder(string  $name, array  $fields, string  $order = NULL, integer  $limit = NULL, integer  $offset = NULL) : array

Calls a defined finder (see registerFinder)

Parameters

string $name

The name of the finder

array $fields

An associative array with the fields to use for the search (field => value)

string $order

If not NULL, field(s) to sort data

integer $limit

If not NULL, maximum of the searched data (useful for paging)

integer $offset

If not NULL, offset of the searched data (useful for paging)

Throws

\myMVC_InvalidFieldException

On invalid field

\myMVC_DatabaseException

On Problems

Returns

array —

The result as array of database class or NULL if no record was found

callIterator()

callIterator(string  $name, array  $fields, string  $order = NULL, integer  $limit = NULL, integer  $offset = NULL) : resource

Iterate throws a defined finder (see registerFinder)

Parameters

string $name

The name of the finder

array $fields

An associative array with the fields to use for the search (field => value)

string $order

If not NULL, field(s) to sort data

integer $limit

If not NULL, maximum of the searched data (useful for paging)

integer $offset

If not NULL, offset of the searched data (useful for paging)

Throws

\myMVC_InvalidFieldException

On invalid field

\myMVC_DatabaseException

On Problems

Returns

resource —

The result as database resource or NULL if no record was found

callCounter()

callCounter(string  $name, array  $fields) : integer

Calls a defined counter (see registerCounter)

Parameters

string $name

The name of the counter

array $fields

An associative array with the fields to use for the search (field => value)

Throws

\myMVC_InvalidFieldException

On invalid field

\myMVC_DatabaseException

On Problems

Returns

integer —

The count of the searched records

findAll()

findAll(string  $order = NULL, integer  $limit = NULL, integer  $offset = NULL) : array

Returns all records

Parameters

string $order

If not NULL, field(s) to sort data

integer $limit

If not NULL, maximum of the searched data (useful for paging)

integer $offset

If not NULL, offset of the searched data (useful for paging)

Throws

\myMVC_InvalidFieldException

On invalid field

\myMVC_DatabaseException

On Problems

Returns

array —

The result as array of database class or NULL if no record was found

iterateAll()

iterateAll(string  $order = NULL, integer  $limit = NULL, integer  $offset = NULL) : resource

Iterate all records

Parameters

string $order

If not NULL, field(s) to sort data

integer $limit

If not NULL, maximum of the searched data (useful for paging)

integer $offset

If not NULL, offset of the searched data (useful for paging)

Throws

\myMVC_InvalidFieldException

On invalid field

\myMVC_DatabaseException

On Problems

Returns

resource —

The result as database resource or NULL if no record was found

getCountAll()

getCountAll() : integer

Returns the count of all records

Throws

\myMVC_InvalidFieldException

On invalid field

\myMVC_DatabaseException

On Problems

Returns

integer —

The count of all records

findByPK()

findByPK(array  $pk) : \DB-Class

Get a record using the primary key of the table

Parameters

array $pk

The primary key as associative array (field => value)

Throws

\myMVC_DatabaseException

On Problems

Returns

\DB-Class —

The result as database class or NULL if no record was found

isRecordUnique()

isRecordUnique() : boolean

Check whether the current record satisfies all unique keys

Returns

boolean —

true if all unique keys are satisfied, false otherwise

save()

save() 

Save the data. It checks the PK: if the record does not exists, create a new record.

Otherwise it updates it.

Throws

\myMVC_InvalidFieldException

On invalid field

\myMVC_DatabaseException

On generic problem

delete()

delete() 

Deletes the record, using the primary key

Throws

\myMVC_InvalidFieldException

On invalid field

\myMVC_DatabaseException

On generic problem

deleteAll()

deleteAll(array  $param) 

Deletes all records that match the given parameters

Parameters

array $param

The parameters, as associative array field => value

Throws

\myMVC_InvalidFieldException

On invalid field

\myMVC_DatabaseException

On generic problem

sqlExportByParameter()

sqlExportByParameter(array  $param, array  $useLike = array(), string  $order = NULL, integer  $limit = NULL, integer  $offset = NULL, boolean  $exportToBrowser = true, integer  $separateAfter = 50) : string

Export data as SQL queries, using parameters Exportiert Datensätze als SQL anhand der eingegebenen Parametern

Parameters

array $param

The parameters, as associative array field => value

array $useLike

Associative array with the fields that should be search with a LIKE (default: empty)

string $order

If not NULL, field(s) to sort data

integer $limit

If not NULL, maximum of the searched data (useful for paging)

integer $offset

If not NULL, offset of the searched data (useful for paging)

boolean $exportToBrowser

If true (default) data will be exported to the browser If false, data will be exported in the return string

integer $separateAfter

After $separateAfter lines (default 50) a new SQL header (INSERT INTO...) will be added

Throws

\myMVC_InvalidFieldException

On invalid field

\myMVC_DatabaseException

On generic problem

Returns

string —

The export as SQL queries or NULL if no data is available

now()

now() : string

Returns the current date and time, formatted as for insert in the database

Returns

string —

The current date and time

nowFormatted()

nowFormatted() : string

Returns the current date and time, formatted as in the current locale

Returns

string —

The current date and time

checkAutoIncrementalId()

checkAutoIncrementalId(  $value,   $type) 

Check function for typical autoincrement ID (INT(11) NOT NULL)

Parameters

$value
$type

checkSignedIntNotNull()

checkSignedIntNotNull(  $value,   $type) 

Check function for Signed INT NOT NULL

Parameters

$value
$type

checkUnsignedIntNotNull()

checkUnsignedIntNotNull(  $value,   $type) 

Check function for Unsigned INT NOT NULL

Parameters

$value
$type

checkSignedIntNull()

checkSignedIntNull(  $value,   $type) 

Check function for Signed INT NULL

Parameters

$value
$type

checkUnsignedIntNull()

checkUnsignedIntNull(  $value,   $type) 

Check function for Unsigned INT NULL

Parameters

$value
$type

checkSignedMediumIntNotNull()

checkSignedMediumIntNotNull(  $value,   $type) 

Check function for Signed MEDIUMINT NOT NULL

Parameters

$value
$type

checkUnsignedMediumIntNotNull()

checkUnsignedMediumIntNotNull(  $value,   $type) 

Check function for Unsigned MEDIUMINT NOT NULL

Parameters

$value
$type

checkSignedMediumIntNull()

checkSignedMediumIntNull(  $value,   $type) 

Check function for Signed MEDIUMINT NULL

Parameters

$value
$type

checkUnsignedMediumIntNull()

checkUnsignedMediumIntNull(  $value,   $type) 

Check function for Unsigned MEDIUMINT NULL

Parameters

$value
$type

checkSignedSmallIntNotNull()

checkSignedSmallIntNotNull(  $value,   $type) 

Check function for Signed SMALLINT NOT NULL

Parameters

$value
$type

checkUnsignedSmallIntNotNull()

checkUnsignedSmallIntNotNull(  $value,   $type) 

Check function for Unsigned SMALLINT NOT NULL

Parameters

$value
$type

checkSignedSmallIntNull()

checkSignedSmallIntNull(  $value,   $type) 

Check function for Signed SMALLINT NULL

Parameters

$value
$type

checkUnsignedSmallIntNull()

checkUnsignedSmallIntNull(  $value,   $type) 

Check function for Unsigned SMALLINT NULL

Parameters

$value
$type

checkSignedTinyIntNotNull()

checkSignedTinyIntNotNull(  $value,   $type) 

Check function for Signed TINYINT NOT NULL

Parameters

$value
$type

checkUnsignedTinyIntNotNull()

checkUnsignedTinyIntNotNull(  $value,   $type) 

Check function for Unsigned TINYINT NOT NULL

Parameters

$value
$type

checkSignedTinyIntNull()

checkSignedTinyIntNull(  $value,   $type) 

Check function for Signed TINYINT NULL

Parameters

$value
$type

checkUnsignedTinyIntNull()

checkUnsignedTinyIntNull(  $value,   $type) 

Check function for Unsigned TINYINT NULL

Parameters

$value
$type

checkBoolNotNull()

checkBoolNotNull(  $value,   $type) 

Check function for boolean value NOT NULL

Parameters

$value
$type

checkBoolNull()

checkBoolNull(  $value,   $type) 

Check function for boolean value NULL

Parameters

$value
$type

checkString255NotNull()

checkString255NotNull(  $value,   $type) 

Check function for VARCHAR(255) NOT NULL

Parameters

$value
$type

checkString255Null()

checkString255Null(  $value,   $type) 

Check function for VARCHAR(255) NULL

Parameters

$value
$type

checkTextNotNull()

checkTextNotNull(  $value,   $type) 

Check function for TEXT NOT NULL

Parameters

$value
$type

checkTextNull()

checkTextNull(  $value,   $type) 

Check function for TEXT NULL

Parameters

$value
$type

checkEMailNotNull()

checkEMailNotNull(  $value,   $type) 

Check function for E-Mail address NOT NULL

Parameters

$value
$type

checkEMailNull()

checkEMailNull(  $value,   $type) 

Check function for E-Mail address NULL

Parameters

$value
$type

checkURLNotNull()

checkURLNotNull(  $value,   $type) 

Check function for URL NOT NULL

Parameters

$value
$type

checkURLNull()

checkURLNull(  $value,   $type) 

Check function for URL NULL

Parameters

$value
$type

checkDomainnameNotNull()

checkDomainnameNotNull(  $value,   $type) 

Check function for domain name NOT NULL

Parameters

$value
$type

checkDomainnameNull()

checkDomainnameNull(  $value,   $type) 

Check function for domain name NULL

Parameters

$value
$type

checkDateNotNull()

checkDateNotNull(  $value,   $type) 

Check function for date NOT NULL (Format: YYYY-MM-DD)

Parameters

$value
$type

checkDateNull()

checkDateNull(  $value,   $type) 

Check function for date NULL (Format: YYYY-MM-DD)

Parameters

$value
$type

checkTimeNotNull()

checkTimeNotNull(  $value,   $type) 

Check function for time NOT NULL (Format: HH:MM:SS)

Parameters

$value
$type

checkTimeNull()

checkTimeNull(  $value,   $type) 

Check function for time NULL (Format: HH:MM:SS)

Parameters

$value
$type

checkDateTimeNotNull()

checkDateTimeNotNull(  $value,   $type) 

Check function for date NOT NULL (Format: YYYY-MM-DD HH:MM:SS)

Parameters

$value
$type

checkDateTimeNull()

checkDateTimeNull(  $value,   $type) 

Check function for date NULL (Format: YYYY-MM-DD HH:MM:SS)

Parameters

$value
$type

checkIPNotNull()

checkIPNotNull(  $value,   $type) 

Check function for IPv4 addresses (NOT NULL)

Parameters

$value
$type

checkIPNull()

checkIPNull(  $value,   $type) 

Check function for IPv4 addresses (NULL)

Parameters

$value
$type

checkMacNotNull()

checkMacNotNull(  $value,   $type) 

Check function for MAC addresses (NOT NULL)

Parameters

$value
$type

checkMacNull()

checkMacNull(  $value,   $type) 

Check function for MAC addresses (NULL)

Parameters

$value
$type

checkNetworkNotNull()

checkNetworkNotNull(  $value,   $type) 

Check function for IPv4 network (NOT NULL)

Parameters

$value
$type

checkNetworkNull()

checkNetworkNull(  $value,   $type) 

Check function for IPv4 network (NULL)

Parameters

$value
$type

checkMaskNotNull()

checkMaskNotNull(  $value,   $type) 

Check function for IPv4 netmask (NOT NULL)

Parameters

$value
$type

checkMaskNull()

checkMaskNull(  $value,   $type) 

Check function for IPv4 netmask (NULL)

Parameters

$value
$type

executeQuery()

executeQuery(string  $sql, boolean  $linear = false,   $assoc = false) : array

Sends the given query to the engine an returns the result as array

Parameters

string $sql

The SQL query

boolean $linear

If true, all data will returned as one numeric array, otherwise (default) they will returned as array of arrays

$assoc

If true data will be returned as associative array. Default: false

Throws

\myMVC_DatabaseException

On generic problem

Returns

array —

The result as array of arrays or NULL if no data was found

quoteSql()

quoteSql(string  $value) : string

Quotes a string to use in a SQL-Query

Parameters

string $value

The string with the Query

Returns

string —

A secure SQL-String (quoted)

quoteSqlForLike()

quoteSqlForLike(string  $value, string  $prefix = '%', string  $suffix = '%') : string

Quotes a string to use in a SQL-Query (with LIKE)

Parameters

string $value

The string with the Query

string $prefix

(normally %) added BEFORE the string

string $suffix

(normally %) added AFTER the string

Returns

string —

A secure SQL-String (quoted)

registerFinder()

registerFinder(string  $name, array  $fieldsToSearch, array  $fixedFields = array()) 

Register a new finder method.

This can be called with the method callFinder or iterateFinder.

Parameters

string $name

The name of the finder

array $fieldsToSearch

An associative array with the fields to use for the search Format of the array: field => "LIKE" or field => "EQUAL"

array $fixedFields

An associative array with the "fixed" fields for the search (default: empty) Format of the array: field => value

Throws

\myMVC_DatabaseException

On Problems

registerCounter()

registerCounter(string  $name, array  $fieldsToSearch, array  $fixedFields = array()) 

Register a new counter method.

This can be called with the method callCounter.

Parameters

string $name

The name of the counter

array $fieldsToSearch

An associative array with the fields to use for the search Format of the array: field => "LIKE" or field => "EQUAL"

array $fixedFields

An associative array with the "fixed" fields for the search (default: empty) Format of the array: field => value

Throws

\myMVC_DatabaseException

On Problems

saveValues()

saveValues(array  $values) 

Saves the values of the record

Parameters

array $values

Associative array with the values from the database

createWhere()

createWhere(array  $param, array  $useLike = array()) 

Creates the WHERE-statement for the SQL query, using parameters

Parameters

array $param

The parameters, as associative array field => value

array $useLike

Associative array with the fields that should be search with a LIKE (default: empty)

findByParameter()

findByParameter(array  $param, array  $useLike = array(), string  $order = NULL, integer  $limit = NULL, integer  $offset = NULL) : resource

Get a record from the database, using parameters

Parameters

array $param

The parameters, as associative array field => value

array $useLike

Associative array with the fields that should be search with a LIKE (default: empty)

string $order

If not NULL, field(s) to sort data

integer $limit

If not NULL, maximum of the searched data (useful for paging)

integer $offset

If not NULL, offset of the searched data (useful for paging)

Throws

\myMVC_InvalidFieldException

On invalid field

\myMVC_DatabaseException

On generic problem

Returns

resource —

The result as database resource or NULL if no data was found

getCountByParameter()

getCountByParameter(array  $param, array  $useLike = array()) : integer

Get the count of the records, using parameters

Parameters

array $param

The parameters, as associative array field => value

array $useLike

Associative array with the fields that should be search with a LIKE (default: empty)

Throws

\myMVC_InvalidFieldException

On invalid field

\myMVC_DatabaseException

On generic problem

Returns

integer —

The count of the record

findBySQLQuery()

findBySQLQuery(string  $sql) : resource

Sends to the database engine an SQL query and get the result

Parameters

string $sql

The SQL query

Throws

\myMVC_DatabaseException

On generic problem

Returns

resource —

The result as database resource or NULL if no data was found

checkFields()

checkFields(array  $param, string  $type) 

Checks if all the given data are valid

Parameters

array $param

The data to check

string $type

Check type (save/search)

Throws

\myMVC_InvalidFieldException

On invalid field

checkUniques()

checkUniques() 

Checks if the unique keys are satisfied

Throws

\myMVC_InvalidFieldException

On unsatisfied unique key

errno()

errno() : integer

Wrapper for the errno-function of the selected database engine

Returns

integer —

the error number

error()

error() : string

Wrapper for the error-function of the selected database engine

Returns

string —

the error string

getDatabaseConnection()

getDatabaseConnection() : resource

Connects to the database.

This will just be done, if no connection exists yet.

Throws

\myMVC_DatabaseException

if no connection is possible

Returns

resource —

a connection resource

quoteSqlString()

quoteSqlString(string  $value) : string

Quotes a string to use in a SQL-Query

Parameters

string $value

The string with the Query

Returns

string —

A secure SQL-String (quoted)

quoteSqlStringForLike()

quoteSqlStringForLike(string  $value, string  $prefix = '%', string  $suffix = '%') : string

Quotes a string to use in a SQL-Query (with LIKE)

Parameters

string $value

The string with the Query

string $prefix

(normally %) added BEFORE the string

string $suffix

(normally %) added AFTER the string

Returns

string —

A secure SQL-String (quoted)

execute()

execute(string  $query) : boolean

Execute an INSERT, UPDATE or DELETE query

Parameters

string $query

The SQL-Query

Returns

boolean —

true on success, false on failure, last inserted ID on successfully INSERT

executeSelect()

executeSelect(string  $query) : resource

Executes a SELECT-Query

Parameters

string $query

The SQL-Query

Returns

resource —

a resource to get data on success, false otherwise

getValueArrayFromResult()

getValueArrayFromResult(resource  $result) : array

Returns a record as associative array, or NULL if no record can be returned.

Parameters

resource $result

The resource

Returns

array —

The record

getValuesFromResult()

getValuesFromResult(resource  $result) : array

Returns a record as numeric array, or NULL if no record can be returned.

Parameters

resource $result

The resource

Returns

array —

The record

getRowsNum()

getRowsNum(resource  $result) : integer

Returns the number of row of the runned query.

Parameters

resource $result

The resource

Returns

integer —

The row's number

freeResult()

freeResult(resource  $result) : boolean

Free the allocated memory for the SQL query.

Parameters

resource $result

The resource

Returns

boolean —

true on success, false on problem