Amiro.CMS API Reference [beta] (available from v5.10) Amiro.Ru / Manual
Interfaces:
DB_iRecordset
Orphus
[ class tree: DB ] [ index: DB ] [ all elements ] [ Resource List ]
DB / The DB_Query class

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;
// Adds where definition.
public addWhereDef ( DB_Snippet|string $expression ) 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;
// 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 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:
  1.  echo DB_Query::getInsertQuery("visit", array(
  2.      'date'    => DB_Query::getSnippet('%s')->plain('NOW()'), // plain not quoted value
  3.      'id_user' => 1,
  4.      'id_page' => 2,
  5.      'ip'      => DB_Query::getSnippet('INET_ATON(%s)')->q('192.168.0.1'), // quoted IP value
  6.  ));
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;
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:
  1.  echo DB_Query::getUpdateQuery(
  2.      'ami_sample_plugin',
  3.       array(
  4.          'name'  => DB_Query::getSnippet('%s + %s')->plain('name')->q('...'),
  5.          'about' => DB_Query::getSnippet('%s + %s')->plain('name')->q(' + about'),
  6.       ),
  7.       DB_Query::getSnippet('WHERE id IN (%s)')->implode(array(1, 2, 3, 4))
  8.  );
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:
  1.  $oQuery = new DB_Query('visit');
  2.  $oQuery->addExpressionField("COUNT(id) quantity");
// 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:
  1.  $oQuery = new DB_Query('visit', 'i');
  2.  $oQuery->addField('id', '', 'visit_id');
  3.  $select = $oQuery->getFieldsAsString();
  4.  // $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:
  1.  $oQuery = new DB_Query('visit', 'i');
  2.  $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:
  1.  $oQuery = new DB_Query('visit');
  2.  $oQuery->addField('*');
  3.  $oQuery->addGrouping('ip');
  4.  $oQuery->setHavingDef("1");
  5.  $oQuery->addHavingDef("AND COUNT(id) > 1");
// 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:
  1.  $oQuery = new DB_Query('user', 'u');
  2.  $oQuery
  3.      ->addField("name", "u")
  4.      ->addExpressionField("COUNT(v.id) cnt")
  5.      ->addJoinedTable("visit", "v", "u.id = v.id_user", "LEFT OUTER JOIN");
// 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:
  1.  $oQuery = new DB_Query('visit');
  2.  $oQuery->addOrder('date', 'desc');
  3.  $oQuery->addOrder('id_user', 'asc');
// Adds where definition.
public addWhereDef ( DB_Snippet|string $expression ) returns DB_Query;
Expression cannot contain any quotes. Use DB_Query::getSnippet() to pass complex data.
  Example:
  1.  $oQuery = new DB_Query('visit');
  2.  $oQuery->setWhereDef("1");
  3.  $oQuery->addWhereDef("AND id_user = 1");
  4.  $oQuery->addWhereDef(
  5.      DB_Query::getSnippet("AND date BETWEEN %s AND %s")
  6.          ->q('2010-01-01')
  7.          ->q('2010-01-31')
  8.  );
// 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;
// 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:
  1.  $oQuery = new DB_Query('visit');
  2.  $oQuery->addField('*');
  3.  $oQuery->addGrouping('ip');
  4.  $oQuery->setHavingDef("COUNT(id) > 1");
// 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 SELECT query prefix.
public setPrefix ( string $prefix ) returns DB_Query;
  • string $prefix
    Prefix
  Example:
  1.  $oQuery = new DB_Query('visit');
  2.  $oQuery->setPrefix('SQL_CALC_FOUND_ROWS');
  3.  $oQuery->addField('id');
  4.  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:
  1.  $oQuery = new DB_Query('visit');
  2.  $oQuery->setWhereDef("id_user = 1");
// Converts object to query string.
public __toString ( ) returns string;
Tags:
see DB_Query::get()
Documentation generated on Wed, 21 Dec 2011 10:41:31 +0600 by