Amiro.CMS API Reference
 Amiro.Ru / Manual

Interfaces:
DB_iRecordset
Orphus
Loading search...

The DB_Query class

• Introduction

Database query constructor.

• Class synopsis

DB_Query {
/* Methods */
// Returns INSERT query.
public static getInsertQuery ( string $table, $aRecord, [ bool $lowPriority = false ] ) returns DB_Snippet;
// Returns REPLACE query.
public static getReplaceQuery ( string $table, $aRecord, [ bool $lowPriority = false ] ) returns DB_Snippet;
// Returns query template object.
public static getSnippet ( string $snippet ) returns DB_Snippet;
// Returns UPDATE query.
public static getUpdateQuery ( string $table, $aRecord, [ DB_Stniplet|string $condition = '' ], [ bool $lowPriority = false ] ) returns DB_Snippet;
// Constructor.
public __construct ( [ string $table = '' ], [ string $alias = '' ] ) returns DB_Query;
// Adds expression to selection.
public addExpressionField ( DB_Snippet|string $expression ) returns DB_Query;
// Adds field to SELECT query.
public addField ( string $name, [ string $prefix = '' ], [ string $alias = '' ] ) returns DB_Query;
// Adds several fields.
public addFields ( $aNames, [ string $prefix = '' ], [ string $aliasPrefix = '' ] ) returns DB_Query;
// Adds grouping.
public addGrouping ( DB_Snippet|string $expression ) returns DB_Query;
// Adds having definition.
public addHavingDef ( DB_Snippet|string $expression ) returns DB_Query;
// Adds joined table.
public addJoinedTable ( string $name, string $alias, string $joinCondition, [ string $joinType = 'LEFT JOIN' ] ) returns DB_Query;
// Adds order.
public addOrder ( DB_Snippet|string $expression, [ string $direction = '' ] ) returns DB_Query;
// Sets FROM definition.
public addWhereDef ( string $expression ) returns DB_Query;
// Removes field from SELECT query.
public dropField ( string $name, [ string $prefix = '' ], [ string $alias = '' ] ) returns DB_Query;
// Returns query string.
public get ( [ string $forceFields = '' ] ) returns string;
// Returns fields as array.
public getFieldsAsArray ( ) returns array;
// Returns fields as string.
public getFieldsAsString ( ) returns string;
// Returns main table name.
public getMainTableAlias ( [ bool $bAsPrefix = FALSE ] ) returns string;
// Returns main table name.
public getMainTableName ( ) returns string;
// Prepends order to order fields.
public prependOrder ( DB_Snippet|string $expression, [ string $direction = '' ] ) returns DB_Query;
// Resets field list.
public resetFields ( ) returns DB_Query;
// Sets having definition.
public setHavingDef ( DB_Snippet|string $expression ) returns DB_Query;
// Sets limit parameters.
public setLimitParameters ( int $start, int $limit ) returns DB_Query;
// Sets main table alias.
public setMainTableAlias ( string $alias ) returns DB_Query;
// Sets main table name.
public setMainTableName ( string $name ) returns DB_Query;
// Sets new order.
public setOrder ( DB_Snippet|string $expression, [ string $direction = '' ] ) returns DB_Query;
// Sets SELECT query prefix.
public setPrefix ( string $prefix ) returns DB_Query;
// Sets where definition.
public setWhereDef ( DB_Snippet|string $expression ) returns DB_Query;
// Converts object to query string.
public __toString ( ) returns string;
}

• Class Details

Contains functionality to construct SELECT, INSERT and UPDATE queries.
Tags:
example DB_Query usage example

• Class Methods

// Returns INSERT query.
public static getInsertQuery ( string $table, $aRecord, [ bool $lowPriority = false ] ) returns DB_Snippet;
  • string $table
    Table name
  • array $aRecord
    Array containing keys as field names and values as field values
  • bool $lowPriority
    Low priority flag
  Example:
  •  echo DB_Query::getInsertQuery("visit", array(
  •      'date'    => DB_Query::getSnippet('%s')->plain('NOW()'), // plain not quoted value
  •      'id_user' => 1,
  •      'id_page' => 2,
  •      'ip'      => DB_Query::getSnippet('INET_ATON(%s)')->q('192.168.0.1'), // quoted IP value
  •  ));
will output 
 INSERT INTO visit
 (`date`,`id_user`,`id_page`,`ip`)
 VALUES
 (NOW(),'1','2',INET_ATON('192.168.0.1'))
// Returns REPLACE query.
public static getReplaceQuery ( string $table, $aRecord, [ bool $lowPriority = false ] ) returns DB_Snippet;
  • string $table
    Table name
  • array $aRecord
    Array containing keys as field names and values as field values
  • bool $lowPriority
    Low priority flag
// Returns query template object.
public static getSnippet ( string $snippet ) returns DB_Snippet;
  • string $snippet
    Query snippet having '%s' placeholders
Tags:
since 5.12.4
// Returns UPDATE query.
public static getUpdateQuery ( string $table, $aRecord, [ DB_Stniplet|string $condition = '' ], [ bool $lowPriority = false ] ) returns DB_Snippet;
  • string $table
    Table name
  • array $aRecord
    Array containing keys as field names and values as field values
  • DB_Stniplet|string $condition
    Condition
  • bool $lowPriority
    Low priority flag
  Example: will output 
 UPDATE ami_sample_plugin
 SET `name`=name + '...', `about`=name + ' + about'
 WHERE id IN ('1','2','3','4')
