Menu

## Sql
in package

Sql.php : 18

SQL Function

##### Tags

see
  https://www.kotchasan.com/

### Table of Contents

$sql : string
  SQL statement stored here
$values : array<string|int, mixed>
  Array to store parameters for binding
__construct() : mixed
  class constructer
AVG() : static
  Calculate the average of the selected column
BETWEEN() : static
  Generate a SQL BETWEEN ... AND ... clause
CONCAT() : static
  Generate a SQL CONCAT or CONCAT_WS clause
COUNT() : static
  Count the number of records for the selected column
create() : mixed
  สร้าง Object Sql
DATE() : static
  Extract date from a DATETIME column
DATE_FORMAT() : static
  Format a date column for display
DATEDIFF() : static
  Calculate the difference in days between two dates or between a date and NOW()
DAY() : static
  Extract the day from a DATE or DATETIME column
DISTINCT() : static
  Return distinct values of a column
extractSort() : array<string|int, mixed>|string
  Extract sorting information and return as an array.
fieldName() : string|int
  Wraps a column name with backticks (`) for SQL identifiers.
FORMAT() : static
  Format a column for display
getValues() : array<string|int, mixed>
  Retrieves or merges bind parameters ($values) used for prepared statements in SQL queries.
GROUP_CONCAT() : static
  Create a GROUP_CONCAT SQL statement
HOUR() : static
  Extract the hour from a DATETIME column
IFNULL() : static
  Create an IFNULL SQL statement
ISNOTNULL() : static
  Create an IS NOT NULL SQL statement
ISNULL() : static
  Create an IS NULL SQL statement
MAX() : static
  Find the maximum value of a column
MIN() : static
  Find the minimum value of a column
MINUTE() : static
  Extract minutes from a DATETIME column
MONTH() : static
  Extract month from a DATE or DATETIME column
NEXT() : static
  Generate SQL to find the next value in a sequence (MAX + 1)
NOW() : static
  Returns the current date and time as a SQL function NOW().
POSITION() : static
  Searches for a substring in a string and returns its position. If not found, returns 0; indexing starts from 1.
quoteValue() : string|int
  Quotes and prepares a value for use in SQL queries, handling various data types and formats.
RAND() : static
  Generates a random number.
SECOND() : static
  Extracts the seconds from a DATETIME column.
strValue() : static
  Creates a SQL string literal by wrapping the given value in single quotes ('').
SUM() : static
  Calculates the sum of values in a selected column.
text() : string
  Returns the SQL command as a string.
TIMEDIFF() : static
  Calculates the time difference between two datetime columns or values.
TIMESTAMPDIFF() : static
  Calculates the difference between two datetime columns or values in specified units.
WHERE() : static
  Constructs a WHERE clause based on the provided conditions.
YEAR() : static
  Extracts the year from a DATE or DATETIME column.
buildWhere() : string
  Constructs SQL WHERE command based on given conditions.

### Properties

#### $sql

Sql.php : 25

SQL statement stored here

`protected string $sql`

#### $values

Sql.php : 32

Array to store parameters for binding

`protected array<string|int, mixed> $values`

### Methods

#### __construct()

Sql.php : 927

class constructer

`public __construct([string $sql = null ]) : mixed`

##### Parameters

$sql : string = null

##### Return values

mixed —

#### AVG()

Sql.php : 53

Calculate the average of the selected column

`public static AVG(string $column_name[, string|null $alias = null ][, bool $distinct = false ]) : static`

This function generates a SQL expression to calculate the average of a specified column.

##### Parameters

$column_name : string

  The name of the column to calculate the average for

$alias : string|null = null

  The alias for the resulting column, optional

$distinct : bool = false

  If true, calculates the average of distinct values only; default is false

##### Tags

assert

  ('id')->text() [==] 'AVG(`id`)'

demo

      $result = \Kotchasan\Database\Sql::AVG('id')->text();
      echo $result; // Outputs: AVG(`id`)

##### Return values

static —

#### BETWEEN()

Sql.php : 90

Generate a SQL BETWEEN ... AND ... clause

`public static BETWEEN(string $column_name, string $min, string $max) : static`

This function creates a SQL BETWEEN clause for a specified column and range.

##### Parameters

$column_name : string

  The name of the column for the BETWEEN clause

$min : string

  The minimum value for the range

$max : string

  The maximum value for the range

##### Tags

assert

  ('create_date', 'create_date', 'U.create_date')->text() [==] "`create_date` BETWEEN `create_date` AND U.`create_date`"

assert

  ('create_date', 'table_name.field_name', 'U.`create_date`')->text() [==] "`create_date` BETWEEN `table_name`.`field_name` AND U.`create_date`"

assert

  ('create_date', '`database`.`table`', '12-1-1')->text() [==] "`create_date` BETWEEN `database`.`table` AND '12-1-1'"

assert

  ('create_date', 0, 1)->text() [==] "`create_date` BETWEEN 0 AND 1"

demo

      $result = \Kotchasan\Database\Sql::BETWEEN('create_date', 'create_date', 'U.create_date')->text();
      echo $result; // Outputs: `create_date` BETWEEN `create_date` AND U.`create_date`

##### Return values

static —

#### CONCAT()

Sql.php : 122

Generate a SQL CONCAT or CONCAT_WS clause

`public static CONCAT(array<string|int, mixed> $fields[, string|null $alias = null ][, string|null $separator = null ]) : static`

This function creates a SQL CONCAT or CONCAT_WS clause with optional alias and separator.

##### Parameters

$fields : array<string|int, mixed>

  List of fields to concatenate

$alias : string|null = null

  The alias for the resulting concatenation, optional

$separator : string|null = null

  Null (default) to use CONCAT, specify a separator to use CONCAT_WS

##### Tags

assert

  (array('fname', 'lname'))->text() [==] "CONCAT(`fname`, `lname`)"

assert

  (array('U.fname', 'U.`lname`'), 'displayname')->text() [==] "CONCAT(U.`fname`, U.`lname`) AS `displayname`"

assert

  (array('fname', 'lname'), 'displayname', ' ')->text() [==] "CONCAT_WS(' ', `fname`, `lname`) AS `displayname`"

demo

      $result = \Kotchasan\Database\Sql::CONCAT(['fname', 'lname'])->text();
      echo $result; // Outputs: CONCAT(`fname`, `lname`)

throws
  InvalidArgumentException

  If $fields is not an array

##### Return values

static —

#### COUNT()

Sql.php : 168

Count the number of records for the selected column

`public static COUNT([string $column_name = '*' ][, string|null $alias = null ][, bool $distinct = false ]) : static`

This function generates a SQL COUNT expression for a specified column.

##### Parameters

$column_name : string = '*'

  The name of the column to count, defaults to '*'

$alias : string|null = null

  The alias for the resulting count, optional

$distinct : bool = false

  If true, counts only distinct values; default is false

##### Tags

assert

  ('id')->text() [==] 'COUNT(`id`)'

demo

      $result = \Kotchasan\Database\Sql::COUNT('id')->text();
      echo $result; // Outputs: COUNT(`id`)

##### Return values

static —

#### create()

Sql.php : 938

สร้าง Object Sql

`public static create(string $sql) : mixed`

##### Parameters

$sql : string

##### Return values

mixed —

#### DATE()

Sql.php : 204

Extract date from a DATETIME column

`public static DATE(string $column_name[, string|null $alias = null ]) : static`

This function generates a SQL DATE expression to extract the date part from a DATETIME column.

##### Parameters

$column_name : string

  The name of the DATETIME column

$alias : string|null = null

  The alias for the resulting date, optional

##### Tags

assert

  ('create_date')->text() [==] 'DATE(`create_date`)'

assert

  ('create_date', 'date')->text() [==] 'DATE(`create_date`) AS `date`'

demo

      $result = \Kotchasan\Database\Sql::DATE('create_date')->text();
      echo $result; // Outputs: DATE(`create_date`)

##### Return values

static —

#### DATE_FORMAT()

Sql.php : 272

Format a date column for display

`public static DATE_FORMAT(string $column_name, string $format[, string|null $alias = null ]) : static`

This function generates a SQL DATE_FORMAT expression to format a date column for display.

##### Parameters

$column_name : string

  The name of the date column

$format : string

  The format string for date formatting

$alias : string|null = null

  The alias for the resulting formatted date, optional

##### Tags

assert

  (Sql::NOW(), '%h:%i')->text() [==] "DATE_FORMAT(NOW(), '%h:%i')"

assert

  ('create_date', '%Y-%m-%d', 'today')->text() [==] "DATE_FORMAT(`create_date`, '%Y-%m-%d') AS `today`"

demo

      $result = \Kotchasan\Database\Sql::DATE_FORMAT(Sql::NOW(), '%h:%i')->text();
      echo $result; // Outputs: DATE_FORMAT(NOW(), '%h:%i')

##### Return values

static —

#### DATEDIFF()

Sql.php : 238

Calculate the difference in days between two dates or between a date and NOW()

`public static DATEDIFF(string $column_name1, string $column_name2[, string $alias = null ]) : static`

This function generates a SQL DATEDIFF expression to calculate the difference in days between two dates.

##### Parameters

$column_name1 : string

  The first date column or a specific date string

$column_name2 : string

  The second date column or a specific date string

$alias : string = null

  The alias for the resulting difference, optional

##### Tags

assert

  ('create_date', Sql::NOW())->text() [==] "DATEDIFF(`create_date`, NOW())"

assert

  ('2017-04-04', 'create_date')->text() [==] "DATEDIFF('2017-04-04', `create_date`)"

demo

      $result = \Kotchasan\Database\Sql::DATEDIFF('create_date', Sql::NOW())->text();
      echo $result; // Outputs: DATEDIFF(`create_date`, NOW())

##### Return values

static —

#### DAY()

Sql.php : 299

Extract the day from a DATE or DATETIME column

`public static DAY(string $column_name[, string|null $alias = null ]) : static`

This function generates a SQL DAY expression to extract the day from a DATE or DATETIME column.

##### Parameters

$column_name : string

  The name of the DATE or DATETIME column

$alias : string|null = null

  The alias for the resulting day, optional

##### Tags

assert

  ('date')->text() [==] 'DAY(`date`)'

assert

  ('date', 'd')->text() [==] 'DAY(`date`) AS `d`'

##### Return values

static —

#### DISTINCT()

Sql.php : 325

Return distinct values of a column

`public static DISTINCT(string $column_name[, string|null $alias = null ]) : static`

This function generates a SQL DISTINCT expression to return unique values of a column.

##### Parameters

$column_name : string

  The name of the column to retrieve distinct values from

$alias : string|null = null

  The alias for the resulting distinct values, optional

##### Tags

assert

  ('id')->text() [==] 'DISTINCT `id`'

##### Return values

static —

#### extractSort()

Sql.php : 354

Extract sorting information and return as an array.

`public static extractSort(array<string|int, mixed> $columns, string $sort[, array<string|int, mixed> $default = [] ]) : array<string|int, mixed>|string`

This function processes sorting parameters and returns an array of valid sorting instructions.

##### Parameters

$columns : array<string|int, mixed>

  List of columns that are valid for sorting.

$sort : string

  Sorting instructions in the format 'column_name direction'.

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

  Default sorting array if no valid sort instructions are provided.

##### Tags

assert

  (['order_date', 'order_no', 'project_type', 'company', 'delivery'], 'order_date desc,order_no ASC,project_type none') [==] array('order_date desc', 'order_no ASC')

assert

  (['order_date', 'order_no', 'project_type', 'company', 'delivery'], 'order_date desc,order_no ASC,project_type') [==] array('order_date desc', 'order_no ASC', 'project_type')

assert

  (['order_date', 'order_no', 'project_type', 'company', 'delivery'], '', ['order_date']) [==] array('order_date')

assert

  (['order_date', 'order_no', 'project_type', 'company', 'delivery'], '', 'order_date') [==] 'order_date'

##### Return values

array<string|int, mixed>|string —

#### fieldName()

Sql.php : 980

Wraps a column name with backticks (`) for SQL identifiers.

