SQL

SQL abstraction layer for using MySQL. This class is provided where the PHP version is problematic with ADOdb and its methods emulate those found in ADODBSQL.php. NB As of v3.1.1, adoDB is used solely to upgrade the database structure.

version 1
package wikindx4\core\sql
author Mark Grimshaw

 Methods

SQL

__construct() 

Return a AVG() clause

avg(string $clause) : string

Parameters

$clause

string

Returns

string

Beautify very briefly a SQL statement to facilitate debugging.

beautify(string $sqlStatement) : string

Return Sql instruction packaged in a nice HTML

author Stephane Aulery

Parameters

$sqlStatement

string

Default is ""

Returns

string

Create a CASE WHEN() THEN clause

caseWhen(string $subject, string $test, string $result, string $default, boolean $tidy) : string

Parameters

$subject

string

$test

string

$result

string

$default

string

Default is FALSE

$tidy

boolean

Default is TRUE. If TRUE, format fields for database type

Returns

string

Close SQL database

closeMySQL() 
author Mark Grimshaw
version 1

Create a COALESCE clause: "COALESCE($fields) $alias"

coalesce(mixed $fields, string $alias) : string

Parameters

$fields

mixed

Array of field names or single field name

$alias

string

Returns

string

Create a CONCAT clause

concat(array $array, string $separator) : string

Parameters

$array

array

$separator

string

Default is FALSE. If TRUE, CONCAT_WS() is used, else CONCAT().

Returns

string

Create a COUNT() clause

count(string $field, string $operator, string $comparison, boolean $distinct, string $alias) : string

Parameters

$field

string

Database field to count

$operator

string

Optional =, !=, >, <. Default is FALSE

$comparison

string

Comparison following $operator. Default is FALSE

$distinct

boolean

TRUE/FALSE (default). COUNT(DISTINCT field)

$alias

string

COUNT(field) AS $alias. Default is FALSE

Returns

string

Create SQL tables

createSQL(string $xmlFile) 
author Mark Grimshaw
version 1

Parameters

$xmlFile

string

XML file (used for extra tables used in plug-in modules). Default is FALSE

Create a table

createTable(string $newTable, array $fieldsArray) 

Parameters

$newTable

string

$fieldsArray

array

Return a ratio alias of $field / number days since e.g.

dateDiffRatio(string $field, string $denominator, boolean $alias, boolean $avg, int $round, mixed $otherFields, boolean $group) : int

resource added.

Parameters

$field

string

(e.g. 'resourcemiscAccesses', or 'resourceattachmentDownloads')

$denominator

string

(e.g. 'resourcetimestampTimestampAdd')

$alias

boolean

Default is FALSE

$avg

boolean

Default is FALSE. If TRUE, calculate an average of multiple $fields

$round

int

Default is 3

$otherFields

mixed

FALSE (default) or comma-delimited list of database fields to include in the GROUP BY

$group

boolean

FALSE (default) or GROUP BY $field and $otherFields

Returns

int

Create a condition clause for a time interval: "DATE_SUB($fromTime, INTERVAL $limit $timescale)"

dateIntervalCondition(string $limit, string $timescale, string $fromTime) : string

Parameters

$limit

string

$timescale

string

Default is 'day'

$fromTime

string

Default is 'now'

Returns

string

Execute a DELETE statement

delete(string $table) 

NB Unless you want to delete all rows from a table, set the condition first!

author Mark Grimshaw
version 1

Parameters

$table

string

Delete a WIKINDX database cache

deleteCache(string $field) 
author Mark Grimshaw
version 1

Parameters

$field

string

Add delimiters to statements

delimit(string $stmt, string $delimiter) : string
author Mark Grimshaw
version 1

Parameters

$stmt

string

$delimiter

string

'backtick', 'singleQuote', 'doubleQuote' etc. based on MySQL

Returns

stringdelimited statement

Drop a table

dropTable(string $table) 

Parameters

$table

string

Create an EXISTS() clause

existsClause(string $stmt, boolean $not) : string

Parameters

$stmt

string

IN ($stmt)

