WIKINDX API trunk

SQL
in package

SQL

SQL abstraction layer for using MySQL.

Table of Contents

Properties

$and  : string
string
$asc  : string
string
$ascDesc  : string
string
$condition  : array<string|int, mixed>
array
$conditionSeparator  : string
string
$desc  : string
string
$errno  : int
$error  : string
$join  : array<string|int, mixed>
array
$or  : string
string
$order  : array<string|int, mixed>
array

Methods

__construct()  : mixed
SQL
avg()  : string
Return a AVG() clause
beautifySQL()  : string
Beautify very briefly a SQL statement to facilitate debugging.
caseWhen()  : string
Create a CASE WHEN() THEN clause
cast()  : string
Cast a column/value to a specific data type
close()  : bool
Close SQL database
coalesce()  : string
Create a COALESCE clause: "COALESCE($fields) $alias"
concat()  : string
Create a CONCAT clause
count()  : string
Create a COUNT() clause
countAlpha()  : string
Create the SQL SELECT statement for counting resources/initial character of creator or title when using alphabetic paging.
createRepairKitDbSchema()  : array<string|int, mixed>
Create a db schema in an array for the Repair Kit plugin from the current database
cteSearchIds()  : string
create a CTE query where the expected return from executing the query is a list of resource IDs given as 'rId1' by default. This can be overriden by specifying $label
dateDiffRatio()  : string
Return a ratio alias of $field / number days since e.g. resource added.
dateIntervalCondition()  : string
Create a condition clause for a time interval: "DATE_SUB($fromTime, INTERVAL $limit $timescale)"
delete()  : void
Execute a DELETE statement
deleteCache()  : void
Delete a WIKINDX database cache
delimit()  : string
Add delimiters to statements
escapeLikeString()  : string
Escape a string for the LIKE statement according to db type
escapeString()  : string
Escape a string according to db type
except()  : string
Create an EXCEPT sub query (in MySQL from v8 up)
existsClause()  : string
Create an EXISTS() clause
fetchOne()  : mixed
Fetch one field value from the database
fetchRow()  : bool|array<string|int, mixed>|null
Fetch one row from a recordset
firstDayOfCurrentMonth()  : string
Return SQL code to retrieve the first day of the current mounth
firstDayOfCurrentYear()  : string
Return SQL code to retrieve the first day of the current year
formatConditions()  : string
Set up the SQL conditions for the next query.
formatConditionsOneField()  : mixed
Format multiple conditions for one field using ' OR '
formatFields()  : string
Format fields for database type
formatTables()  : string|false
Format tables for database type
formatTimestamp()  : string
Format a timestamp value as "Y-m-d H:i:s"
formatValues()  : string
Format field values for database type
fulltextSearch()  : string
Create a FULLTEXT search clause: MATCH($field) AGAINST('$searchTerm' $type)
getAutoincrementValue()  : array<string|int, mixed>
Gte the auto increment value of a table (next increment available)
getFieldsProperties()  : mixed
Fetch fields properties of a recordset
getRepairKitDbSchema()  : array<string|int, mixed>|false
Read a db schema formated for the Repair Kit plugin in an array
goToRow()  : void
Adjusts the result pointer to an arbitrary row in the resultset
groupBy()  : void
Create a GROUP BY clause
groupConcat()  : string
Create a GROUP_CONCAT clause
ifClause()  : string
Create an IF clause: "IF($field $test, $result, $default)"
inClause()  : string
Create an IN() clause
innerJoin()  : mixed
Create a INNER JOIN clause on a table
innerJoinGeneric()  : void
Create a INNER JOIN clause (generic)
innerJoinSubQuery()  : mixed
Create a INNER JOIN clause on a subquery
insert()  : void
Execute an INSERT statement
intersect()  : string
Create an INTERSECT sub query (in MySQL from v8 up)
lastAutoID()  : int
Return last auto_increment ID
leftJoin()  : void
Create a LEFT JOIN clause on a table
leftJoinCondition()  : void
Create a LEFT JOIN clause with additional condition string
leftJoinGeneric()  : void
Create a LEFT JOIN clause (generic)
leftJoinSubQuery()  : void
Create a LEFT JOIN clause on a subquery
like()  : string
Create a LIKE clause
limit()  : mixed
Create a LIMIT clause
listFields()  : array<string|int, mixed>
List fields in a database table
listTables()  : array<string|int, mixed>
show all tables in db
lower()  : string
Create an LOWER clause
minStmt()  : string
Create a MIN() statement
monthDiff()  : int|null
Return number months difference between two database timestamps
multiInsert()  : void
Execute a multiple INSERT statement
multiUpdate()  : void
Create and execute a multiple update on one table
multiUpdateNull()  : void
Create and execute a multiple update on one table, setting column values to NULL
numRows()  : int
return numRows from recordset
open()  : bool
Open SQL database
orderBy()  : mixed
Create an ORDER BY clause
orderByRandom()  : void
Create an ORDER BY RAND() clause
prependTableToField()  : array<string|int, mixed>|string
prepend the configured table name to the field names
query()  : bool|array<string|int, mixed>|null
execute queries and return recordset
queryFetchFirstField()  : mixed
Execute queries, fetch only the first field of the first row of the result and return it
queryFetchFirstRow()  : mixed
Execute queries, fetch only the first row of the result and return it
queryNoExecute()  : string
Create the entire querystring but do not execute
queryNoResult()  : bool
Execute queries and return TRUE for success, FALSE if the query failed
readCache()  : array<string|int, mixed>|object|bool
Read a WIKINDX database cache
regexp()  : string
Create a REGEXP clause
replace()  : string
Create a REPLACE clause: "REPLACE(' . $field . ", '$find', '$replace')"
resetSubs()  : void
reset various strings and arrays used in subclauses
round()  : string
Create a ROUND() clause
select()  : bool|array<string|int, mixed>|null
Execute SELECT statement
selectAverageDate()  : string
Execute a "SELECT FROM_UNIXTIME(AVG(UNIX_TIMESTAMP($field))) AS $field FROM $table" statement
selectCount()  : bool|array<string|int, mixed>|null
Execute a "SELECT COUNT(*) AS count, $field FROM $table" statement
selectCountDistinctField()  : bool|array<string|int, mixed>|null
Execute a "SELECT COUNT(DISTINCT $field) AS count $table $subQuery" statement
selectCountDistinctFieldNoExecute()  : string
Create a "SELECT COUNT(DISTINCT $field) AS count $table $subQuery $clause" statement without executing
selectCountFromSubquery()  : bool|array<string|int, mixed>|null
Execute a "SELECT COUNT(DISTINCT $field) AS count $subQuery" statement
selectCountFromSubqueryNoExecute()  : string
Create a "SELECT COUNT(DISTINCT $field) AS count $subQuery $clause" statment without executing
selectCountMax()  : bool|array<string|int, mixed>|null
Execute a "SELECT COUNT(*) AS count, $field FROM $table" statement
selectCountOnly()  : int
Execute a "SELECT COUNT($field) AS count FROM $table" statement
selectCounts()  : bool|array<string|int, mixed>|null
Execute a "SELECT $field, COUNT($field) AS count $otherFields $table $subQuery" statement
selectCountsNoExecute()  : string
Create a "SELECT $field, COUNT($field) AS count $otherFields $table $subQuery $clause" statement without executing
selectFirstField()  : mixed
Execute SELECT statement and return the first field of the first row
selectFirstRow()  : bool|array<string|int, mixed>|null
Execute SELECT statement and return the first row
selectFromSubQuery()  : bool|array<string|int, mixed>|null
Execute a SELECT statement with a subquery
selectMax()  : mixed
Execute SELECT MAX() statement
selectMin()  : mixed
Execute a SELECT MIN() statement
selectNoExecute()  : string
Create a SELECT statement without executing
selectNoExecuteFromSubQuery()  : string
Create a SELECT statement with a subquery without executing
selectNoExecuteWithExceptions()  : string
Create a SELECT statement without executing
selectWithExceptions()  : bool|array<string|int, mixed>|null
Execute a SELECT statement
subQuery()  : string
Create a subquery from a SQL statement
subQueryFields()  : string
Create a subquery from SQL fields
sum()  : string
Create a SUM() clause
tableExists()  : bool
Check if a table exists in the current database
tableIsEmpty()  : bool
Is a table Empty?
tidyInput()  : string
Format field values for database type
union()  : string
Create a UNION sub query
update()  : void
Execute an UPDATE statement for an array of fields
updateNull()  : void
Execute an UPDATE statement setting the fields to NULL
updateSingle()  : void
Execute an UPDATE statement for a single field
updateTimestamp()  : void
Execute an UPDATE statement for an array of fields, setting the timestamp of a field
upper()  : string
Create an UPPER clause
whereStmt()  : string
Create a WHERE() statement
writeCache()  : void
Write a WIKINDX database cache
writeRepairKitDbSchema()  : bool
Write an array of db schema formated for the Repair Kit plugin to a file