`public static fieldName(string|int $column_name) : string|int`

Column names should consist of English letters, numbers, and underscores only. If the column name contains any other characters, returns it wrapped in single quotes ('').

##### Parameters

$column_name : string|int

  The column name or value to be formatted for SQL.

##### Tags

assert

  ('C') [==] "'C'"

assert

  ('c') [==] "'c'"

assert

  ('UU') [==] "'UU'"

assert

  ('U9') [==] "'U9'"

assert

  ('id') [==] '`id`'

assert

  ('field_name') [==] '`field_name`'

assert

  ('U.id') [==] 'U.`id`'

assert

  ('U1.id') [==] 'U1.`id`'

assert

  ('U99.member_id') [==] 'U99.`member_id`'

assert

  ('U99.provinceId1') [==] 'U99.`provinceId1`'

assert

  ('U999.provinceId1') [==] "`U999`.`provinceId1`"

assert

  ('U999.`provinceId1`') [==] "`U999`.`provinceId1`"

assert

  ('U1.id DESC') [==] 'U1.`id` DESC'

assert

  ('table_name.field_name') [==] '`table_name`.`field_name`'

assert

  ('`table_name`.`field_name`') [==] '`table_name`.`field_name`'

assert

  ('table_name.`field_name`') [==] '`table_name`.`field_name`'