$not

boolean

Default is FALSE

Returns

string

Fetch one field value from the database

fetchOne(object $recordset) : string
author Mark Grimshaw
version 2

Parameters

$recordset

object

Returns

string

Fetch a row from the database

fetchRow(object $recordset) : array
author Mark Grimshaw
version 2

Parameters

$recordset

object

Returns

array

SQL stored function to strip HTML from field value (cf WIKINDX Search functions).

fnStripHtml() 

Taken from: http://www.artfulsoftware.com/infotree/queries.php#567

However, as MySQL 5.x requires SUPER privileges or a server variable to be set, this cannot be used out of the box. Kept here in case future MySQL allows it 'out of the box' again.

Set up the SQL conditions for the next query.

formatConditions(mixed $condition, boolean $notEqual, boolean $returnString) : string

Conditions should be set before almost every SQL query. After the query is executed, the conditions are reset automatically. Multiple conditions are joined with $this->conditionSeparator which by default is set to $this->and (it could be $this->or). $this->conditionSeparator is reset automatically after each query.

Parameters

$condition

mixed

Array of field => condition conditions or formatted condition string

$notEqual

boolean

Default is FALSE

$returnString

boolean

Default is FALSE. If TRUE, don't set the condition but return a formatted condition string instead

Returns

stringOptional return

Format multiple conditions for one field using ' OR '

formatConditionsOneField(mixed $condition, string $field, boolean $notEqual, boolean $tidy) 

Conditions should be set before almost every SQL query. After the query is executed, the conditions are reset automatically. Multiple conditions are joined with $this->conditionSeparator which by default is set to $this->and (it could be $this->or). $this->conditionSeparator is reset automatically after each query.

Parameters

$condition

mixed

Array of field => condition conditions or formatted condition string

$field

string

$notEqual

boolean

Default is FALSE

$tidy

boolean

Format the field for the database type. Default is TRUE.

Format fields for database type

formatFields(array $fields, boolean $withExceptions) : string
author Mark Grimshaw
version 1

Parameters

$fields

array

$withExceptions

boolean

Default is FALSE

Returns

string

Format tables for database type

formatTables(mixed $tables, boolean $brackets) : string

Parameters

$tables

mixed

Array of tables or single table

$brackets

boolean

Default is FALSE

Returns

string

Format a timestamp value as "Y-m-d H:i:s"

formatTimestamp(string $time) : string
author Mark Grimshaw
version 1

Parameters

$time

string

UNIX epoch time. Default is FALSE (in which case time() is used)

Returns

string

Free mysqli result set

freeSet(object $resultset) 

Parameters

$resultset

object

Create a GROUP BY clause

groupBy(string $field, boolean $tidy, string $having) 

Clauses are stored in $this->group array for use at the next query after which the array is emptied. You should set up your group statements before each query.

Parameters

$field

string

$tidy

boolean

Default is TRUE. If TRUE, format fields for database type

$having

string

Default is FALSE. If TRUE, group by clause has ' HAVING $having' appended to it.

Create an IF clause: "IF($field $test, $result, $default)"

ifClause(string $field, string $test, string $result, string $default) : string

Parameters

$field

string

$test

string

$result

string

$default

string

Returns

string

Create an IN() clause

inClause(string $stmt, boolean $not) : string

Parameters

$stmt

string

IN ($stmt)

$not

boolean

Default is FALSE

Returns

string

Clause for using an index in a SQL query

indexHint(string $field, string $type) 

Parameters

$field

string

$type

string

Default is 'FORCE'

Execute an INSERT statement

insert(string $table, array $fields, array $values) 
author Mark Grimshaw
version 1

Parameters

$table

string

$fields

array

$values

array

Return last auto_increment ID

lastAutoID() : int
author Mark Grimshaw
version 1

Returns

int

Create a LEFT JOIN clause.

leftJoin(string $table, string $left, string $right, boolean $tidy) 

Clauses are stored in $this->join array for use at the next query after which the array is emptied. You should set up your join statements before each query.

Parameters

$table

string

$left

string

$right

