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 |
__construct()
avg(string $clause) : string
string
string
beautify(string $sqlStatement) : string
Return Sql instruction packaged in a nice HTML
author | Stephane Aulery |
---|
string
Default is ""
string
caseWhen(string $subject, string $test, string $result, string $default, boolean $tidy) : string
string
string
string
string
Default is FALSE
boolean
Default is TRUE. If TRUE, format fields for database type
string
coalesce(mixed $fields, string $alias) : string
mixed
Array of field names or single field name
string
string
concat(array $array, string $separator) : string
array
string
Default is FALSE. If TRUE, CONCAT_WS() is used, else CONCAT().
string
count(string $field, string $operator, string $comparison, boolean $distinct, string $alias) : string
string
Database field to count
string
Optional =, !=, >, <. Default is FALSE
string
Comparison following $operator. Default is FALSE
boolean
TRUE/FALSE (default). COUNT(DISTINCT field
)
string
COUNT(field
) AS $alias. Default is FALSE
string
createSQL(string $xmlFile)
author | Mark Grimshaw |
---|---|
version | 1 |
string
XML file (used for extra tables used in plug-in modules). Default is FALSE
createTable(string $newTable, array $fieldsArray)
string
array
dateDiffRatio(string $field, string $denominator, boolean $alias, boolean $avg, int $round, mixed $otherFields, boolean $group) : int
resource added.
string
(e.g. 'resourcemiscAccesses', or 'resourceattachmentDownloads')
string
(e.g. 'resourcetimestampTimestampAdd')
boolean
Default is FALSE
boolean
Default is FALSE. If TRUE, calculate an average of multiple $fields
int
Default is 3
mixed
FALSE (default) or comma-delimited list of database fields to include in the GROUP BY
boolean
FALSE (default) or GROUP BY $field and $otherFields
int
dateIntervalCondition(string $limit, string $timescale, string $fromTime) : string
string
string
Default is 'day'
string
Default is 'now'
string
delete(string $table)
NB Unless you want to delete all rows from a table, set the condition first!
author | Mark Grimshaw |
---|---|
version | 1 |
string
deleteCache(string $field)
delimit(string $stmt, string $delimiter) : string
author | Mark Grimshaw |
---|---|
version | 1 |
string
string
'backtick', 'singleQuote', 'doubleQuote' etc. based on MySQL
string
delimited statementdropTable(string $table)
string
existsClause(string $stmt, boolean $not) : string
string
IN ($stmt)
boolean
Default is FALSE
string
fetchOne(object $recordset) : string
fetchRow(object $recordset) : array
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.
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.
mixed
Array of field => condition conditions or formatted condition string
boolean
Default is FALSE
boolean
Default is FALSE. If TRUE, don't set the condition but return a formatted condition string instead
string
Optional returnformatConditionsOneField(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.
mixed
Array of field => condition conditions or formatted condition string
string
boolean
Default is FALSE
boolean
Format the field for the database type. Default is TRUE.
formatFields(array $fields, boolean $withExceptions) : string
author | Mark Grimshaw |
---|---|
version | 1 |
array
boolean
Default is FALSE
string
formatTables(mixed $tables, boolean $brackets) : string
mixed
Array of tables or single table
boolean
Default is FALSE
string
formatTimestamp(string $time) : string
author | Mark Grimshaw |
---|---|
version | 1 |
string
UNIX epoch time. Default is FALSE (in which case time() is used)
string
freeSet(object $resultset)
object
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.
string
boolean
Default is TRUE. If TRUE, format fields for database type
string
Default is FALSE. If TRUE, group by clause has ' HAVING $having' appended to it.
ifClause(string $field, string $test, string $result, string $default) : string
string
string
string
string
string
inClause(string $stmt, boolean $not) : string
string
IN ($stmt)
boolean
Default is FALSE
string
indexHint(string $field, string $type)
string
string
Default is 'FORCE'
insert(string $table, array $fields, array $values)
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.
string
string
string
Default is FALSE
boolean
Default is TRUE. If TRUE, format fields for database type
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.
string
string
string
Default is FALSE
boolean
Default is TRUE. If TRUE, format fields for database type
like(string $first, string $test, string $last, boolean $not) : string
string
string
string
boolean
Default is FALSE
string
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.
int
int
listFields(string $table) : array
monthDiff(string $date1, string $date2) : int
string
Timestamp value from database
string
Default is FALSE. If FALSE, NOW() is assumed
int
numRows(object $recordset) : int
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.
string
boolean
Default is TRUE. If TRUE, format fields for database type
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.
string
boolean
Default is TRUE. If TRUE, format fields for database type
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.
prependTableToField(string $table, mixed $fields) : mixed
string
mixed
Array of field names or single field name
mixed
query(string $querystring, boolean $saveSession) : object
author | Mark Grimshaw |
---|---|
version | 1 |
string
boolean
Default is FALSE
object
queryNoError(string $querystring) : object
Ignore error warnings (used with SYSTEMCHECK.php)
author | Mark Grimshaw |
---|---|
version | 1 |
string
query
object
queryNoExecute(string $querystring, boolean $saveSession) : string
author | Mark Grimshaw |
---|---|
version | 1 |
string
boolean
Default is FALSE
string
readCache(string $field) : array
regexp(string $first, string $test, string $last, boolean $not) : string
string
string
string
boolean
Default is FALSE
string
renameTable(string $oldTable, string $newTable)
string
string
replace(string $field, string $find, string $replace, boolean $tidy) : string
$field . ", '$find', '$replace')"
string
string
string
boolean
Default is TRUE. If TRUE, format fields for database type
string
resetSubs()
restore(boolean $restore)
boolean
Default is FALSE
round(string $clause, boolean $alias, int $round) : string
string
boolean
Default is FALSE
int
Default is 3
string
select(array $tables, mixed $fields, boolean $distinct, boolean $tidyFields, boolean $alias) : object
author | Mark Grimshaw |
---|---|
version | 1 |
array
mixed
Array of fields or can be '*'
boolean
Default is FALSE
boolean
Format fields for SQL. Default is TRUE
boolean
Default is FALSE
object
recordsetselectAverageDate(string $table, string $field) : object
selectCount(string $table, string $field) : object
NB 'count' field in recordset
author | Mark Grimshaw |
---|---|
version | 1 |
string
string
object
recordsetselectCountDistinctField(string $table, string $field, string $subQuery) : object
NB 'count' field in the recordset
author | Mark Grimshaw |
---|---|
version | 1 |
string
string
string
Default is FALSE
object
recordsetselectCountDistinctFieldNoExecute(string $table, string $field, string $subQuery, string $clause) : string
NB 'count' field in the recordset
author | Mark Grimshaw |
---|---|
version | 1 |
string
string
string
Default is FALSE
string
Default is FALSE
string
selectCountFromSubquery(string $field, string $subQuery) : object
selectCountFromSubqueryNoExecute(string $field, string $subQuery, string $clause) : string
author | Mark Grimshaw |
---|
string
string
string
Default is FALSE
string
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 |
string
string
object
recordsetselectCounts(string $table, string $field, array $otherFields, string $subQuery, boolean $group) : object
author | Mark Grimshaw |
---|---|
version | 1 |
string
string
array
Other fields to add to the query. Default is FALSE
string
Default is FALSE
boolean
Default is TRUE
object
recordsetselectCountsNoExecute(string $table, string $field, array $otherFields, string $subQuery, boolean $group, string $clause) : string
author | Mark Grimshaw |
---|---|
version | 1 |
string
string
array
Other fields to add to the query. Default is FALSE
string
Default is FALSE
boolean
Default is TRUE
string
Default is FALSE
string
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 |
array
mixed
Array of fields or '*'
string
boolean
Default is FALSE
boolean
Format fields. Default is TRUE
object
recordsetselectMax(string $table, string $maxField, boolean $alias, array $otherFields, string $subQuery) : object
author | Mark Grimshaw |
---|---|
version | 1 |
string
string
boolean
Default is FALSE
array
Other fields to add to the query. Default is FALSE
string
Default is FALSE
object
recordsetselectMin(string $table, string $minField) : object
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 |
array
mixed
Array of fields or '*'
boolean
Default is FALSE
boolean
Format fields. Default is TRUE
string
Default is FALSE
boolean
Default is FALSE
string
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 |
array
mixed
Array of fields or '*'
string
boolean
Default is FALSE
boolean
Format fields. Default is TRUE
boolean
Default is FALSE
object
recordsetselectNoExecuteWithExceptions(array $tables, mixed $fields, boolean $distinct) : string
author | Mark Grimshaw |
---|---|
version | 1 |
see | \SQL::select() |
array
mixed
Array of fields or '*'
boolean
Default is FALSE
string
selectWithExceptions(array $tables, mixed $fields, boolean $distinct) : object
author | Mark Grimshaw |
---|---|
version | 1 |
see | \SQL::select() |
array
mixed
Array of fields or '*'
boolean
Default is FALSE
object
recordsetsubQuery(string $stmt, boolean $alias, boolean $from, boolean $clause) : string
author | Mark Grimshaw |
---|---|
version | 1 |
string
Pre-defined SQL stmt (which may be a subquery itself)
boolean
Boolean table alias sometimes required for subquery SELECT statements. Default is FALSE
boolean
TRUE If FALSE, don't add the initial 'FROM'
boolean
Default is FALSE. If TRUE, add all conditions, joins, groupBy, orderBy etc. clauses
string
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 |
array
string
Formatted subquery string
boolean
Boolean table alias sometimes required for subquery SELECT statements. Default is FALSE
boolean
Default is FALSE. If TRUE, add all conditions, joins, groupBy, orderBy etc. clauses
boolean
Default is FALSE
boolean
Format fields for SQL queries. Default is TRUE
string
sum(string $field, string $alias) : string
string
string
Default is FALSE
string
tidyInput(string $string) : string
tidyInputClause(string $string) : string
For use with ORDER or GROUP by clauses
author | Mark Grimshaw |
---|---|
version | 1 |
string
string
tidyInputNoTrim(string $string) : string
union(mixed $stmt, boolean $all) : string
mixed
string or array select statement(s) to be unionized
boolean
Default FALSE. Set to TRUE to have 'UNION ALL'
string
update(string $table, array $updateArray)
updateNull(string $table, array $nulls)
author | Mark Grimshaw |
---|---|
version | 1 |
see | \SQL::updateNull() |
string
array
Array of fields to set to NULL
updateSingle(string $table, string $set)
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 |
string
array
upper(string $field, boolean $tidy) : string
string
boolean
Default is TRUE. If TRUE, format fields for database type
string
whereStmt(array $conditions, string $join) : string
array
string
Default is ''
string
writeCache(string $field, array $array)
formatAlias(array $array, string $table) : string
author | Mark Grimshaw |
---|---|
version | 1 |
array
Field => Alias
string
Default is FALSE. If TRUE, prepend table name to alias
string
formatAliasWithExceptions(array $array) : string
Array keys are checked for UNIX_TIMESTAMP or DATE_FORMAT
author | Mark Grimshaw |
---|---|
version | 1 |
array
Field => Alias
string
formatUpdate(array $array) : string
formatValues(mixed $values) : string
mixed
Array of values or single value
string
sqlAddSlashes(string $element) : string
string
string
subClause() : string
string
$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