assert

  ('`table_name`.field_name') [==] '`table_name`.`field_name`'

assert

  ('`table_name`.field_name ASC') [==] '`table_name`.`field_name` ASC'

assert

  ('0x64656') [==] "`0x64656`"

assert

  (0x64656) [==] 411222

assert

  ('DATE(day)') [==] "'DATE(day)'"

assert

  ('DROP table') [==] "'DROP table'"

assert

  ('SQL(DATE(day))') [==] 'DATE(day)'

assert

  (Sql::DATE('day')) [==] 'DATE(`day`)'

assert

  ([]) [throws] InvalidArgumentException

throws
  InvalidArgumentException

  If the column name format is invalid.

##### Return values

string|int —

#### FORMAT()

Sql.php : 401

Format a column for display

`public static FORMAT(string $column_name, string $format[, string|null $alias = null ]) : static`

This function generates a SQL FORMAT expression to format a column for display.

##### Parameters

$column_name : string

  The name of the column to format

$format : string

  The format string for formatting

$alias : string|null = null

  The alias for the resulting formatted column, optional

##### Tags

assert

  (Sql::NOW(), 'Y-m-d')->text() [==] "FORMAT(NOW(), 'Y-m-d')"

assert

  ('create_date', 'Y-m-d', 'today')->text() [==] "FORMAT(`create_date`, 'Y-m-d') AS `today`"

