- Home
- About
- Testimonials
- Demos
- Download
- Documentation
- Blogs
- Forum
- Support

Packages:
default
System
System.Caching
System.Collections
System.Data
System.Data.ActiveRecord
System.Data.ActiveRecord.Relations
System.Data.ActiveRecord.Scaffold
System.Data.ActiveReecord.Scaffold.InputBuilder
System.Data.Commom.Sqlite
System.Data.Common
System.Data.Common.Mssql
System.Data.Common.Mysql
System.Data.Common.Oracle
System.Data.Common.Pgsql
System.Data.Common.Sqlite
System.Data.DataGateway
System.Data.SqlMap
System.Data.SqlMap.Configuration
System.Data.SqlMap.Statements
System.Exceptions
System.I18N
System.IO
System.Security
System.Util
System.Web
System.Web.Services
System.Web.UI
System.Web.UI.ActiveControls
System.Web.UI.WebControls
System.Web.UI.WebControls.assets
System.Xml
Classes:
TDataGatewayCommand
TDataGatewayEventParameter
TDataGatewayResultEventParameter
TSqlCriteria
TTableGateway

Keyword

# Class TTableGateway

    TComponent
       |
       --TTableGateway

TTableGateway class provides several find methods to get data from the database and update, insert, and delete methods.

Each method maps the input parameters into a SQL call and executes the SQL against a database connection. The TTableGateway is stateless (with respect to the data and data objects), as its role is to push data back and forth.

Example usage:

     //create a connection
      $dsn = 'pgsql:host=localhost;dbname=test';
     $conn = new TDbConnection($dsn, 'dbuser','dbpass');

     //create a table gateway for table/view named 'address'
      $table = new TTableGateway('address', $conn);

     //insert a new row, returns last insert id (if applicable)
      $id = $table->insert(array('name'=>'wei', 'phone'=>'111111'));

     $record1 = $table->findByPk($id); //find inserted record

     //finds all records, returns an iterator
      $records = $table->findAll();
     print_r($records->readAll());

     //update the row
      $table->updateByPk($record1, $id);

All methods that may return more than one row of data will return an TDbDataReader iterator.

The OnCreateCommand event is raised when a command is prepared and parameter binding is completed. The parameter object is a TDataGatewayEventParameter of which the TDataGatewayEventParameter::getCommand property can be inspected to obtain the sql query to be executed.

The OnExecuteCommand event is raised when a command is executed and the result from the database was returned. The parameter object is a TDataGatewayResultEventParameter of which the TDataGatewayEventParameter::getResult property contains the data return from the database. The data returned can be changed by setting the TDataGatewayEventParameter::setResult property.

     $table->OnCreateCommand[] = 'log_it'; //any valid PHP callback statement
      $table->OnExecuteCommand[] = array($obj, 'method_name'); // calls 'method_name' on $obj

     function log_it($sender, $param)
     {
         var_dump($param); //TDataGatewayEventParameter object.
     }

Since: 3.1
Author: Wei Zhuo <weizho[at]gmail[dot]com>

Constructor Summary
public | __construct Array Creates a new generic table gateway for a given table or view name and a database connection.

Method Summary
int | count ( string|TSqlCriteria $criteria, mixed $parameters) Find the number of records.
integer | deleteAll ( string $criteria, array $parameters) Delete records from the table with condition given by $where and binding values specified by $parameter argument.
void | deleteAllByPks ( mixed $keys) Alias for deleteByPk()
int | deleteByPk ( mixed $keys) Delete records by primary key. Usage:
array | find ( string|TSqlCriteria $criteria, mixed $parameters) Find one single record that matches the criteria.
TDbDataReader | findAll ( string|TSqlCriteria $criteria, mixed $parameters) Accepts same parameters as find(), but returns TDbDataReader instead.
TDbDataReader | findAllByPks ( mixed $keys) Similar to findByPk(), but returns TDbDataReader instead.
TDbDataReader | findAllBySql ( string $sql, array $parameters) Execute arbituary sql command with binding parameters.
array | findByPk ( mixed $keys) Find one record using only the primary key or composite primary keys. Usage:
array | findBySql ( string $sql, array $parameters) Execute arbituary sql command with binding parameters.
protected TDataGatewayCommand | getCommand ()
protected TSqlCriteria | getCriteria ( string|TSqlCriteria $criteria, mixed $parameters, array $args) Create a new TSqlCriteria object from a string $criteria. The $args are additional parameters and are used in place of the $parameters if $parameters is not an array and $args is an arrary.
TDbConnection | getDbConnection ()
mixed | getLastInsertId ()
void | getTableInfo ()
void | getTableName ()
protected void | initCommandBuilder ( TDbCommandBuilder $builder)
mixed | insert ( array $data) Inserts a new record into the table. Each array key must correspond to a column name in the table unless a null value is permitted.
void | onCreateCommand ( TDataGatewayCommand $sender, TDataGatewayEventParameter $param) Raised when a command is prepared and parameter binding is completed.
void | onExecuteCommand ( TDataGatewayCommand $sender, TDataGatewayResultEventParameter $param) Raised when a command is executed and the result from the database was returned.
protected void | setTableInfo ( TDbTableInfo $tableInfo)
protected void | setTableName ( string $tableName) Sets up the command builder for the given table.
integer | update ( array $data, string $criteria, array $parameters) Updates the table with new name-value pair $data. Each array key must correspond to a column name in the table. The update condition is specified by the $where argument and additional binding values can be specified using the $parameter argument.
mixed | __call ( mixed $method, mixed $args) Dynamic find method using parts of method name as search criteria.