string

Default is FALSE

$tidy

boolean

Default is TRUE. If TRUE, format fields for database type

Create a LEFT JOIN clause on a subquery

leftJoinSubQuery(string $subQuery, string $left, string $right, boolean $tidy) 

Clauses are stored in $this->join array for use at the next query after which the array is emptied. You should set up your join statements before each query.

Parameters

$subQuery

string

$left

string

$right

string

Default is FALSE

$tidy

boolean

Default is TRUE. If TRUE, format fields for database type

Create a LIKE clause

like(string $first, string $test, string $last, boolean $not) : string

Parameters

$first

string

$test

string

$last

string

$not

boolean

Default is FALSE

Returns

string

Create a LIMIT clause

limit(int $limit, int $offset) 

Clauses are stored in the $this->limit string for use at the next query after which the string is reset. You should set up your limit statement before each query.

Parameters

$limit

int

$offset

int

List fields in a database table

listFields(string $table) : array
author Mark Grimshaw
version 1
see \SQL::listFields()

Parameters

$table

string

Returns

array

show all tables in db

listTables() : array
author Mark Grimshaw
version 2

Returns

array

Return number months difference between two database timestamps

monthDiff(string $date1, string $date2) : int

Parameters

$date1

string

Timestamp value from database

$date2

string

Default is FALSE. If FALSE, NOW() is assumed

Returns

int

return numRows from recordset

numRows(object $recordset) : int
author Mark Grimshaw
version 1

Parameters

$recordset

object

Returns

int

Optimize a table

optimize(string $table) 
author Mark Grimshaw
version 1

Parameters

$table

string

Create an ORDER BY clause

orderBy(string $field, boolean $tidy) 

Clauses are stored in $this->order array for use at the next query after which the array is emptied. You should set up your order statements before each query.

Parameters

$field

string

$tidy

boolean

Default is TRUE. If TRUE, format fields for database type

Create an ORDER BY clause with additional COLLATION for UTF8

orderByCollate(string $field, boolean $tidy) 

Clauses are stored in $this->order array for use at the next query after which the array is emptied. You should set up your order statements before each query.

Parameters

$field

string

$tidy

boolean

Default is TRUE. If TRUE, format fields for database type

Create an ORDER BY RAND() clause

orderByRandom() 

Clauses are stored in $this->order array for use at the next query after which the array is emptied. You should set up your order statements before each query.

prepend the configured table name to the field names

prependTableToField(string $table, mixed $fields) : mixed

Parameters

$table

string

$fields

mixed

Array of field names or single field name

Returns

mixed

execute queries and return recordset

query(string $querystring, boolean $saveSession) : object
author Mark Grimshaw
version 1

Parameters

$querystring

string

$saveSession

boolean

Default is FALSE

Returns

object

execute queries and return recordset

queryNoError(string $querystring) : object

Ignore error warnings (used with SYSTEMCHECK.php)

author Mark Grimshaw
version 1

Parameters

$querystring

string

query

Returns

object

create the entire querystring but do not execute

queryNoExecute(string $querystring, boolean $saveSession) : string
author Mark Grimshaw
version 1

Parameters

$querystring

string

$saveSession

boolean

Default is FALSE

Returns

string

Read a WIKINDX database cache

readCache(string $field) : array
author Mark Grimshaw
version 1

Parameters

$field

string

Returns

array

Create a REGEXP clause

regexp(string $first, string $test, string $last, boolean $not) : string

Parameters

$first

string

$test

string

$last

string

$not

boolean

Default is FALSE

Returns

string

Rename a table

renameTable(string $oldTable, string $newTable) 

Parameters

$oldTable

string

$newTable

string

Create a REPLACE clause: "REPLACE(' .

replace(string $field, string $find, string $replace, boolean $tidy) : string

$field . ", '$find', '$replace')"

Parameters

$field

string

$find

string

$replace

string

$tidy

boolean

Default is TRUE. If TRUE, format fields for database type

Returns

string

reset various strings and arrays used in subclauses

resetSubs() 

Store and restore various and arrays used in subclauses