##### Return values

static —

#### getValues()

Sql.php : 1018

Retrieves or merges bind parameters ($values) used for prepared statements in SQL queries.

`public getValues([array<string|int, mixed> $values = [] ]) : array<string|int, mixed>`

##### Parameters

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

  Optional. An array of bind parameters to merge or retrieve.

##### Return values

array<string|int, mixed> —

#### GROUP_CONCAT()

Sql.php : 430

Create a GROUP_CONCAT SQL statement

`public static GROUP_CONCAT(string $column_name[, string|null $alias = null ][, string $separator = ',' ][, bool $distinct = false ][, string|array<string|int, mixed> $order = null ]) : static`

This function generates a SQL GROUP_CONCAT expression to concatenate values of a column within a group.

##### Parameters

$column_name : string

  The name of the column to concatenate

$alias : string|null = null

  The alias for the resulting concatenated column, optional

$separator : string = ','

  The separator to use between concatenated values, default is ','

$distinct : bool = false

  If true, returns only distinct values; default is false

$order : string|array<string|int, mixed> = null

  The order in which concatenated values should appear

##### Tags

assert

  ('C.topic', 'topic', ', ')->text() [==] "GROUP_CONCAT(C.`topic` SEPARATOR ', ') AS `topic`"

##### Return values

static —

#### HOUR()

Sql.php : 471

Extract the hour from a DATETIME column

`public static HOUR(string $column_name[, string|null $alias = null ]) : static`

This function generates a SQL HOUR expression to extract the hour from a DATETIME column.

##### Parameters

$column_name : string

  The name of the DATETIME column

$alias : string|null = null

  The alias for the resulting hour, optional

##### Tags

assert

  ('create_date')->text() [==] 'HOUR(`create_date`)'

assert

  ('create_date', 'date')->text() [==] 'HOUR(`create_date`) AS `date`'

##### Return values

static —

#### IFNULL()

Sql.php : 499

Create an IFNULL SQL statement

