Skip to content

Shortcut Methods

Jump to bottom Edit New page
Damien edited this page Jun 8, 2020 · 5 revisions

Detailed Shortcut Methods

The detailed shortcut methods outlined below are used in the SQL methods within the library.

All examples below will be based off an example table as so

| id | name        | email             | phone  | country | active |
|----|-------------|-------------------|--------|---------|--------|
| 1  | john john   | [email protected]  | 123456 | US      | 1      |
| 2  | john doe    | [email protected] | 345678 | US      | 1      |
| 3  | scott brown | [email protected] | 123654 | NZ      | 1      |
| 4  | jane smith  | [email protected]  | 345654 | UK      | 0      |
| 5  | sarah davis | [email protected] | 123678 | AU      | 1      |
| 6  | david brown | [email protected]  | 987456 | UK      | 0      |
| 7  | allan brown | [email protected] | 845679 | AU      | 1      |

For more information on connecting, look at the example connections in the installation section of the wiki

All the examples below were run with prepare statement support on

SQL Methods / Functions

Methods

coming soon...

Functions

where

where -- A standard function that can be used inside the create_select(), delete(), insert_select(), replace(), select_into(), selecting(), update(), union(), and unionAll() methods

Description

string where (array(s) conditions) standard use

string where (array(s) (string key, string|constant operator, string value, string|constant combine, string|constant combineShifted)) array use

string where (string(s) "string key string|constant operator string value string|constant combine string|constant combineShifted") string use (double spaced) TODO: Need confirmation on how this works as testing failed

where() adds a WHERE clause into your statement. The value(s) entered in here are comparison expressions.

Example 1 This example shows basic usage

// SQL Select with `where()` and `eq()`
$db->selecting(
  'profile',
  'name, country',
  where(
    eq('country', 'US')
  )
);

// Will result in
"SELECT name, country FROM profile WHERE country = 'US'"

Example 1 Results

| name      | country |
|-----------|---------|
| john john | US      |
| john doe  | US      |

Example 2 This example has multiple conditions

// SQL Select with `where()`, `eq()`, and `like()`
$db->selecting(
  'profile',
  'name, country',
  where(
    eq('country', 'US', _AND),
    like('name', '%doe%')
  )
);

// Will result in
"SELECT name, country FROM profile WHERE country = 'US' AND name LIKE '%doe%'"

Example 2 Results

| name      | country |
|-----------|---------|
| john doe  | US      |

Example 3 This example uses the array method

// SQL Select with `where()` using the array method
$db->selecting(
  'profile',
  'name, country',
  where(
    array('country', EQ, 'UK', _AND),
    array('name', _LIKE, '%smith%')
  )
);

// Will result in
"SELECT name, country FROM profile WHERE country = 'UK' AND name LIKE '%smith%'"

Example 3 Results

| name       | country |
|------------|---------|
| jane smith | UK      |

grouping

grouping -- A standard function that can be used inside the where() function

Description

array grouping (array(s) conditions)

grouping() adds a group around any of the conditions / comparison expressions in your where function. The value(s) entered in here are comparison expressions.

Example

$db->selecting(
  'profile',
  'name, country',
  where(
    like('name', '%brown%'),
    grouping(
      eq('country', 'UK', _OR),
      eq('country', 'NZ')
    )
  )
);

// Will result in
"SELECT name, country FROM profile WHERE name LIKE '%brown' AND ( country = 'UK' OR country = 'NZ' )"

Example Results

| name        | country |
|-------------|---------|
| scott brown | NZ      |
| david brown | UK      |

groupBy

groupBy -- A standard function that can be used inside the selecting() method

Description

string groupBy (string columns)

groupBy() adds a GROUP BY clause into your SELECT statement. The value(s) entered in here are a string value column name.

Example

// SQL Select with `groupBy()`
$db->selecting(
  'profile',
  'COUNT(ID), country',
  groupBy('country')
);

// Will result in
"SELECT COUNT(ID), country FROM profile GROUP BY country"

Example Results

