Documentation

CMySqlComposer extends CSqlComposerBase
in package

Extends the CSqlComposerBase class to perform all specific MySQL SQL code.

It is a complete class and could be used directly but if you want to change the DBMS you will have to modify all the classes that use it instead of in one place (CSqlCompser). Use the CSqlCompser derived class instead.

Table of Contents

Constants

VALID_JOIN_TYPES  = ['JOIN', 'LEFT JOIN', 'RIGHT JOIN', 'INNER JOIN', 'LEFT OUTER JOIN', 'RIGHT OUTER JOIN', 'CROSS JOIN']
VALID_OPERATORS  = ['=', '!=', '<>', '<', '>', '<=', '>=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', 'IS', 'IS NOT']

Properties

$elements_per_page  : int
$page  : int
$params  : array<string|int, mixed>
$renamed_tables  : array<string|int, mixed>
$types  : array<string|int, mixed>
$values  : array<string|int, mixed>

Methods

__construct()  : mixed
__toString()  : string
Convert the SQL composer to a string (the SQL query)
AndWhere()  : CSqlComposerBase
Add "AND" WHERE condition
As()  : string
Return the renamed item in the SELECT
Binding()  : void
Set the parameters for the binding
CheckInTables()  : bool
Check if the table is in the query NOTE: this method not return this object but only a boolean value
Clear()  : void
Reset all internal data (like a new)
CrossJoin()  : CSqlComposerBase
Sets the name of the table to CROSS JOIN
From()  : CSqlComposerBase
Set the main table for the query
FromList()  : CSqlComposerBase
Set multiple tables for the query
FromSubquery()  : CSqlComposerBase
Set a subquery as FROM source
GetItems()  : string
Return the list of items
GetRenamedTable()  : string
Return the Renamed Table
GetTable()  : string
Return the table name by index (the value 0=first table is the default)
GetTypes()  : array<string|int, mixed>
Return values for ? operator in the WHERE conditions
GetValues()  : array<string|int, mixed>
Return values for ? operator in the WHERE conditions
GroupBy()  : CSqlComposerBase
Set GROUP BY clause
Having()  : CSqlComposerBase
Set HAVING clause
InnerJoin()  : CSqlComposerBase
Sets the name of the table to INNER JOIN and its condition for crossing with a starting table
Join()  : CSqlComposerBase
Sets the name of the table to JOIN and its condition for crossing with a starting table
JoinItems()  : string
MySQL specific: Override concatenation for MySQL syntax
LeftJoin()  : CSqlComposerBase
Sets the name of the table to LEFT JOIN and its condition for crossing with a starting table
LowerItem()  : string
MySQL/PostgreSql/Oracle/MSQL/SQLite: SELECT LOWER($item) AS $new_name
Match()  : CSqlComposerBase
Set match element for advanced search
OrderBy()  : CSqlComposerBase
Set ORDER BY clause
OrWhere()  : CSqlComposerBase
Add "OR" WHERE condition
OuterJoin()  : CSqlComposerBase
Sets the name of the table to OUTER JOIN and its condition for crossing with a starting table
Paging()  : ISqlComposer
Impostazione dei parametri per l'impaginazione dei dati
Prepare()  : string
SQL query composer according to the MySQL standard
PrepareDelete()  : string
Build DELETE SQL statement
PrepareInsert()  : string
Build INSERT SQL statement for single record
PrepareInsertBatch()  : string
Build INSERT SQL statement for batch insert (multiple records)
PrepareUpdate()  : string
Build UPDATE SQL statement
RightJoin()  : CSqlComposerBase
Sets the name of the table to RIGHT JOIN and its condition for crossing with a starting table
Select()  : CSqlComposerBase
Set the fields of the query
SelectList()  : CSqlComposerBase
Set the fields of the query
SelectSubquery()  : CSqlComposerBase
Add a subquery in SELECT
SetRenamedTables()  : void
Set the ramed tables name
SetTypes()  : void
Set types for ? operator in the WHERE conditions
SetValues()  : void
Set values for ? operator in the WHERE conditions
Union()  : CSqlComposerBase
Add UNION to combine queries
UpperItem()  : string
MySQL/PostgreSql/Oracle/MSQL/SQLite: SELECT UPPER($item) AS $new_name
Where()  : CSqlComposerBase
Add WHERE conditions
WhereBetween()  : CSqlComposerBase
Add WHERE BETWEEN condition
WhereExists()  : CSqlComposerBase
Add WHERE EXISTS condition with a subquery
WhereIn()  : CSqlComposerBase
Add WHERE IN condition
WhereNotBetween()  : CSqlComposerBase
Add WHERE NOT BETWEEN condition
WhereNotExists()  : CSqlComposerBase
Add WHERE NOT EXISTS condition with a subquery
WhereNotIn()  : CSqlComposerBase
Add WHERE NOT IN condition
WhereNotNull()  : CSqlComposerBase
Add WHERE IS NOT NULL condition
WhereNull()  : CSqlComposerBase
Add WHERE IS NULL condition
BuildMainQuery()  : string
Build the main query
QuoteIdentifier()  : string
Quote identifier according to DBMS specific rules Default implementation uses backticks for backward compatibility Override in derived classes for specific DBMS syntax
ValidateIdentifier()  : void
Validate SQL identifier (table alias, column name)
ValidateTableName()  : void
Validate table name to prevent SQL injection