`public static IFNULL(string $column_name1, string $column_name2[, string|null $alias = null ]) : static`

This function generates a SQL IFNULL expression to return the first non-null value from two columns.

##### Parameters

$column_name1 : string

  The first column name

$column_name2 : string

  The second column name

$alias : string|null = null

  The alias for the resulting expression, optional

##### Tags

assert

  ('create_date', 'U.create_date')->text() [==] "IFNULL(`create_date`, U.`create_date`)"

assert

  ('create_date', 'U.create_date', 'test')->text() [==] "IFNULL(`create_date`, U.`create_date`) AS `test`"

##### Return values

static —

#### ISNOTNULL()

Sql.php : 524

Create an IS NOT NULL SQL statement

`public static ISNOTNULL(string $column_name) : static`

This function generates a SQL IS NOT NULL expression to check if a column is not null.

##### Parameters

$column_name : string

  The column name to check for not null

##### Tags

assert

  ('U.id')->text() [==] "U.`id` IS NOT NULL"

##### Return values

static —

#### ISNULL()

Sql.php : 544

Create an IS NULL SQL statement

`public static ISNULL(string $column_name) : static`

This function generates a SQL IS NULL expression to check if a column is null.

##### Parameters

$column_name : string

  The column name to check for null

##### Tags

assert

  ('U.id')->text() [==] "U.`id` IS NULL"

##### Return values

static —

#### MAX()

Sql.php : 565

Find the maximum value of a column

`public static MAX(string $column_name[, string|null $alias = null ]) : static`

This function generates a SQL MAX expression to find the maximum value of a column.

##### Parameters

$column_name : string

  The column name to find the maximum value

$alias : string|null = null

  The alias for the resulting maximum value, optional

##### Tags

assert

  ('id')->text() [==] 'MAX(`id`)'

##### Return values

static —

#### MIN()

Sql.php : 591

Find the minimum value of a column

`public static MIN(string $column_name[, string|null $alias = null ]) : static`

This function generates a SQL MIN expression to find the minimum value of a column.

##### Parameters

$column_name : string

  The column name to find the minimum value

$alias : string|null = null

  The alias for the resulting minimum value, optional

##### Tags

assert

  ('id')->text() [==] 'MIN(`id`)'

##### Return values

static —

#### MINUTE()

Sql.php : 618

Extract minutes from a DATETIME column

`public static MINUTE(string $column_name[, string|null $alias = null ]) : static`

This function generates a SQL MINUTE expression to extract minutes from a DATETIME column.

##### Parameters

$column_name : string

  The column name to extract minutes from

$alias : string|null = null

  The alias for the resulting minutes, optional

##### Tags

assert

  ('create_date')->text() [==] 'MINUTE(`create_date`)'

assert

  ('create_date', 'date')->text() [==] 'MINUTE(`create_date`) AS `date`'

##### Return values

static —

#### MONTH()

Sql.php : 645

Extract month from a DATE or DATETIME column

`public static MONTH(string $column_name[, string|null $alias = null ]) : static`

This function generates a SQL MONTH expression to extract the month from a DATE or DATETIME column.

##### Parameters

$column_name : string

  The column name to extract the month from

$alias : string|null = null

  The alias for the resulting month, optional

##### Tags

assert

  ('date')->text() [==] 'MONTH(`date`)'

assert

  ('date', 'm')->text() [==] 'MONTH(`date`) AS `m`'

##### Return values

static —

#### NEXT()

Sql.php : 677

Generate SQL to find the next value in a sequence (MAX + 1)

`public static NEXT(string $field, string $table_name[, mixed $condition = null ][, string $alias = null ][, string $operator = 'AND' ][, string $id = 'id' ]) : static`

Used to find the next ID in a table.

##### Parameters

$field : string

  The field name to find the maximum value

$table_name : string

  The table name

$condition : mixed = null

  (optional) WHERE condition for the query

$alias : string = null

  (optional) Alias for the resulting field, null means no alias

$operator : string = 'AND'

  (optional) Logical operator like AND or OR

$id : string = 'id'

  (optional) Key field name

##### Tags

assert

  ('id', '`world`')->text() [==] '(1 + IFNULL((SELECT MAX(`id`) FROM `world` AS X), 0))'