Properties

$and

string

public string $and = ' AND '

$asc

string

public string $asc = ' ASC'

$ascDesc

string

public string $ascDesc

$condition

array

public array<string|int, mixed> $condition = []

$conditionSeparator

string

public string $conditionSeparator

$desc

string

public string $desc = ' DESC'

$errno

public int $errno = 0

Error code returned by db drivers

$error

public string $error = ""

Error message returned by db drivers or WIKINDX

$join

array

public array<string|int, mixed> $join = []

$or

string

public string $or = ' OR '

$order

array

public array<string|int, mixed> $order = []

Methods

__construct()

SQL

public __construct([bool $autoConnect = true ]) : mixed
Parameters
$autoConnect : bool = true

Allow or prevent this class to open a connection during instantiation (TRUE by default)

avg()

Return a AVG() clause

public avg(string $clause) : string
Parameters
$clause : string
Return values
string

beautifySQL()

Beautify very briefly a SQL statement to facilitate debugging.

public beautifySQL([string $sqlStatement = "" ][, string $executionType = "" ]) : string

Return Sql instruction packaged in a nice HTML

Parameters
$sqlStatement : string = ""

Default is ""

$executionType : string = ""

Default is ""

Return values
string

caseWhen()

Create a CASE WHEN() THEN clause

public caseWhen(array<string|int, mixed>|string|null $subject, string $test, string $result[, bool|string $default = false ][, bool $tidy = true ][, bool|string $alias = false ]) : string

$subject can be an array. This allows multiple WHEN $subject:key THEN $subject:value to be part of the CASE statement. If $subject is an array, $test and $result are ignored ($tidy is still tested regarding formatting or not of $default) and the keys and values of $subject should already be formatted and tidied for SQL: e.g. subject['subject = test' => result];

Parameters
$subject : array<string|int, mixed>|string|null

(string or array)

$test : string
$result : string
$default : bool|string = false

Default is FALSE

$tidy : bool = true

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

$alias : bool|string = false

Default is FALSE

Return values
string

close()

Close SQL database

public close() : bool
Return values
bool

coalesce()

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

public coalesce(array<string|int, mixed>|string $fields[, string|false $alias = false ]) : string
Parameters
$fields : array<string|int, mixed>|string

Array of field names or single field name

$alias : string|false = false
Return values
string

concat()

Create a CONCAT clause