| COUNT(ID) | country |
|-----------|---------|
| 2         | AU      |
| 1         | NZ      |
| 2         | UK      |
| 2         | US      |

having

having -- A standard function that can be used inside the selecting() method

Description

string having (array conditions)

having() adds a HAVING clause into your SELECT statement. The value(s) entered in here are the same comparisons entered into a where() function.

Example

// SQL Select with `having()`
$db->selecting(
  'profile',
  'COUNT(ID), country',
  groupBy('country'),
  having(
    gt('COUNT(ID)', 1)
  )
);

// Will result in
"SELECT COUNT(ID), country FROM profile GROUP BY country HAVING COUNT(ID) > '1'"

Example Results

| COUNT(ID) | country |
|-----------|---------|
| 2         | UK      |
| 2         | US      |

orderBy

orderBy -- A standard function that can be used inside the selecting() method

Description

string orderBy (string column(s), string ASC|DESC)

orderBy() adds an ORDER BY clause into your SELECT statement. The value(s) entered in here are the column name to order by and which direction to order by.

Example 1

// SQL Select with `orderBy()`
$db->selecting(
  'profile',
  'name, country',
  orderBy('country', 'ASC')
);

// Will result in
"SELECT name, country FROM profile ORDER BY country ASC"

Example 1 Results

| name        | country |
|-------------|---------|
| sarah davis | AU      |
| allan brown | AU      |
| scott brown | NZ      |
| jane smith  | UK      |
| david brown | UK      |
| john john   | US      |
| john doe    | US      |

Example 2 You can also order by multiple columns with a slight code change inside the first orderBy arg

// SQL Select with `orderBy()`
$db->selecting(
  'profile',
  'name, country',
  orderBy('country ASC, name', 'ASC')
);

// Will result in
"SELECT name, country FROM profile ORDER BY country ASC, name ASC"

Example 2 Results

| name        | country |
|-------------|---------|
| allan brown | AU      |
| sarah davis | AU      |
| scott brown | NZ      |
| david brown | UK      |
| jane smith  | UK      |
| john doe    | US      |
| john john   | US      |

limit

limit -- A standard function that can be used inside the selecting() method

Description

string limit (string numberOfRecords [, string offset])

limit() adds a LIMIT clause into your SELECT statement. The value(s) entered in here the number of records to display and the offset to start at. The offset is optional

Example 1 This example does not have an offset defined

// SQL Select with `limit()`
$db->selecting(
  'profile',
  'name',
  limit(1)
);

// Will result in
"SELECT name FROM profile LIMIT 1"

Example 1 Results

| name      |
|-----------|
| john john |

Example 2 This example has an offset defined

// SQL Select with `limit()`
$db->selecting(
  'profile',
  'name',
  limit(1, 2)
);

// Will result in
"SELECT name FROM profile LIMIT 1 OFFSET 2"

Example 1 Results

| name        |
|-------------|
| scott brown |

Operator expressions

Operator expressions are defined constants and are generally used in conditions / comparison expressions in the array method to key and value together.

  • EQ will result in =

  • NEQ will result in <>

  • LT will result in <

  • LTE will result in <=

  • GT will result in >

  • GTE will result in >=

  • _IN will result in IN

  • _notIN will result in NOT IN

  • _LIKE will result in LIKE

  • _notLIKE will result in NOT LIKE

  • _BETWEEN will result in BETWEEN

  • _notBETWEEN will result in NOT BETWEEN

  • _isNULL will result in IS NULL

  • _notNULL will result in IS NOT NULL

Combine Operators

Combine operators are defined constants and are used to join comparison expressions together. The default value for most comparison expressions will be _AND.

  • _AND will result in AND
  • _OR will result in OR
  • _NOT will result in NOT
  • _andNOT will result in AND NOT

Conditions / Comparison Expressions

The variadic ...$whereConditions, and ...$conditions parameters, represent the following global functions. They are comparison expressions returning an array with the given arguments, the last arguments of _AND, _OR, _NOT, _andNOT will combine expressions. The default value for most comparison expressions will be _AND.

Add a custom footer
Add a custom sidebar
Clone this wiki locally