assert

  ('id', '`world`', array(array('module_id', 'D.`id`')), 'next_id')->text() [==] '(1 + IFNULL((SELECT MAX(`id`) FROM `world` AS X WHERE `module_id` = D.`id`), 0)) AS `next_id`'

assert

  ('id', '`world`', array(array('module_id', 'D.`id`')), null)->text() [==] '(1 + IFNULL((SELECT MAX(`id`) FROM `world` AS X WHERE `module_id` = D.`id`), 0))'

##### Return values

static —

#### NOW()

Sql.php : 708

Returns the current date and time as a SQL function NOW().

`public static NOW([string|null $alias = null ]) : static`

##### Parameters

$alias : string|null = null

  Optional alias for the NOW() function result in SQL. If provided, formats the SQL as NOW() AS `$alias`.

##### Return values

static —

#### POSITION()

Sql.php : 730

Searches for a substring in a string and returns its position. If not found, returns 0; indexing starts from 1.

`public static POSITION(string $substr, string $str[, string|null $alias = null ], int $pos) : static`

##### Parameters

$substr : string

  The substring to search for. If it's a field name, it should be enclosed in ``.

$str : string

  The original string to search within. If it's a field name, it should be enclosed in ``.

$alias : string|null = null

  Optional alias for the result of the LOCATE() function in SQL. If provided, formats the SQL as LOCATE(...) AS `$alias`.

$pos : int

  Optional starting position for the search. Defaults to 0 (search from the beginning).

##### Tags

assert

  ('find', 'C.`topic`')->text() [==] "LOCATE('find', C.`topic`)"

##### Return values

static —

#### quoteValue()

Sql.php : 1065

Quotes and prepares a value for use in SQL queries, handling various data types and formats.

`public static quoteValue(string $column_name, mixed $value, array<string|int, mixed> &$values) : string|int`

Updates the $values array with bind parameters for prepared statements.

##### Parameters

$column_name : string

  The column name or identifier to associate with the value.

$value : mixed

  The value to quote and prepare for the query.

$values : array<string|int, mixed>

  Reference to an array to store bind parameters for prepared statements.

##### Tags

assert

  ('id', 'ทดสอบ', $array) [==] "'ทดสอบ'"

assert

  ('id', 'test', $array) [==] "'test'"

assert

  ('id', 'abcde012345', $array) [==] "'abcde012345'"

assert

  ('id', 123456, $array) [==] 123456

assert

  ('id', 0.1, $array) [==] 0.1

assert

  ('id', null, $array) [==] 'NULL'

assert

  ('id', 'U.id', $array) [==] "U.`id`"

assert

  ('id', 'U.`id`', $array) [==] 'U.`id`'

assert

  ('id', 'domain.tld', $array) [==] "'domain.tld'"

assert

  ('id', 'table_name.`id`', $array) [==] '`table_name`.`id`'

assert

  ('id', '`table_name`.id', $array) [==] '`table_name`.`id`'

assert

  ('id', '`table_name`.`id`', $array) [==] '`table_name`.`id`'

assert

  ('id', 'INSERT INTO', $array) [==] ':id0'

assert

  ('id', array(1, '2', null), $array) [==] "(1, '2', NULL)"

assert

  ('id', '0x64656', $array) [==] ':id0'

assert

  ('id', 0x64656, $array) [==] 411222

assert

  ('`table_name`.`id`', '0x64656', $array) [==] ':tablenameid0'

assert

  ('U1.`id`', '0x64656', $array) [==] ':u1id0'

assert

  ('U.id', '0x64656', $array) [==] ':uid0'

throws
  InvalidArgumentException

  If the value format is invalid or not handled.

##### Return values

string|int —

#### RAND()

Sql.php : 754

Generates a random number.

`public static RAND([string|null $alias = null ]) : static`

##### Parameters

$alias : string|null = null

  Optional alias for the RAND() function result in SQL. If provided, formats the SQL as RAND() AS `$alias`.

##### Tags

assert

  ()->text() [==] 'RAND()'

assert

  ('id')->text() [==] 'RAND() AS `id`'

##### Return values

static —

#### SECOND()

Sql.php : 775

Extracts the seconds from a DATETIME column.

`public static SECOND(string $column_name[, string|null $alias = null ]) : static`

##### Parameters