Methods Inherited From TComponent

TComponent::addParsedObject(), TComponent::attachEventHandler(), TComponent::canGetProperty(), TComponent::canSetProperty(), TComponent::createdOnTemplate(), TComponent::detachEventHandler(), TComponent::evaluateExpression(), TComponent::evaluateStatements(), TComponent::getEventHandlers(), TComponent::getSubProperty(), TComponent::hasEvent(), TComponent::hasEventHandler(), TComponent::hasProperty(), TComponent::raiseEvent(), TComponent::setSubProperty(), TComponent::__get(), TComponent::__set()

Constructor Details

## __construct

public __construct | Array

Creates a new generic table gateway for a given table or view name and a database connection.

Method Details

## count

public int count | (string|TSqlCriteria $criteria , mixed $parameters )

Find the number of records.

Input
string|TSqlCriteria | $criteria | SQL condition or criteria object.
mixed | $parameters | parameter values.
Output
int | number of records.
Exception

## deleteAll

public integer deleteAll | (string $criteria , array $parameters )

Delete records from the table with condition given by $where and binding values specified by $parameter argument.

This method uses additional arguments as $parameters. E.g.

     $table->delete('age > ? AND location = ?', $age, $location);

Input
string | $criteria | delete condition.
array | $parameters | condition parameters.
Output
integer | number of records deleted.
Exception

## deleteAllByPks

public void deleteAllByPks | (mixed $keys )

Alias for deleteByPk()

Input
mixed | $keys |
Output
Exception

## deleteByPk

public int deleteByPk | (mixed $keys )

Delete records by primary key. Usage:

     $table->deleteByPk($primaryKey); //delete 1 record
      $table->deleteByPk($key1,$key2,...); //delete multiple records
      $table->deleteByPk(array($key1,$key2,...)); //delete multiple records

For composite primary keys (determined from the table definitions):

     $table->deleteByPk(array($key1,$key2)); //delete 1 record

     //delete multiple records
      $table->deleteByPk(array($key1,$key2), array($key3,$key4),...);

     //delete multiple records
      $table->deleteByPk(array( array($key1,$key2), array($key3,$key4), .. ));

Input
mixed | $keys | primary key values.
Output
int | number of records deleted.
Exception

## find

public array find | (string|TSqlCriteria $criteria , mixed $parameters )

Find one single record that matches the criteria.

Usage:

     $table->find('username = :name AND password = :pass',
                         array(':name'=>$name, ':pass'=>$pass));
     $table->find('username = ? AND password = ?', array($name, $pass));
     $table->find('username = ? AND password = ?', $name, $pass);
     //$criteria is of TSqlCriteria
      $table->find($criteria); //the 2nd parameter for find() is ignored.

Input
string|TSqlCriteria | $criteria | SQL condition or criteria object.
mixed | $parameters | parameter values.
Output
array | matching record object.
Exception

## findAll

public TDbDataReader findAll | (string|TSqlCriteria $criteria , mixed $parameters )

Accepts same parameters as find(), but returns TDbDataReader instead.

Input
string|TSqlCriteria | $criteria | SQL condition or criteria object.
mixed | $parameters | parameter values.
Output
TDbDataReader | matching records.
Exception

## findAllByPks

public TDbDataReader findAllByPks | (mixed $keys )

Similar to findByPk(), but returns TDbDataReader instead.

For scalar primary keys:

     $table->findAllByPk($key1, $key2, ...);
     $table->findAllByPk(array($key1, $key2, ...));

For composite keys:

     $table->findAllByPk(array($key1, $key2), array($key3, $key4), ...);
     $table->findAllByPk(array(array($key1, $key2), array($key3, $key4), ...));

Input
mixed | $keys | primary keys
Output
TDbDataReader | data reader.
Exception

## findAllBySql

public TDbDataReader findAllBySql | (string $sql , array $parameters )

Execute arbituary sql command with binding parameters.

Input
string | $sql | SQL query string.
array | $parameters | binding parameters, positional or named.
Output
TDbDataReader | query results.
Exception