Constants

VALID_JOIN_TYPES

protected mixed VALID_JOIN_TYPES = ['JOIN', 'LEFT JOIN', 'RIGHT JOIN', 'INNER JOIN', 'LEFT OUTER JOIN', 'RIGHT OUTER JOIN', 'CROSS JOIN']

VALID_OPERATORS

protected mixed VALID_OPERATORS = ['=', '!=', '<>', '<', '>', '<=', '>=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', 'IS', 'IS NOT']

Properties

$renamed_tables

protected static array<string|int, mixed> $renamed_tables = []

Methods

__construct()

public __construct([string|null $items = '*' ][, string|null $table = null ][, string|null $as = null ][, string $conditions = null ][, array<string|int, mixed>|int|string $values = [] ][, array<string|int, mixed>|int|string $types = [] ]) : mixed
Parameters
$items : string|null = '*'

list of db fields separated by a comma

$table : string|null = null

name of the default db table

$as : string|null = null

alias table name

$conditions : string = null

WHERE conditions

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

list of values in WHERE conditions (replaced by '?')

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

list of types mapped one to one with values (it depends on used DBMS)

__toString()

Convert the SQL composer to a string (the SQL query)

public __toString() : string
Return values
string —

the string containing the query made up of all the parameters previously set

AndWhere()

Add "AND" WHERE condition

public AndWhere(string $conditions[, array<string|int, mixed>|int|string $values = [] ][, array<string|int, mixed>|int|string $types = [] ]) : CSqlComposerBase
Parameters
$conditions : string

WHERE conditions

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

list of values in WHERE conditions (replaced by '?')

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

list of types mapped one to one with values (it depends on used DBMS)

Return values
CSqlComposerBase —

this object

As()

Return the renamed item in the SELECT

public As(string $item) : string
Parameters
$item : string

item name to renamed

Return values
string —

Return the renamed item in the SELECT

Binding()

Set the parameters for the binding

public Binding([array<string|int, mixed>|null $params = null ]) : void
Parameters
$params : array<string|int, mixed>|null = null

list of parameters deferred in the query by the placeholder "?"

CheckInTables()

Check if the table is in the query NOTE: this method not return this object but only a boolean value

public CheckInTables(string $table) : bool
Parameters
$table : string

name of table

Return values
bool —

true if is included in the list of tables

CrossJoin()

Sets the name of the table to CROSS JOIN

public CrossJoin(string $table[, string|null $as = null ]) : CSqlComposerBase
Parameters
$table : string

name of table

$as : string|null = null

alias table name

Return values
CSqlComposerBase —

this object

From()

Set the main table for the query

public From(string $table[, string|null $as = null ]) : CSqlComposerBase
Parameters
$table : string

name of table

$as : string|null = null

alias table name

Return values
CSqlComposerBase —

this object

FromList()

Set multiple tables for the query

public FromList(array<string|int, mixed> $tables) : CSqlComposerBase
Parameters
$tables : array<string|int, mixed>

list of table name in the query

Return values
CSqlComposerBase —

this object

GetItems()

Return the list of items

public GetItems() : string
Return values
string —

the list of all items of query

GetRenamedTable()

Return the Renamed Table

public static GetRenamedTable(string $table) : string
Parameters
$table : string

name of renamed table

Return values
string —

the original table name

GetTable()

Return the table name by index (the value 0=first table is the default)

public GetTable([int $index = 0 ]) : string
Parameters
$index : int = 0

the index of the list of tables

Return values
string —

the name of table

GetTypes()

Return values for ? operator in the WHERE conditions

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

list of types for ? operator in the WHERE conditions

GetValues()

Return values for ? operator in the WHERE conditions

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