public concat(array<string|int, mixed> $array[, bool|string $separator = false ]) : string
Parameters
$array : array<string|int, mixed>
$separator : bool|string = false

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

Return values
string

count()

Create a COUNT() clause

public count(string $field[, bool|string $operator = false ][, bool|string $comparison = false ][, bool $distinct = false ][, bool|string $alias = false ]) : string
Parameters
$field : string

Database field to count

$operator : bool|string = false

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

$comparison : bool|string = false

Comparison following $operator. Default is FALSE

$distinct : bool = false

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

$alias : bool|string = false

COUNT(field) AS $alias. Default is FALSE

Return values
string

countAlpha()

Create the SQL SELECT statement for counting resources/initial character of creator or title when using alphabetic paging.

public countAlpha(string $order[, string|false $subQuery = false ][, array<string|int, mixed> $conditions = [] ][, array<string|int, mixed> $joins = [] ][, array<string|int, mixed> $conditionsOneField = [] ][, string $table = 'resource' ][, string $tableJoin = 'resourceId' ]) : string

A-Z for Latin characters, '??' for all other characters and '#' for NULL resourcecreatorCreatorSurname fields

Parameters
$order : string

('creator' or 'title')

$subQuery : string|false = false

Optional subquery to be added to this statement

$conditions : array<string|int, mixed> = []

Array of conditions to SQL

$joins : array<string|int, mixed> = []