## findByPk

public array findByPk | (mixed $keys )

Find one record using only the primary key or composite primary keys. Usage:

     $table->findByPk($primaryKey);
     $table->findByPk($key1, $key2, ...);
     $table->findByPk(array($key1,$key2,...));

Input
mixed | $keys | primary keys
Output
array | matching record.
Exception

## findBySql

public array findBySql | (string $sql , array $parameters )

Execute arbituary sql command with binding parameters.

Input
string | $sql | SQL query string.
array | $parameters | binding parameters, positional or named.
Output
array | query results.
Exception

## getCommand

protected TDataGatewayCommand getCommand | ()

Output
TDataGatewayCommand | command builder and executor.
Exception

## getCriteria

protected TSqlCriteria getCriteria | (string|TSqlCriteria $criteria , mixed $parameters , array $args )

Create a new TSqlCriteria object from a string $criteria. The $args are additional parameters and are used in place of the $parameters if $parameters is not an array and $args is an arrary.

Input
string|TSqlCriteria | $criteria | sql criteria
mixed | $parameters | parameters passed by the user.
array | $args | additional parameters obtained from function_get_args().
Output
TSqlCriteria | criteria object.
Exception

## getDbConnection

public TDbConnection getDbConnection | ()

Output
TDbConnection | database connection.
Exception

## getLastInsertId

public mixed getLastInsertId | ()

Output
mixed | last insert id, null if none is found.
Exception

## getTableInfo

public void getTableInfo | ()

Output
Exception

## getTableName

public void getTableName | ()

Output
Exception

## initCommandBuilder

protected void initCommandBuilder | (TDbCommandBuilder $builder )

Input
TDbCommandBuilder | $builder | database specific command builder.
Output
Exception

## insert

public mixed insert | (array $data )

Inserts a new record into the table. Each array key must correspond to a column name in the table unless a null value is permitted.

Input
array | $data | new record data.
Output
mixed | last insert id if one column contains a serial or sequence, otherwise true if command executes successfully and affected 1 or more rows.
Exception

## onCreateCommand

public void onCreateCommand | (TDataGatewayCommand $sender , TDataGatewayEventParameter $param )

Raised when a command is prepared and parameter binding is completed.

The parameter object is TDataGatewayEventParameter of which the TDataGatewayEventParameter::getCommand property can be inspected to obtain the sql query to be executed.

Input
TDataGatewayCommand | $sender | originator $sender
TDataGatewayEventParameter | $param |
Output
Exception

## onExecuteCommand

public void onExecuteCommand | (TDataGatewayCommand $sender , TDataGatewayResultEventParameter $param )

Raised when a command is executed and the result from the database was returned.

The parameter object is TDataGatewayResultEventParameter of which the TDataGatewayEventParameter::getResult property contains the data return from the database. The data returned can be changed by setting the TDataGatewayEventParameter::setResult property.

Input
TDataGatewayCommand | $sender | originator $sender
TDataGatewayResultEventParameter | $param |
Output
Exception

## setTableInfo

protected void setTableInfo | (TDbTableInfo $tableInfo )

Input
TDbTableInfo | $tableInfo | table or view information.
Output
Exception

## setTableName

protected void setTableName | (string $tableName )

Sets up the command builder for the given table.

Input
string | $tableName | table or view name.
Output
Exception

## update

public integer update | (array $data , string $criteria , array $parameters )

Updates the table with new name-value pair $data. Each array key must correspond to a column name in the table. The update condition is specified by the $where argument and additional binding values can be specified using the $parameter argument.

This method uses additional arguments as $parameters. E.g.

     $gateway->update($data, 'age > ? AND location = ?', $age, $location);

Input
array | $data | new record data.
string | $criteria | update condition
array | $parameters | additional binding name-value pairs.
Output
integer | number of records updated.
Exception

## __call

public mixed __call | (mixed $method , mixed $args )

Dynamic find method using parts of method name as search criteria.

Method name starting with "findBy" only returns 1 record. Method name starting with "findAllBy" returns 0 or more records. Method name starting with "deleteBy" deletes records by the trail criteria. The condition is taken as part of the method name after "findBy", "findAllBy" or "deleteBy".

The following are equivalent:

     $table->findByName($name)
     $table->find('Name = ?', $name);

     $table->findByUsernameAndPassword($name,$pass); // OR may be used
      $table->findBy_Username_And_Password($name,$pass); // _OR_ may be used
      $table->find('Username = ? AND Password = ?', $name, $pass);

     $table->findAllByAge($age);
     $table->findAll('Age = ?', $age);

     $table->deleteAll('Name = ?', $name);
     $table->deleteByName($name);

Input
mixed | $method |
mixed | $args |
Output
mixed | single record if method name starts with "findBy", 0 or more records if method name starts with "findAllBy"
Exception