list of values for ? operator in the WHERE conditions

InnerJoin()

Sets the name of the table to INNER JOIN and its condition for crossing with a starting table

public InnerJoin(string $table, string $on_condition[, string|null $as = null ]) : CSqlComposerBase
Parameters
$table : string

name of table

$on_condition : string

condition for crossing with a starting table

$as : string|null = null

alias table name

Return values
CSqlComposerBase —

this object

Join()

Sets the name of the table to JOIN and its condition for crossing with a starting table

public Join(string $table, string $on_condition[, string|null $as = null ]) : CSqlComposerBase
Parameters
$table : string

name of table

$on_condition : string

condition for crossing with a starting table

$as : string|null = null

alias table name

Return values
CSqlComposerBase —

this object

JoinItems()

MySQL specific: Override concatenation for MySQL syntax

public JoinItems(array<string|int, mixed> $items, string $new_name) : string
Parameters
$items : array<string|int, mixed>

list of items name to concatenate

$new_name : string

name to view for che concatenation items

Return values
string —

the string contains the command for concatenate items

LeftJoin()

Sets the name of the table to LEFT JOIN and its condition for crossing with a starting table

public LeftJoin(string $table, string $on_condition[, string|null $as = null ]) : CSqlComposerBase
Parameters
$table : string

name of table

$on_condition : string

condition for crossing with a starting table

$as : string|null = null

alias table name

Return values
CSqlComposerBase —

this object

LowerItem()

MySQL/PostgreSql/Oracle/MSQL/SQLite: SELECT LOWER($item) AS $new_name

public LowerItem(string $item[, string $new_name = '' ]) : string
Parameters
$item : string

the name of item

$new_name : string = ''

new name for result

Return values
string

Match()

Set match element for advanced search

public Match(string $items, string $match) : CSqlComposerBase
Parameters
$items : string

list of items

$match : string

condition for advanced search

Return values
CSqlComposerBase —

this object

OrWhere()

Add "OR" WHERE condition

public OrWhere(string $conditions[, array<string|int, mixed>|int|string $values = [] ][, array<string|int, mixed>|int|string $types = [] ]) : CSqlComposerBase
Parameters
$conditions : string

WHERE conditions

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

list of values in WHERE conditions (replaced by '?')

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

list of types mapped one to one with values (it depends on used DBMS)

Return values
CSqlComposerBase —

this object

OuterJoin()

Sets the name of the table to OUTER JOIN and its condition for crossing with a starting table

public OuterJoin(string $table, string $on_condition[, string|null $as = null ]) : CSqlComposerBase
Parameters
$table : string

name of table

$on_condition : string

condition for crossing with a starting table

$as : string|null = null

alias table name

Return values
CSqlComposerBase —

this object

Paging()

Impostazione dei parametri per l'impaginazione dei dati

public Paging(int $page[, int $elements_per_page = DEFAULT_ITEMS_PER_REQUEST ]) : ISqlComposer
Parameters
$page : int

page to read (starting from 1)

$elements_per_page : int = DEFAULT_ITEMS_PER_REQUEST

maximum total of elements that make up a page

return CSqlComposerBase this object

Return values
ISqlComposer

Prepare()

SQL query composer according to the MySQL standard

public Prepare() : string
Return values
string —

the composed SQL code

PrepareDelete()

Build DELETE SQL statement

public PrepareDelete(string $table, string $conditions) : string
Parameters
$table : string

table name

$conditions : string

WHERE conditions

Return values
string —

the composed DELETE SQL statement

PrepareInsert()

Build INSERT SQL statement for single record

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

table name

$items : array<string|int, mixed>

associative array with field names as keys and values as array values

Return values
string —

the composed INSERT SQL statement

PrepareInsertBatch()

Build INSERT SQL statement for batch insert (multiple records)

public PrepareInsertBatch(string $table, array<string|int, mixed> $items, array<string|int, mixed> $batch_data) : string
Parameters
$table : string

table name

$items : array<string|int, mixed>

associative array with field names as keys for the first record

$batch_data : array<string|int, mixed>

array of arrays, each containing values for one record

Return values
string —

the composed INSERT SQL statement

PrepareUpdate()

Build UPDATE SQL statement

public PrepareUpdate(string $table, array<string|int, mixed> $items, string $conditions) : string
Parameters
$table : string

table name

$items : array<string|int, mixed>

associative array with field names as keys and values as array values

$conditions : string

WHERE conditions

Return values
string —

the composed UPDATE SQL statement

RightJoin()