restore(boolean $restore) 

Parameters

$restore

boolean

Default is FALSE

Create a ROUND() clause

round(string $clause, boolean $alias, int $round) : string

Parameters

$clause

string

$alias

boolean

Default is FALSE

$round

int

Default is 3

Returns

string

Execute SELECT statement

select(array $tables, mixed $fields, boolean $distinct, boolean $tidyFields, boolean $alias) : object
author Mark Grimshaw
version 1

Parameters

$tables

array

$fields

mixed

Array of fields or can be '*'

$distinct

boolean

Default is FALSE

$tidyFields

boolean

Format fields for SQL. Default is TRUE

$alias

boolean

Default is FALSE

Returns

objectrecordset

Execute a "SELECT FROM_UNIXTIME(AVG(UNIX_TIMESTAMP($field))) AS $field FROM $table" statement

selectAverageDate(string $table, string $field) : object
author Mark Grimshaw
version 1

Parameters

$table

string

$field

string

Returns

objectrecordset

Execute a "SELECT COUNT(*) AS `count`, $field FROM $table" statement

selectCount(string $table, string $field) : object

NB 'count' field in recordset

author Mark Grimshaw
version 1

Parameters

$table

string

$field

string

Returns

objectrecordset

Execute a "SELECT COUNT(DISTINCT $field) AS `count` $table $subQuery" statement

selectCountDistinctField(string $table, string $field, string $subQuery) : object

NB 'count' field in the recordset

author Mark Grimshaw
version 1

Parameters

$table

string

$field

string

$subQuery

string

Default is FALSE

Returns

objectrecordset

Create a "SELECT COUNT(DISTINCT $field) AS `count` $table $subQuery $clause" statement without executing

selectCountDistinctFieldNoExecute(string $table, string $field, string $subQuery, string $clause) : string

NB 'count' field in the recordset

author Mark Grimshaw
version 1

Parameters

$table

string

$field

string

$subQuery

string

Default is FALSE

$clause

string

Default is FALSE

Returns

string

Execute a "SELECT COUNT(DISTINCT $field) AS `count` $subQuery" statement

selectCountFromSubquery(string $field, string $subQuery) : object
author Mark Grimshaw

Parameters

$field

string

$subQuery

string

Returns

objectrecordset

Create a "SELECT COUNT(DISTINCT $field) AS `count` $subQuery $clause" statment without executing

selectCountFromSubqueryNoExecute(string $field, string $subQuery, string $clause) : string
author Mark Grimshaw

Parameters

$field

string

$subQuery

string

$clause

string

Default is FALSE

Returns

string

Execute a "SELECT COUNT(*) AS `count`, $field FROM $table" statement

selectCountMax(string $table, string $field) : object

NB 'count' field in recordset. MAX is achieved by grouping and ordering on $field

author Mark Grimshaw
version 1

Parameters

$table

string

$field

string

Returns

objectrecordset

Execute a "SELECT $field, COUNT($field) AS `count` $otherFields $table $subQuery" statement

selectCounts(string $table, string $field, array $otherFields, string $subQuery, boolean $group) : object
author Mark Grimshaw
version 1

Parameters

$table

string

$field

string

$otherFields

array

Other fields to add to the query. Default is FALSE

$subQuery

string

Default is FALSE

$group

boolean

Default is TRUE

Returns

objectrecordset

Create a "SELECT $field, COUNT($field) AS `count` $otherFields $table $subQuery $clause" statement without executing

selectCountsNoExecute(string $table, string $field, array $otherFields, string $subQuery, boolean $group, string $clause) : string
author Mark Grimshaw
version 1

Parameters

$table

string

$field

string

$otherFields

array

Other fields to add to the query. Default is FALSE

$subQuery

string

Default is FALSE

$group

boolean

Default is TRUE

$clause

string

Default is FALSE

Returns

string

Execute a SELECT statement with a subquery

selectFromSubQuery(array $tables, mixed $fields, string $subQuery, boolean $distinct, boolean $tidy) : object