$column_name : string

  The name of the DATETIME column to extract seconds from.

$alias : string|null = null

  Optional alias for the SECOND() function result in SQL. If provided, formats the SQL as SECOND(...) AS `$alias`.

##### Tags

assert

  ('create_date')->text() [==] 'SECOND(`create_date`)'

assert

  ('create_date', 'date')->text() [==] 'SECOND(`create_date`) AS `date`'

##### Return values

static —

#### strValue()

Sql.php : 1144

Creates a SQL string literal by wrapping the given value in single quotes ('').

`public static strValue(string $value) : static`

##### Parameters

$value : string

  The string value to be wrapped in single quotes.

##### Return values

static —

#### SUM()

Sql.php : 800

Calculates the sum of values in a selected column.

`public static SUM(string $column_name[, string|null $alias = null ][, bool $distinct = false ]) : static`

##### Parameters

$column_name : string

  The name of the column to sum. If it's a field name, it should be enclosed in ``.

$alias : string|null = null

  Optional alias for the SUM() function result in SQL. If provided, formats the SQL as SUM(...) AS `$alias`.

$distinct : bool = false

  Optional. If true, sums only distinct values in the column. Defaults to false, summing all values in the column.

##### Tags

assert

  ('id')->text() [==] 'SUM(`id`)'

assert

  ('table_name.`id`', 'id')->text() [==] 'SUM(`table_name`.`id`) AS `id`'

assert

  ('U.id', 'id', true)->text() [==] 'SUM(DISTINCT U.`id`) AS `id`'

assert

  ('U1.id', 'id', true)->text() [==] 'SUM(DISTINCT U1.`id`) AS `id`'

##### Return values

static —

#### text()

Sql.php : 1159

Returns the SQL command as a string.

`public text([string|null $key = null ]) : string`

If $sql is null, returns :$key for binding purposes.

##### Parameters

$key : string|null = null

  The key used for binding (optional).

##### Tags

throws
  InvalidArgumentException

  When $key is provided but empty.

##### Return values

string —

#### TIMEDIFF()

Sql.php : 822

Calculates the time difference between two datetime columns or values.

`public static TIMEDIFF(string $column_name1, string $column_name2[, string $alias = null ]) : static`

##### Parameters

$column_name1 : string

  The first datetime column or value. If it's a column name, it should be enclosed in ``.

$column_name2 : string

  The second datetime column or value. If it's a column name, it should be enclosed in ``.

$alias : string = null

  Optional alias for the TIMEDIFF() function result in SQL. If provided, formats the SQL as TIMEDIFF(...) AS `$alias`.

##### Tags

assert

  ('create_date', Sql::NOW())->text() [==] "TIMEDIFF(`create_date`, NOW())"

assert

  ('2017-04-04', 'create_date')->text() [==] "TIMEDIFF('2017-04-04', `create_date`)"

##### Return values

static —

#### TIMESTAMPDIFF()

Sql.php : 846

Calculates the difference between two datetime columns or values in specified units.

`public static TIMESTAMPDIFF(string $unit, string $column_name1, string $column_name2[, string $alias = null ]) : static`

##### Parameters

$unit : string

  The unit of time difference to calculate: FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

$column_name1 : string

  The first datetime column or value. If it's a column name, it should be enclosed in ``.

$column_name2 : string

  The second datetime column or value. If it's a column name, it should be enclosed in ``.

$alias : string = null

  Optional alias for the TIMESTAMPDIFF() function result in SQL. If provided, formats the SQL as TIMESTAMPDIFF(...) AS `$alias`.

##### Tags

assert

  ('HOUR', 'create_date', Sql::NOW())->text() [==] "TIMESTAMPDIFF(HOUR, `create_date`, NOW())"

assert

  ('MONTH', '2017-04-04', 'create_date')->text() [==] "TIMESTAMPDIFF(MONTH, '2017-04-04', `create_date`)"

##### Return values

static —

#### WHERE()

Sql.php : 893

Constructs a WHERE clause based on the provided conditions.

`public static WHERE(mixed $condition[, string $operator = 'AND' ][, string $id = 'id' ]) : static`

##### Parameters