Sets the name of the table to RIGHT JOIN and its condition for crossing with a starting table

public RightJoin(string $table, string $on_condition[, string|null $as = null ]) : CSqlComposerBase
Parameters
$table : string

name of table

$on_condition : string

condition for crossing with a starting table

$as : string|null = null

alias table name

Return values
CSqlComposerBase —

this object

SelectList()

Set the fields of the query

public SelectList(array<string|int, mixed> $items) : CSqlComposerBase
Parameters
$items : array<string|int, mixed>

array contains the list of db fields

Return values
CSqlComposerBase —

this object

SetRenamedTables()

Set the ramed tables name

public static SetRenamedTables(array<string|int, string> $renamed_tables) : void
Parameters
$renamed_tables : array<string|int, string>

list of renamed tables (is an associative array original_table=>renamed_table)

SetTypes()

Set types for ? operator in the WHERE conditions

public SetTypes(array<string|int, mixed> $types) : void
Parameters
$types : array<string|int, mixed>

list of types for ? operator in the WHERE conditions

SetValues()

Set values for ? operator in the WHERE conditions

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

list of values for ? operator in the WHERE conditions

UpperItem()

MySQL/PostgreSql/Oracle/MSQL/SQLite: SELECT UPPER($item) AS $new_name

public UpperItem(string $item[, string $new_name = '' ]) : string
Parameters
$item : string

the name of item

$new_name : string = ''

new name for result

Return values
string

Where()

Add WHERE conditions

public Where(string $conditions[, array<string|int, mixed>|int|string $values = [] ][, array<string|int, mixed>|int|string $types = [] ]) : CSqlComposerBase
Parameters
$conditions : string

WHERE conditions

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

list of values in WHERE conditions (replaced by '?')

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

list of types mapped one to one with values (it depends on used DBMS)

Tags
example

->where('id=?', 34, 'INT') ->where('id=34') ->where('id_parent=? AND name like ?', [34,'%abc%'],['INT','VARCHAR']) ->where('id_parent=34 AND name like '%abc%')

Return values
CSqlComposerBase —

this object

WhereBetween()

Add WHERE BETWEEN condition

public WhereBetween(string $column, mixed $min, mixed $max[, string $type = '' ]) : CSqlComposerBase
Parameters
$column : string

column name

$min : mixed

minimum value

$max : mixed

maximum value

$type : string = ''

type of values (it depends on used DBMS)

Return values
CSqlComposerBase —

this object

WhereIn()

Add WHERE IN condition

public WhereIn(string $column, array<string|int, mixed> $values[, string $type = '' ]) : CSqlComposerBase
Parameters
$column : string

column name

$values : array<string|int, mixed>

list of values for IN clause

$type : string = ''

type of values (it depends on used DBMS)

Return values
CSqlComposerBase —

this object

WhereNotBetween()

Add WHERE NOT BETWEEN condition

public WhereNotBetween(string $column, mixed $min, mixed $max[, string $type = '' ]) : CSqlComposerBase
Parameters
$column : string

column name

$min : mixed

minimum value

$max : mixed

maximum value

$type : string = ''

type of values (it depends on used DBMS)

Return values
CSqlComposerBase —

this object

WhereNotIn()

Add WHERE NOT IN condition

public WhereNotIn(string $column, array<string|int, mixed> $values[, string $type = '' ]) : CSqlComposerBase
Parameters
$column : string

column name

$values : array<string|int, mixed>

list of values for NOT IN clause

$type : string = ''

type of values (it depends on used DBMS)

Return values
CSqlComposerBase —

this object

BuildMainQuery()

Build the main query

protected BuildMainQuery() : string
Return values
string —

the main query SQL

QuoteIdentifier()

Quote identifier according to DBMS specific rules Default implementation uses backticks for backward compatibility Override in derived classes for specific DBMS syntax

protected QuoteIdentifier(string $identifier) : string
Parameters
$identifier : string

identifier to quote (table name, column name, alias)

Return values
string —

quoted identifier

ValidateIdentifier()

Validate SQL identifier (table alias, column name)

protected ValidateIdentifier(string $identifier) : void
Parameters
$identifier : string

identifier to validate

Tags
throws
InvalidArgumentException

if identifier is invalid

ValidateTableName()

Validate table name to prevent SQL injection

protected ValidateTableName(string $table) : void
Parameters
$table : string

table name to validate

Tags
throws
InvalidArgumentException

if table name is invalid


        

        
    




    

    

                                

                

                                
    

        On this page

        
    


                

                            

            

    

        

            
Search results