Skip to content

Latest commit

 

History

History
358 lines (154 loc) · 6.32 KB

JSMF-Database-Query.md

File metadata and controls

358 lines (154 loc) · 6.32 KB

JSMF\Database\Query

  • Class name: Query
  • Namespace: JSMF\Database

Methods

__construct

mixed JSMF\Database\Query::__construct(\JSMF\Database $database, String $command, Int|null $fetchCount)

creates new query

  • Visibility: public

Arguments

  • $database JSMF\Database
  • $command String - <p>(INSERT,UPDATE,DELETE,SELECT)</p>
  • $fetchCount Int|null - <p>(for select commands; fetch one row or all)</p>

getLastStatement

\PDOStatement JSMF\Database\Query::getLastStatement()

returns the last PDOStatement if exists or null

  • Visibility: public

from

\JSMF\Database\Query JSMF\Database\Query::from(String $table, String|null $tableAlias)

adds the FROM clause to select commmands

  • Visibility: public

Arguments

  • $table String
  • $tableAlias String|null

where

\JSMF\Database\Query JSMF\Database\Query::where(Array $queries, Array $params)

add WHERE clause to select, update, delete commands

  • Visibility: public

Arguments

  • $queries Array
  • $params Array - <p>(the PDO params used in the queries)</p>

Example for $queries

$queries = [
    [ 'alias.fieldname', '=?'],
    'AND',
    [ 'alias.fieldname2', 'IS NOT NULL', 'OR', [
                                            ['alias.fieldname3', 'IS NULL'],
                                            'AND'
                                            ['alias.fieldname4', 'LIKE "%foo"']
                                         ]
    ],
 ]

This will result in a WHERE clause like:

WHERE (alias.fieldname=?) AND (alias.fieldname2 IS NOT NULL OR (alias.fieldname3 IS NULL AND alias.fieldname4 LIKE "%foo"))

Queries can be nested as deep as needed by nesting the array too (example with fieldname3 and fieldname4) Each part of the query that is an own array will be surrounded by brackets

It looks more complicated than writing the query by hand, but with this technique the database class can ensure the correct COLLATION for each field

orderBy

mixed JSMF\Database\Query::orderBy(\JSMF\Database\string $column, String|\JSMF\Database\ASC $direction)

adds order by (can be used multiple times)

@param String $column

  • Visibility: public

Arguments

  • $column JSMF\Database\string
  • $direction String|JSMF\Database\ASC - <p>@return \JSMF\Database\Query @throw JSMF\Exception</p>

groupBy

\JSMF\Database\Query JSMF\Database\Query::groupBy(\JSMF\Database\string $column)

adds group by (can be used multiple times)

@param String $column

  • Visibility: public

Arguments

  • $column JSMF\Database\string

limit

mixed JSMF\Database\Query::limit(Int $offset, Int $count)

set limit

  • Visibility: public

Arguments

  • $offset Int
  • $count Int - <p>@return \JSMF\Database\Query @throw JSMF\Exception</p>

join

\JSMF\Database\Query JSMF\Database\Query::join(String $table, \JSMF\Database\string $tableAlias, String $on, Array $fields, Array $params)

add JOIN

  • Visibility: public

Arguments

  • $table String
  • $tableAlias JSMF\Database\string
  • $on String - <p>(ON Command)</p>
  • $fields Array - <p>(fields to select from the joined table)</p>
  • $params Array - <p>(additional params when used in ON query)</p>

leftJoin

\JSMF\Database\Query JSMF\Database\Query::leftJoin(String $table, \JSMF\Database\string $tableAlias, String $on, Array $fields, Array $params)

add LEFT JOIN

  • Visibility: public

Arguments

  • $table String
  • $tableAlias JSMF\Database\string
  • $on String - <p>(ON Command)</p>
  • $fields Array - <p>(fields to select from the joined table)</p>
  • $params Array - <p>(additional params when used in ON query)</p>

innerJoin

\JSMF\Database\Query JSMF\Database\Query::innerJoin(String $table, \JSMF\Database\string $tableAlias, String $on, Array $fields, Array $params)

add INNER JOIN

  • Visibility: public

Arguments

  • $table String
  • $tableAlias JSMF\Database\string
  • $on String - <p>(ON Command)</p>
  • $fields Array - <p>(fields to select from the joined table)</p>
  • $params Array - <p>(additional params when used in ON query)</p>

set

\JSMF\Database\Query JSMF\Database\Query::set(String $key, mixed $value)

set column data for INSERT or UPDATE commands

  • Visibility: public

Arguments

  • $key String
  • $value mixed

values

\JSMF\Database\Query JSMF\Database\Query::values(Array $data)

set column data for INSERT or UPDATE commands

  • Visibility: public

Arguments

  • $data Array - <p>(key=>value)</p>

setTable

Void JSMF\Database\Query::setTable(String $table, String|null $tableAlias)

set the table where the query should work on

  • Visibility: public

Arguments

  • $table String - <p>(database.tablename)</p>
  • $tableAlias String|null - <p>(optional alias, recommended)</p>

getCommand

String JSMF\Database\Query::getCommand()

get the current query command

  • Visibility: public

addWhere

Void JSMF\Database\Query::addWhere(Array $queries, Array $params)

add a WHERE clause

  • Visibility: public

Arguments

  • $queries Array - <p>(for example, see doc-comment in JSMF\Database::where())</p>
  • $params Array - <p>(the PDO params used in the queries)</p>

addFields

mixed JSMF\Database\Query::addFields(Array $fields)

adds fields for select

  • Visibility: public

Arguments

  • $fields Array

addCommandExtra

mixed JSMF\Database\Query::addCommandExtra(\JSMF\Database\string $commandExtra)

extra to append after command (e.g. for DISTINCT or SQL_NO_CACHE)

  • Visibility: public

Arguments

  • $commandExtra JSMF\Database\string

addOnDuplicateKeyUpdate

mixed JSMF\Database\Query::addOnDuplicateKeyUpdate()

appends the "on duplicate key update .

.." phrase to the insert query

  • Visibility: public

execute

Boolean|Mixed JSMF\Database\Query::execute()

executes the prepared query

  • Visibility: public