$condition : mixed

  The condition(s) to build the WHERE clause. Can be:

  - A scalar value for simple comparisons.
  - An array for more complex conditions:
    - [column, operator, value]
    - [column, 'IN', array(values)]
    - [column, 'NOT IN', Sql::create(subquery)]
    - Nested arrays for complex logical conditions.
$operator : string = 'AND'

  (optional) The logical operator to combine multiple conditions ('AND' or 'OR'). Defaults to 'AND'.

$id : string = 'id'

  (optional) The key field name. Defaults to 'id' if not specified.

##### Tags

assert

  (1)->text() [==] "`id` = 1"

assert

  ('1')->text() [==] "`id` = '1'"

assert

  (0.1)->text() [==] "`id` = 0.1"

assert

  ('ทดสอบ')->text() [==] "`id` = 'ทดสอบ'"

assert

  (null)->text() [==] "`id` = NULL"

assert

  (0x64656)->text() [==] "`id` = 411222"

assert

  ('SELECT * FROM')->text() [==] "`id` = :id0"

assert

  (Sql::create('EXISTS SELECT FROM WHERE'))->text() [==] "EXISTS SELECT FROM WHERE"

assert

  (array('id', '=', 1))->text() [==] "`id` = 1"

assert

  (array('U.id', '2017-01-01 00:00:00'))->text() [==] "U.`id` = '2017-01-01 00:00:00'"

assert

  (array('id', 'IN', array(1, '2', null)))->text() [==] "`id` IN (1, '2', NULL)"

assert

  (array('id', 'SELECT * FROM'))->text() [==] "`id` = :id0"

assert

  (array('U.`id`', 'NOT IN', Sql::create('SELECT * FROM')))->text() [==] "U.`id` NOT IN SELECT * FROM"

assert

  (array(array('id', 'IN', array(1, '2', null))))->text() [==] "`id` IN (1, '2', NULL)"

assert

  (array(array('U.id', 1), array('U.id', '!=', '1')))->text() [==] "(U.`id` = 1 AND U.`id` != '1')"

assert

  (array(array(Sql::MONTH('create_date'), 1), array(Sql::YEAR('create_date'), 1)))->text() [==] "(MONTH(`create_date`) = 1 AND YEAR(`create_date`) = 1)"

assert

  (array(array('id', array(1, 'a')), array('id', array('G.id', 'G.`id2`'))))->text() [==] "(`id` IN (1, 'a') AND `id` IN (G.`id`, G.`id2`))"

assert

  (array(array('id', array('', 'th'))))->text() [==] "`id` IN ('', 'th')"

assert

  (array(Sql::YEAR('create_date'), Sql::YEAR('`create_date`')))->text() [==] "YEAR(`create_date`) = YEAR(`create_date`)"

assert

  (array('ip', 'NOT IN', array('', '192.168.1.2')))->text() [==] "`ip` NOT IN ('', '192.168.1.2')"

assert

  (array(1, 1))->text() [==] "1 = 1"

assert

  (array(array('username', NULL), array('username', '=', NULL), array('username', '!=', NULL)))->text() [==] "(`username` IS NULL AND `username` IS NULL AND `username` IS NOT NULL)"

##### Return values

static —

#### YEAR()

Sql.php : 913

Extracts the year from a DATE or DATETIME column.

`public static YEAR(string $column_name[, string|null $alias = null ]) : static`

##### Parameters

$column_name : string

  The name of the DATE or DATETIME column. Should be enclosed in `` if it's a column name.

$alias : string|null = null

  Optional alias for the YEAR() function result in SQL. If provided, formats the SQL as YEAR(...) AS `$alias`.

##### Tags

assert

  ('date')->text() [==] 'YEAR(`date`)'

assert

  ('date', 'y')->text() [==] 'YEAR(`date`) AS `y`'

##### Return values

static —

#### buildWhere()

Sql.php : 1182

Constructs SQL WHERE command based on given conditions.

`private buildWhere(mixed $condition, array<string|int, mixed> &$values, string $operator, string $id) : string`

##### Parameters

$condition : mixed

  The condition(s) to build into WHERE clause.

$values : array<string|int, mixed>

  Array to collect values for parameter binding.

$operator : string

  Logical operator (e.g., AND, OR) to combine multiple conditions.

$id : string

  Field name used as key in conditions.

##### Return values

string —

## Search results