If $tables is FALSE, statement is "SELECT $field $subQuery" else it is "SELECT $field $subQuery $tables"

author Mark Grimshaw
version 1

Parameters

$tables

array

$fields

mixed

Array of fields or '*'

$subQuery

string

$distinct

boolean

Default is FALSE

$tidy

boolean

Format fields. Default is TRUE

Returns

objectrecordset

Execute SELECT MAX() statement

selectMax(string $table, string $maxField, boolean $alias, array $otherFields, string $subQuery) : object
author Mark Grimshaw
version 1

Parameters

$table

string

$maxField

string

$alias

boolean

Default is FALSE

$otherFields

array

Other fields to add to the query. Default is FALSE

$subQuery

string

Default is FALSE

Returns

objectrecordset

Execute a SELECT MIN() statement

selectMin(string $table, string $minField) : object
author Mark Grimshaw
version 1

Parameters

$table

string

$minField

string

Returns

objectrecordset

Create a SELECT statement without executing

selectNoExecute(array $tables, mixed $fields, boolean $distinct, boolean $tidyFields, string $clause, boolean $alias) : string

Either: "SELECT DISTINCT $field FROM $table $alias $clause" or "SELECT $field FROM $table $alias $clause"

author Mark Grimshaw
version 1

Parameters

$tables

array

$fields

mixed

Array of fields or '*'

$distinct

boolean

Default is FALSE

$tidyFields

boolean

Format fields. Default is TRUE

$clause

string

Default is FALSE

$alias

boolean

Default is FALSE

Returns

string

Create a SELECT statement with a subquery without executing

selectNoExecuteFromSubQuery(array $tables, mixed $fields, string $subQuery, boolean $distinct, boolean $tidy, boolean $clause) : object

If $tables is FALSE, statement is "SELECT $field $subQuery $clause" else it is "SELECT $field $subQuery $tables $clause"

author Mark Grimshaw
version 1

Parameters

$tables

array

$fields

mixed

Array of fields or '*'

$subQuery

string

$distinct

boolean

Default is FALSE

$tidy

boolean

Format fields. Default is TRUE

$clause

boolean

Default is FALSE

Returns

objectrecordset

Create a SELECT statement without executing

selectNoExecuteWithExceptions(array $tables, mixed $fields, boolean $distinct) : string
author Mark Grimshaw
version 1
see \SQL::select()

Parameters

$tables

array

$fields

mixed

Array of fields or '*'

$distinct

boolean

Default is FALSE

Returns

string

Execute a SELECT statement

selectWithExceptions(array $tables, mixed $fields, boolean $distinct) : object
author Mark Grimshaw
version 1
see \SQL::select()

Parameters

$tables

array

$fields

mixed

Array of fields or '*'

$distinct

boolean

Default is FALSE

Returns

objectrecordset

Create a subquery from a SQL statement

subQuery(string $stmt, boolean $alias, boolean $from, boolean $clause) : string
author Mark Grimshaw
version 1

Parameters

$stmt

string

Pre-defined SQL stmt (which may be a subquery itself)

$alias

boolean

Boolean table alias sometimes required for subquery SELECT statements. Default is FALSE

$from

boolean

TRUE If FALSE, don't add the initial 'FROM'

$clause

boolean

Default is FALSE. If TRUE, add all conditions, joins, groupBy, orderBy etc. clauses

Returns

string

Create a subquery from SQL fields

subQueryFields(array $fields, string $subquery, boolean $alias, boolean $clause, boolean $distinct, boolean $tidy) : string

If $alias is FALSE: 'FROM (SELECT $distinct $field $subquery $clause)' If $alias is TRUE: 'FROM (SELECT $distinct $field $subquery $clause) AS '

author Mark Grimshaw
version 1

Parameters

$fields

array

$subquery

string

Formatted subquery string

$alias

boolean

Boolean table alias sometimes required for subquery SELECT statements. Default is FALSE

$clause

boolean

Default is FALSE. If TRUE, add all conditions, joins, groupBy, orderBy etc. clauses

$distinct

boolean

Default is FALSE

$tidy

boolean