Array of table joins to SQL (array(table => array(rightField, leftField))

$conditionsOneField : array<string|int, mixed> = []

Array of conditions to SQL (formatConditionsOneField)

$table : string = 'resource'

default is 'resource'

$tableJoin : string = 'resourceId'

default is 'resourceId'

Tags
todo

Use code to create the statements rather than strings

Return values
string

createRepairKitDbSchema()

Create a db schema in an array for the Repair Kit plugin from the current database

public createRepairKitDbSchema() : array<string|int, mixed>
Return values
array<string|int, mixed> —

Schema formated in an array

cteSearchIds()

create a CTE query where the expected return from executing the query is a list of resource IDs given as 'rId1' by default. This can be overriden by specifying $label

public cteSearchIds(array<string|int, mixed> $cte[, string $label = 'rId1' ]) : string

$cte is a multidimensional array that might be: ['UNION'] => [selectString1, selectString2 . . .], ['INTERSECT'] => [selectString1, selectString2 . . .], ['EXCEPT'] => [selectString1, selectString2 . . .]

In each selectString, the selected field must be given as '!WIKINDXIDWIKINDX!'.

The return string is a compound CTE statement unless $cte has a single element, itself with a single element. In this case, a simple SELECT statement is returned.

It is the programmer's responsibility to ensure no compound CTE statement comprises solely EXCEPT statements as this would lead to an illegal SQL result. In this case, the programmer should convert such statements to INTERSECT and negate the condition before passing the $cte array here..

The code here will place any EXCEPT statements towards the end to avoid illegal SQL constructs such as (EXCEPT SELECT cte1 . . . INTERSECT SELECT cte3 . . .). In this case, the example would become (INTERSECT SELECT cte1 . . . EXCEPT SELECT cte3)

Parameters
$cte : array<string|int, mixed>
$label : string = 'rId1'

= 'rid1'

Return values
string

dateDiffRatio()

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

public dateDiffRatio(string $field, string $denominator[, bool|string $alias = false ][, string $aggregateFunction = '' ][, int $round = 3 ][, string|false $otherFields = false ][, bool $group = false ]) : string
Parameters
$field : string

(e.g. 'statisticsresourceviewsCount', or 'statisticsattachmentdownloadsCount')

$denominator : string

(e.g. 'resourcetimestampTimestampAdd')

$alias : bool|string = false

Default is FALSE

$aggregateFunction : string = ''

Default is ''. If <> '', insert an Aggregate Function of the same name of multiple $fields

$round : int = 3

Default is 3

$otherFields : string|false = false

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

$group : bool = false

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

Return values
string

dateIntervalCondition()

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

public dateIntervalCondition(string|int $limit[, string $timescale = 'DAY' ][, string $fromTime = 'CURRENT_DATE' ]) : string
Parameters
$limit : string|int
$timescale : string = 'DAY'

Default is 'DAY'

$fromTime : string = 'CURRENT_DATE'

Default is 'CURRENT_DATE'

Tags
see
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-add
https://dev.mysql.com/doc/refman/8.0/en/expressions.html#temporal-intervals
Return values
string

delete()

Execute a DELETE statement

public delete(string $table) : void

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

Parameters
$table : string

deleteCache()

Delete a WIKINDX database cache

public deleteCache(string $field) : void
Parameters
$field : string

delimit()

Add delimiters to statements

public delimit(string $stmt, string $delimiter) : string
Parameters
$stmt : string
$delimiter : string

'backtick', 'singleQuote', 'doubleQuote', 'parentheses' (based on MySQL)

Return values
string —

delimited statement

except()

Create an EXCEPT sub query (in MySQL from v8 up)

public except(array<string|int, mixed>|string $stmt) : string
Parameters
$stmt : array<string|int, mixed>|string

string or array select statement(s) to be unionized

Return values
string

existsClause()

Create an EXISTS() clause

public existsClause(string $stmt[, bool $not = false ]) : string
Parameters
$stmt : string

EXISTS ($stmt)

$not : bool = false

Default is FALSE

Return values
string

fetchOne()

Fetch one field value from the database

public fetchOne(bool|array<string|int, mixed>|null $recordset) : mixed
Parameters
$recordset : bool|array<string|int, mixed>|null

fetchRow()

Fetch one row from a recordset

public fetchRow(bool|array<string|int, mixed>|null &$recordset) : bool|array<string|int, mixed>|null
Parameters
$recordset : bool|array<string|int, mixed>|null
Return values
bool|array<string|int, mixed>|null

firstDayOfCurrentMonth()

Return SQL code to retrieve the first day of the current mounth

public firstDayOfCurrentMonth() : string
Return values
string

firstDayOfCurrentYear()

Return SQL code to retrieve the first day of the current year

public firstDayOfCurrentYear() : string
Return values
string

formatConditions()

Set up the SQL conditions for the next query.

public formatConditions(array<string|int, mixed>|string $condition[, string $notEqual = "=" ][, bool $returnString = false ][, bool $doubleParentheses = false ]) : string

Conditions should be set before almost every SQL query. After the query is executed, the conditions are deleted 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 back to $this->and.

Parameters
$condition : array<string|int, mixed>|string

Array of field => condition conditions or formatted condition string

$notEqual : string = "="

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

$returnString : bool = false

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

$doubleParentheses : bool = false

Default is FALSE

Return values
string —

Optional return

formatConditionsOneField()

Format multiple conditions for one field using ' OR '

public formatConditionsOneField(array<string|int, mixed>|string $condition, string $field[, string $notEqual = "=" ][, bool $tidy = true ][, bool $doubleParentheses = false ][, string|false $alias = false ][, bool $returnString = false ]) : mixed

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->or ($this->conditionSeparator is ignored).

Parameters
$condition : array<string|int, mixed>|string

Array of conditions or formatted condition string

$field : string
$notEqual : string = "="

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

$tidy : bool = true

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

$doubleParentheses : bool = false

Place double, rather than single, parentheses around the condition. Default is FALSE.

$alias : string|false = false

$field is an alias in SQL so should have quotes instead of backticks. Default is FALSE.

$returnString : bool = false

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

formatFields()

Format fields for database type

public formatFields(array<string|int, mixed>|string $fields[, bool $withExceptions = false ][, bool $tidyLeft = true ]) : string
Parameters
$fields : array<string|int, mixed>|string

array or string

$withExceptions : bool = false

Default is FALSE

$tidyLeft : bool = true

Default is TRUE

Return values
string

formatTables()

Format tables for database type

public formatTables(array<string|int, mixed>|string $tables[, bool $brackets = false ]) : string|false
Parameters
$tables : array<string|int, mixed>|string

Array of tables or single table

$brackets : bool = false

Default is FALSE

Return values
string|false

formatTimestamp()

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

public formatTimestamp([int|null $time = null ]) : string
Parameters
$time : int|null = null

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

Return values
string

formatValues()

Format field values for database type

public formatValues(mixed $values) : string
Parameters
$values : mixed

Array of values or single value

Return values
string

fulltextSearch()

Create a FULLTEXT search clause: MATCH($field) AGAINST('$searchTerm' $type)

public fulltextSearch(array<string|int, mixed>|string $field, string $searchTerm[, bool $EnableBooleanSearch = true ][, bool $not = false ]) : string
Parameters
$field : array<string|int, mixed>|string

Field or array of fields to search on

$searchTerm : string

String formatted for boolean or natural language search

$EnableBooleanSearch : bool = true

Default is TRUE and gives a boolean search, FALSE is natural language search

$not : bool = false

= FALSE

Return values
string

getAutoincrementValue()

Gte the auto increment value of a table (next increment available)

public getAutoincrementValue(string $table) : array<string|int, mixed>
Parameters
$table : string

Name of a table

Return values
array<string|int, mixed>

getFieldsProperties()

Fetch fields properties of a recordset

public getFieldsProperties(string $table) : mixed
Parameters
$table : string

Name of a table

getRepairKitDbSchema()

Read a db schema formated for the Repair Kit plugin in an array

public getRepairKitDbSchema(string $filename) : array<string|int, mixed>|false
Parameters
$filename : string
Return values
array<string|int, mixed>|false —

Schema formated in an array, or FALSE on error

goToRow()

Adjusts the result pointer to an arbitrary row in the resultset

public goToRow(bool|array<string|int, mixed>|null &$recordset, int $rowNumber) : void
Parameters
$recordset : bool|array<string|int, mixed>|null
$rowNumber : int

groupBy()

Create a GROUP BY clause

public groupBy(array<string|int, mixed>|string $field[, bool $tidy = true ][, string|false $having = false ]) : void

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 : array<string|int, mixed>|string
$tidy : bool = true

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

$having : string|false = false

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

groupConcat()

Create a GROUP_CONCAT clause

public groupConcat(string $field[, bool $distinct = true ][, bool $orderClause = true ][, bool|string $separator = false ][, string $alias = false ][, bool $tidy = true ]) : string
Parameters
$field : string
$distinct : bool = true

Default is TRUE.

$orderClause : bool = true

Default is FALSE.

$separator : bool|string = false

Default is FALSE. If FALSE, the default concatenator of ',' is used.

$alias : string = false
$tidy : bool = true

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

Return values
string

ifClause()

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

public ifClause(string $field, string $test, string $result, string $default[, string|false $alias = false ]) : string

If nesting a condition in $test, then set $default to FALSE: "IF($field, $test, $result)"

Parameters
$field : string
$test : string
$result : string
$default : string
$alias : string|false = false

– default is FALSE

Return values
string

inClause()

Create an IN() clause

public inClause(string $stmt[, bool $not = false ]) : string
Parameters
$stmt : string

IN ($stmt)

$not : bool = false

Default is FALSE

Return values
string

innerJoin()

Create a INNER JOIN clause on a table

public innerJoin(array<string|int, mixed>|string $table, string $left[, string|false $right = false ][, bool $tidy = true ]) : mixed

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 : array<string|int, mixed>|string
$left : string
$right : string|false = false

Default is FALSE

$tidy : bool = true

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

innerJoinGeneric()

Create a INNER JOIN clause (generic)

public innerJoinGeneric(string $joinedMember, string $left[, string|false $right = false ][, bool $tidy = true ]) : void

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
$joinedMember : string

(Name of a table, Name of a view, subquery...)

$left : string
$right : string|false = false

Default is FALSE

$tidy : bool = true

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

innerJoinSubQuery()

Create a INNER JOIN clause on a subquery

public innerJoinSubQuery(string $subQuery, string $left[, string|false $right = false ][, bool $tidy = true ]) : mixed

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|false = false

Default is FALSE

$tidy : bool = true

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

insert()

Execute an INSERT statement

public insert(string $table, array<string|int, mixed>|string $fields, array<string|int, mixed>|string $values) : void
Parameters
$table : string
$fields : array<string|int, mixed>|string
$values : array<string|int, mixed>|string

– can be multi-dimensional array

intersect()

Create an INTERSECT sub query (in MySQL from v8 up)

public intersect(array<string|int, mixed>|string $stmt) : string
Parameters
$stmt : array<string|int, mixed>|string

string or array select statement(s) to be unionized

Return values
string

lastAutoID()

Return last auto_increment ID

public lastAutoID() : int
Return values
int

leftJoin()

Create a LEFT JOIN clause on a table

public leftJoin(array<string|int, mixed>|string $table, string $left[, string|false $right = false ][, bool $tidy = true ]) : void

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 : array<string|int, mixed>|string
$left : string
$right : string|false = false

Default is FALSE

$tidy : bool = true

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

leftJoinCondition()

Create a LEFT JOIN clause with additional condition string

public leftJoinCondition(array<string|int, mixed>|string $table, string $left[, string|false $right = false ][, string|false $condition = false ][, bool $and = true ][, bool $tidy = true ]) : void

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 : array<string|int, mixed>|string
$left : string
$right : string|false = false

Default is FALSE

$condition : string|false = false

Default is FALSE

$and : bool = true

Default is TRUE. If TRUE, prefix SQL 'AND' to $condition

$tidy : bool = true

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

leftJoinGeneric()

Create a LEFT JOIN clause (generic)

public leftJoinGeneric(string $joinedMember, string $left[, string|false $right = false ][, bool $tidy = true ]) : void

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
$joinedMember : string

(Name of a table, Name of a view, subquery...)

$left : string
$right : string|false = false

Default is FALSE

$tidy : bool = true

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

leftJoinSubQuery()

Create a LEFT JOIN clause on a subquery

public leftJoinSubQuery(string $subQuery, string $left[, string|false $right = false ][, bool $tidy = true ]) : void

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|false = false

Default is FALSE

$tidy : bool = true

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

like()

Create a LIKE clause

public like(string $first, string $test, string $last[, bool $not = false ]) : string
Parameters
$first : string
$test : string
$last : string
$not : bool = false

Default is FALSE

Return values
string

limit()

Create a LIMIT clause

public limit(int $row_count[, int $offset = 0 ][, bool $return = false ]) : mixed

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
$row_count : int
$offset : int = 0

Default is 0

$return : bool = false

If TRUE, return the limit statement rather than setting it. Default is FALSE

listFields()

List fields in a database table

public listFields(string $table) : array<string|int, mixed>
Parameters
$table : string

Name of a table

Tags
see
SQL::listFields()
Return values
array<string|int, mixed>

listTables()

show all tables in db

public listTables() : array<string|int, mixed>
Return values
array<string|int, mixed>

lower()

Create an LOWER clause

public lower(string $field[, bool $tidy = true ]) : string
Parameters
$field : string
$tidy : bool = true

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

Return values
string

minStmt()

Create a MIN() statement

public minStmt(string $field, string $alias) : string
Parameters
$field : string

formatted field name

$alias : string

formatted alias

Return values
string

monthDiff()

Return number months difference between two database timestamps

public monthDiff(string $date1[, bool|string $date2 = false ]) : int|null
Parameters
$date1 : string

Timestamp value from database

$date2 : bool|string = false

Default is FALSE. If FALSE, CURRENT_TIMESTAMP is assumed

Return values
int|null

multiInsert()

Execute a multiple INSERT statement

public multiInsert(string $table, array<string|int, mixed>|string $fields, string $values) : void
Parameters
$table : string
$fields : array<string|int, mixed>|string
$values : string

Must be formatted as "('1', '2', '3' ...), ('4', '5', '6' ...)"

multiUpdate()

Create and execute a multiple update on one table

public multiUpdate(string $table, string $setField, string $conditionField, array<string|int, mixed> $updateArray[, array<string|int, mixed>|false $extraConditions = false ]) : void

The maximum number of SET statements (e.g., the size in bytes of $updateArray) can be set with WIKINDX_MAX_MULTIUPDATES in the calling function. Something like: if(mb_strlen(implode(' ', $updateArray) > WIKINDX_MAX_MULTIUPDATES) . . .

This executes something like: UPDATE $table SET $setField = CASE $conditionField WHEN 1 THEN a WHEN 2 THEN b WHEN 3 THEN c END WHERE $conditionField IN (1,2,3)

where $updateArray is (1 => a, 2 => b, 3 => c)

Parameters
$table : string
$setField : string
$conditionField : string
$updateArray : array<string|int, mixed>
$extraConditions : array<string|int, mixed>|false = false

Optional array of formatted conditions joined with an AND to the IN clause

multiUpdateNull()

Create and execute a multiple update on one table, setting column values to NULL

public multiUpdateNull(string $table, string $setField, string $conditionField, array<string|int, mixed> $updateArray[, array<string|int, mixed>|false $extraConditions = false ]) : void

The maximum number of SET statements (e.g., the size in bytes of $updateArray) can be set with WIKINDX_MAX_MULTIUPDATES in the calling function. Something like: if(mb_strlen(implode(' ', $updateArray) > WIKINDX_MAX_MULTIUPDATES) . . .

This executes something like: UPDATE $table SET $setField = CASE $conditionField WHEN 1 THEN NULL WHEN 2 THEN NULL WHEN 3 THEN NULL END WHERE $conditionField IN (1,2,3)

where $updateArray is (1, 2, 3)

Parameters
$table : string
$setField : string
$conditionField : string
$updateArray : array<string|int, mixed>
$extraConditions : array<string|int, mixed>|false = false

Optional array of formatted conditions joined with an AND to the IN clause

numRows()

return numRows from recordset

public numRows(bool|array<string|int, mixed>|null $recordset) : int
Parameters
$recordset : bool|array<string|int, mixed>|null
Return values
int

open()

Open SQL database

public open([string $dbhost = WIKINDX_DB_HOST ][, string $dbname = WIKINDX_DB ][, string $dbuser = WIKINDX_DB_USER ][, string $dbpwd = WIKINDX_DB_PASSWORD ]) : bool
Parameters
$dbhost : string = WIKINDX_DB_HOST

Hostname/IP of the server and it's port (optional, eg. hostname:3306)

$dbname : string = WIKINDX_DB

Name of the database

$dbuser : string = WIKINDX_DB_USER

Login

$dbpwd : string = WIKINDX_DB_PASSWORD

Password

Return values
bool

orderBy()

Create an ORDER BY clause

public orderBy(array<string|int, mixed>|string $field[, bool $tidy = true ][, bool $removeBraces = true ][, bool $returnString = false ]) : mixed

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 : array<string|int, mixed>|string
$tidy : bool = true

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

$removeBraces : bool = true

Default is TRUE. If TRUE, remove {...} braces

$returnString : bool = false

Default is FALSE. If TRUE, return the ORDER BY clause as a string

orderByRandom()

Create an ORDER BY RAND() clause

public orderByRandom() : void

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()

prepend the configured table name to the field names

public prependTableToField(string $table, array<string|int, mixed>|string $fields) : array<string|int, mixed>|string
Parameters
$table : string

Table name used as a field prefix (underscores are removed)

$fields : array<string|int, mixed>|string

Array of field names or single field name

Return values
array<string|int, mixed>|string

query()

execute queries and return recordset

public query(string $querystring) : bool|array<string|int, mixed>|null
Parameters
$querystring : string
Return values
bool|array<string|int, mixed>|null —

An array, or a boolean if there are no data to return. Only the first result set is returned

queryFetchFirstField()

Execute queries, fetch only the first field of the first row of the result and return it

public queryFetchFirstField(string $querystring) : mixed
Parameters
$querystring : string

queryFetchFirstRow()

Execute queries, fetch only the first row of the result and return it

public queryFetchFirstRow(string $querystring) : mixed
Parameters
$querystring : string

queryNoExecute()

Create the entire querystring but do not execute

public queryNoExecute(string $querystring) : string
Parameters
$querystring : string
Return values
string

queryNoResult()

Execute queries and return TRUE for success, FALSE if the query failed

public queryNoResult(string $querystring) : bool
Parameters
$querystring : string
Return values
bool

readCache()

Read a WIKINDX database cache

public readCache(string $field) : array<string|int, mixed>|object|bool
Parameters
$field : string
Return values
array<string|int, mixed>|object|bool

regexp()

Create a REGEXP clause

public regexp(string $first, string $test, string $last[, bool $not = false ]) : string
Parameters
$first : string
$test : string
$last : string
$not : bool = false

Default is FALSE

Return values
string

replace()

Create a REPLACE clause: "REPLACE(' . $field . ", '$find', '$replace')"

public replace(string $field, string $find, string $replace[, bool $tidy = true ]) : string
Parameters
$field : string
$find : string
$replace : string
$tidy : bool = true

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

Return values
string

resetSubs()

reset various strings and arrays used in subclauses

public resetSubs() : void

round()

Create a ROUND() clause

public round(string $clause[, string|false $alias = false ][, int $round = 3 ]) : string
Parameters
$clause : string
$alias : string|false = false

Default is FALSE

$round : int = 3

Default is 3

Tags
See

https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_round

Return values
string

select()

Execute SELECT statement

public select(array<string|int, mixed>|string $tables, array<string|int, mixed>|string $fields[, bool $distinct = false ][, bool $tidyFields = true ][, string|false $alias = false ][, bool $clause = false ]) : bool|array<string|int, mixed>|null
Parameters
$tables : array<string|int, mixed>|string
$fields : array<string|int, mixed>|string

Array of fields or can be '*'

$distinct : bool = false

Default is FALSE

$tidyFields : bool = true

Format fields for SQL. Default is TRUE

$alias : string|false = false

Default is FALSE

$clause : bool = false

Default is FALSE

Return values
bool|array<string|int, mixed>|null

selectAverageDate()

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

public selectAverageDate(string $table, string $field) : string
Parameters
$table : string
$field : string
Return values
string

selectCount()

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

public selectCount(string $table, array<string|int, mixed>|string $field) : bool|array<string|int, mixed>|null

NB 'count' field in recordset

Parameters
$table : string
$field : array<string|int, mixed>|string
Return values
bool|array<string|int, mixed>|null

selectCountDistinctField()

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

public selectCountDistinctField(string $table, string $field[, string|false $subQuery = false ][, bool $clause = false ]) : bool|array<string|int, mixed>|null

NB 'count' field in the recordset

Parameters
$table : string
$field : string
$subQuery : string|false = false

Default is FALSE

$clause : bool = false

Default is FALSE

Return values
bool|array<string|int, mixed>|null

selectCountDistinctFieldNoExecute()

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

public selectCountDistinctFieldNoExecute(string $table, string $field[, string|false $subQuery = false ][, bool $clause = false ]) : string

NB 'count' field in the recordset

Parameters
$table : string
$field : string
$subQuery : string|false = false

Default is FALSE

$clause : bool = false

Default is FALSE

Return values
string

selectCountFromSubquery()

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

public selectCountFromSubquery(string $field, string $subQuery[, bool $clause = false ]) : bool|array<string|int, mixed>|null
Parameters
$field : string
$subQuery : string
$clause : bool = false

Default is FALSE

Return values
bool|array<string|int, mixed>|null

selectCountFromSubqueryNoExecute()

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

public selectCountFromSubqueryNoExecute(string $field, string $subQuery[, bool $clause = false ]) : string
Parameters
$field : string
$subQuery : string
$clause : bool = false

Default is FALSE

Return values
string

selectCountMax()

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

public selectCountMax(string $table, array<string|int, mixed>|string $field) : bool|array<string|int, mixed>|null

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

Parameters
$table : string
$field : array<string|int, mixed>|string
Return values
bool|array<string|int, mixed>|null

selectCountOnly()

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

public selectCountOnly(string $table[, string $field = "*" ]) : int

NB 'count' field in recordset

Parameters
$table : string
$field : string = "*"

(Default is "*")

Return values
int

selectCounts()

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

public selectCounts(string $table, string $field[, array<string|int, mixed>|string|false $otherFields = false ][, string|false $subQuery = false ][, bool $group = true ][, bool $clause = false ][, bool $distinct = false ]) : bool|array<string|int, mixed>|null
Parameters
$table : string
$field : string
$otherFields : array<string|int, mixed>|string|false = false

Other fields to add to the query. Default is FALSE

$subQuery : string|false = false

Default is FALSE

$group : bool = true

Default is TRUE

$clause : bool = false

Default is FALSE

$distinct : bool = false

Default is FALSE

Return values
bool|array<string|int, mixed>|null

selectCountsNoExecute()

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

public selectCountsNoExecute(string $table, string $field[, array<string|int, mixed>|string|false $otherFields = false ][, string|false $subQuery = false ][, bool $group = true ][, bool $clause = false ][, bool $distinct = false ]) : string
Parameters
$table : string
$field : string
$otherFields : array<string|int, mixed>|string|false = false

Other fields to add to the query. Default is FALSE

$subQuery : string|false = false

Default is FALSE

$group : bool = true

Default is TRUE

$clause : bool = false

Default is FALSE

$distinct : bool = false

Default is FALSE

Return values
string

selectFirstField()

Execute SELECT statement and return the first field of the first row

public selectFirstField(array<string|int, mixed>|string $tables, array<string|int, mixed>|string $fields[, bool $distinct = false ][, bool $tidyFields = true ][, string|false $alias = false ][, bool $clause = false ]) : mixed
Parameters
$tables : array<string|int, mixed>|string
$fields : array<string|int, mixed>|string

Array of fields or can be '*'

$distinct : bool = false

Default is FALSE

$tidyFields : bool = true

Format fields for SQL. Default is TRUE

$alias : string|false = false

Default is FALSE

$clause : bool = false

Default is FALSE

selectFirstRow()

Execute SELECT statement and return the first row

public selectFirstRow(array<string|int, mixed>|string $tables, array<string|int, mixed>|string $fields[, bool $distinct = false ][, bool $tidyFields = true ][, string|false $alias = false ][, bool $clause = false ]) : bool|array<string|int, mixed>|null
Parameters
$tables : array<string|int, mixed>|string
$fields : array<string|int, mixed>|string

Array of fields or can be '*'

$distinct : bool = false

Default is FALSE

$tidyFields : bool = true

Format fields for SQL. Default is TRUE

$alias : string|false = false

Default is FALSE

$clause : bool = false

Default is FALSE

Return values
bool|array<string|int, mixed>|null

selectFromSubQuery()

Execute a SELECT statement with a subquery

public selectFromSubQuery(array<string|int, mixed>|string $tables, array<string|int, mixed>|string $fields, string $subQuery[, bool $distinct = false ][, bool $tidy = true ][, bool $clause = false ]) : bool|array<string|int, mixed>|null

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

Parameters
$tables : array<string|int, mixed>|string
$fields : array<string|int, mixed>|string

Array of fields or '*'

$subQuery : string
$distinct : bool = false

Default is FALSE

$tidy : bool = true

Format fields. Default is TRUE

$clause : bool = false

Default is FALSE

Return values
bool|array<string|int, mixed>|null

selectMax()

Execute SELECT MAX() statement

public selectMax(string $table, string $maxField[, string|false $alias = false ][, array<string|int, mixed>|string|false $otherFields = false ][, bool|string $subQuery = false ]) : mixed
Parameters
$table : string
$maxField : string
$alias : string|false = false

Default is FALSE

$otherFields : array<string|int, mixed>|string|false = false

Other fields to add to the query. Default is FALSE

$subQuery : bool|string = false

Default is FALSE

selectMin()

Execute a SELECT MIN() statement

public selectMin(string $table, string $minField) : mixed
Parameters
$table : string
$minField : string

selectNoExecute()

Create a SELECT statement without executing

public selectNoExecute(array<string|int, mixed>|string $tables, array<string|int, mixed>|string $fields[, bool $distinct = false ][, bool $tidyFields = true ][, bool $clause = false ][, string|false $alias = false ]) : string

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

Parameters
$tables : array<string|int, mixed>|string
$fields : array<string|int, mixed>|string

Array of fields or '*'

$distinct : bool = false

Default is FALSE

$tidyFields : bool = true

Format fields. Default is TRUE

$clause : bool = false

Default is FALSE

$alias : string|false = false

Default is FALSE

Return values
string

selectNoExecuteFromSubQuery()

Create a SELECT statement with a subquery without executing

public selectNoExecuteFromSubQuery(array<string|int, mixed>|string $tables, array<string|int, mixed>|string $fields, string $subQuery[, bool $distinct = false ][, bool $tidy = true ][, bool $clause = false ]) : string

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

Parameters
$tables : array<string|int, mixed>|string
$fields : array<string|int, mixed>|string

Array of fields or '*'

$subQuery : string
$distinct : bool = false

Default is FALSE

$tidy : bool = true

Format fields. Default is TRUE

$clause : bool = false

Default is FALSE

Return values
string

selectNoExecuteWithExceptions()

Create a SELECT statement without executing

public selectNoExecuteWithExceptions(array<string|int, mixed>|string $tables, array<string|int, mixed>|string $fields[, bool $distinct = false ]) : string
Parameters
$tables : array<string|int, mixed>|string
$fields : array<string|int, mixed>|string

Array of fields or '*'

$distinct : bool = false

Default is FALSE

Tags
see
SQL::select()
Return values
string

selectWithExceptions()

Execute a SELECT statement

public selectWithExceptions(array<string|int, mixed>|string $tables, array<string|int, mixed>|string $fields[, bool $distinct = false ]) : bool|array<string|int, mixed>|null
Parameters
$tables : array<string|int, mixed>|string
$fields : array<string|int, mixed>|string

Array of fields or '*'

$distinct : bool = false

Default is FALSE

Tags
see
SQL::select()
Return values
bool|array<string|int, mixed>|null

subQuery()

Create a subquery from a SQL statement

public subQuery(string $stmt[, string|false $alias = false ][, bool $from = true ][, bool $clause = false ]) : string
Parameters
$stmt : string

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

$alias : string|false = false

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

$from : bool = true

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

$clause : bool = false

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

Return values
string

subQueryFields()

Create a subquery from SQL fields

public subQueryFields(array<string|int, mixed>|string $fields, string $subquery[, bool|string $alias = false ][, bool $clause = false ][, bool $distinct = false ][, bool $tidy = true ]) : string

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

Parameters
$fields : array<string|int, mixed>|string
$subquery : string

Formatted subquery string

$alias : bool|string = false

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

$clause : bool = false

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

$distinct : bool = false

Default is FALSE

$tidy : bool = true

Format fields for SQL queries. Default is TRUE

Return values
string

sum()

Create a SUM() clause

public sum(string $field[, bool|string $alias = false ]) : string
Parameters
$field : string
$alias : bool|string = false

Default is FALSE

Return values
string

tableExists()

Check if a table exists in the current database

public tableExists(string $table) : bool
Parameters
$table : string
Return values
bool

tableIsEmpty()

Is a table Empty?

public tableIsEmpty(string $table) : bool
Parameters
$table : string
Return values
bool

tidyInput()

Format field values for database type

public tidyInput(string|null $string) : string

Fields are trimmed

Parameters
$string : string|null
Return values
string

union()

Create a UNION sub query

public union(array<string|int, mixed>|string $stmt[, bool $all = false ]) : string
Parameters
$stmt : array<string|int, mixed>|string

string or array select statement(s) to be unionized

$all : bool = false

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

Return values
string

update()

Execute an UPDATE statement for an array of fields

public update(string $table, array<string|int, mixed> $updateArray) : void
Parameters
$table : string
$updateArray : array<string|int, mixed>
Tags
see
SQL::update()

updateNull()

Execute an UPDATE statement setting the fields to NULL

public updateNull(string $table, array<string|int, mixed>|string $nulls) : void
Parameters
$table : string
$nulls : array<string|int, mixed>|string

Array of fields to set to NULL

Tags
see
SQL::updateNull()

updateSingle()

Execute an UPDATE statement for a single field

public updateSingle(string $table, string $set) : void
Parameters
$table : string
$set : string

Set statement

updateTimestamp()

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

public updateTimestamp(string $table, array<string|int, mixed> $updateArray) : void

If there is no value for a $updateArray key, the timestamp is set to CURRENT_TIMESTAMP

Parameters
$table : string
$updateArray : array<string|int, mixed>
Tags
see
SQL::updateTimestamp

upper()

Create an UPPER clause

public upper(string $field[, bool $tidy = true ]) : string
Parameters
$field : string
$tidy : bool = true

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

Return values
string

whereStmt()

Create a WHERE() statement

public whereStmt(array<string|int, mixed>|string $conditions[, string $join = '' ]) : string
Parameters
$conditions : array<string|int, mixed>|string
$join : string = ''

Default is ''

Return values
string

writeCache()

Write a WIKINDX database cache

public writeCache(string $field, array<string|int, mixed> $array) : void
Parameters
$field : string
$array : array<string|int, mixed>

writeRepairKitDbSchema()

Write an array of db schema formated for the Repair Kit plugin to a file

public writeRepairKitDbSchema(array<string|int, mixed> $dbSchema, string $filename) : bool
Parameters
$dbSchema : array<string|int, mixed>

Schema formated in an array

$filename : string

Destination filename (absolute or relative)

Return values
bool —

TRUE on success, FALSE on error


        

        
    




    

    

                                

                

                                
    

        On this page

        
    


                

                            

            

    

        

            
Search results