// Constructor.
public __construct ( [ string $table = '' ], [ string $alias = '' ] ) returns DB_Query;
  • string $table
    Main table name
  • string $alias
    Main table alias
// Adds expression to selection.
public addExpressionField ( DB_Snippet|string $expression ) returns DB_Query;
Expression cannot contain any quotes. Use DB_Query::getSnippet() to pass complex data.
  Example:
// Adds field to SELECT query.
public addField ( string $name, [ string $prefix = '' ], [ string $alias = '' ] ) returns DB_Query;
  • string $name
    Field name
  • string $prefix
    Field prefix
  • string $alias
    Field alias
  Example:
  •  $oQuery = new DB_Query('visit', 'i');
  •  $oQuery->addField('id', '', 'visit_id');
  •  $select = $oQuery->getFieldsAsString();
  •  // $select contains i.id visit_id
// Adds several fields.
public addFields ( $aNames, [ string $prefix = '' ], [ string $aliasPrefix = '' ] ) returns DB_Query;
  • array $aNames
    Array containing field names
  • string $prefix
    Field prefix
  • string $aliasPrefix
    Field alias prefix
  Example:
  •  $oQuery = new DB_Query('visit', 'i');
  •  $oQuery->addFields(array('id', 'date'), 'i', 'visit_');
// Adds grouping.
public addGrouping ( DB_Snippet|string $expression ) returns DB_Query;
Expression cannot contain any quotes. Use DB_Query::getSnippet() to pass complex data.
Affects "GROUP BY" query snippet.
// Adds having definition.
public addHavingDef ( DB_Snippet|string $expression ) returns DB_Query;
Expression cannot contain any quotes. Use DB_Query::getSnippet() to pass complex data.
  Example:
// Adds joined table.
public addJoinedTable ( string $name, string $alias, string $joinCondition, [ string $joinType = 'LEFT JOIN' ] ) returns DB_Query;
  • string $name
    Joined table name
  • string $alias
    Joined table alias
  • string $joinCondition
    Join condition
  • string $joinType
    Join type
  Example:
// Adds order.
public addOrder ( DB_Snippet|string $expression, [ string $direction = '' ] ) returns DB_Query;
  • DB_Snippet|string $expression
    Order field or expression (DB_Snippet is supported since 5.14.0)
  • string $direction
    Order direction
  Example:
// Sets FROM definition.
public addWhereDef ( string $expression ) returns DB_Query;
  • string $expression
Tags:
todo Detect usability
// Removes field from SELECT query.
public dropField ( string $name, [ string $prefix = '' ], [ string $alias = '' ] ) returns DB_Query;
  • string $name
    Field name
  • string $prefix
    Field prefix
  • string $alias
    Field alias
  Example:
  •  $oQuery->dropField('id', '', 'visit_id');
// Returns query string.
public get ( [ string $forceFields = '' ] ) returns string;
  • string $forceFields
    Return only these fields in selection if passed
// Returns fields as array.
public getFieldsAsArray ( ) returns array;
// Returns fields as string.
public getFieldsAsString ( ) returns string;
// Returns main table name.
public getMainTableAlias ( [ bool $bAsPrefix = FALSE ] ) returns string;
  • bool $bAsPrefix
    Append '.' if true
// Returns main table name.
public getMainTableName ( ) returns string;
// Prepends order to order fields.
public prependOrder ( DB_Snippet|string $expression, [ string $direction = '' ] ) returns DB_Query;
  • DB_Snippet|string $expression
    Order field or expression (DB_Snippet is supported since 5.14.0)
  • string $direction
    Order direction
  Example:
  •  $oQuery->setOrder('date', 'asc');
  •  $oQuery->prependOrder('id', 'desc');  // Result: ORDER BY id DESC, date ASC
Tags:
since 6.0.4
// Resets field list.
public resetFields ( ) returns DB_Query;
// Sets having definition.
public setHavingDef ( DB_Snippet|string $expression ) returns DB_Query;
Expression cannot contain any quotes. Use DB_Query::getSnippet() to pass complex data.
  Example:
// Sets limit parameters.
public setLimitParameters ( int $start, int $limit ) returns DB_Query;
  • int $start
    Start offset
  • int $limit
    Limit
// Sets main table alias.
public setMainTableAlias ( string $alias ) returns DB_Query;
  • string $alias
    Main table alias
// Sets main table name.
public setMainTableName ( string $name ) returns DB_Query;
  • string $name
    Main table name
// Sets new order.
public setOrder ( DB_Snippet|string $expression, [ string $direction = '' ] ) returns DB_Query;
  • DB_Snippet|string $expression
    Order field or expression (DB_Snippet is supported since 5.14.0)
  • string $direction
    Order direction
  Example:
Tags:
since 5.14.8
// Sets SELECT query prefix.
public setPrefix ( string $prefix ) returns DB_Query;
  • string $prefix
    Prefix
  Example:
  •  $oQuery = new DB_Query('visit');
  •  $oQuery->setPrefix('SQL_CALC_FOUND_ROWS');
  •  $oQuery->addField('id');
  •  echo $oQuery;
will output 
 SELECT SQL_CALC_FOUND_ROWS id
 FROM visit
// Sets where definition.
public setWhereDef ( DB_Snippet|string $expression ) returns DB_Query;
Expression cannot contain any quotes. Use DB_Query::getSnippet() to pass complex data.
  Example:
// Converts object to query string.
public __toString ( ) returns string;
Tags:
see DB_Query::get()