Format fields for SQL queries. Default is TRUE

Returns

string

Create a SUM() statement

sum(string $field, string $alias) : string

Parameters

$field

string

$alias

string

Default is FALSE

Returns

string

Format field values for database type

tidyInput(string $string) : string

Fields are trimmed

author Mark Grimshaw
version 1

Parameters

$string

string

Returns

string

Format field values for database type

tidyInputClause(string $string) : string

For use with ORDER or GROUP by clauses

author Mark Grimshaw
version 1

Parameters

$string

string

Returns

string

Format field values for database type

tidyInputNoTrim(string $string) : string

Fields are not trimmed

author Mark Grimshaw
version 1

Parameters

$string

string

Returns

string

Create a UNION sub query -- MySQL 4.1 and above.

union(mixed $stmt, boolean $all) : string

Parameters

$stmt

mixed

string or array select statement(s) to be unionized

$all

boolean

Default FALSE. Set to TRUE to have 'UNION ALL'

Returns

string

Execute an UPDATE statement for an array of fields

update(string $table, array $updateArray) 
author Mark Grimshaw
version 1
see \SQL::update()

Parameters

$table

string

$updateArray

array

Execute an UPDATE statement setting the fields to NULL

updateNull(string $table, array $nulls) 
author Mark Grimshaw
version 1
see \SQL::updateNull()

Parameters

$table

string

$nulls

array

Array of fields to set to NULL

Execute an UPDATE statement for a single field

updateSingle(string $table, string $set) 
author Mark Grimshaw
version 1

Parameters

$table

string

$set

string

Set statement

Execute an UPDATE statement for an array of fields, setting the timestamp of a field

updateTimestamp(string $table, array $updateArray) 

If there is no value for a $updateArray key, the timestamp is set to NOW()

author Mark Grimshaw
version 1
see \SQL::updateTimestamp

Parameters

$table

string

$updateArray

array

Create an UPPER clause

upper(string $field, boolean $tidy) : string

Parameters

$field

string

$tidy

boolean

Default is TRUE. If TRUE, format fields for database type

Returns

string

Create a WHERE() statement

whereStmt(array $conditions, string $join) : string

Parameters

$conditions

array

$join

string

Default is ''

Returns

string

Write a WIKINDX database cache

writeCache(string $field, array $array) 
author Mark Grimshaw
version 1

Parameters

$field

string

$array

array

Format fields as aliases

formatAlias(array $array, string $table) : string
author Mark Grimshaw
version 1

Parameters

$array

array

Field => Alias

$table

string

Default is FALSE. If TRUE, prepend table name to alias

Returns

string

Format fields as aliases

formatAliasWithExceptions(array $array) : string

Array keys are checked for UNIX_TIMESTAMP or DATE_FORMAT

author Mark Grimshaw
version 1

Parameters

$array

array

Field => Alias

Returns

string

Format fields for an UPDATE statement

formatUpdate(array $array) : string
author Mark Grimshaw
version 1
see \ADOSQL::update

Parameters

$array

array

Returns

string

Format field values for database type

formatValues(mixed $values) : string

Parameters

$values

mixed

Array of values or single value

Returns

string

Open SQL database

openMySQL() : boolean
author Mark Grimshaw
version 1

Returns

boolean

Add slashes to string

sqlAddSlashes(string $element) : string

Parameters

$element

string

Returns

string

Formulate subclause after main query

subClause() : string

Returns

string

 Properties

 

$alias 
 

$and 
 

$asc 
 

$ascDesc 
 

$condition 
 

$conditionSeparator 
 

$dbaseType 
 

$desc 
 

$equal 
 

$error 
 

$escapeString 
 

$from 
 

$greater 
 

$group 
 

$handle 
 

$join 
 

$less 
 

$limit 
 

$minus 
 

$multiConditionSeparator 
 

$notEqual 
 

$or 
 

$order 
 

$plus 
 

$reset 
 

$sqlstring 
 

$unbuffered 
 

$config 
 

$errors 
 

$joinUpdate 
 

$restoreArray 
 

$utf8 
 

$vars