Data Access Objects (DAO)

Data Access Objects (DAO) provides a generic API to access data stored in different database management systems (DBMS). As a result, the underlying DBMS can be changed to a different one without requiring change of the code which uses DAO to access the data.

Yii DAO is built on top of PHP Data Objects (PDO) which is an extension providing unified data access to many popular DBMS, such as MySQL, PostgreSQL. Therefore, to use Yii DAO, the PDO extension and the specific PDO database driver (e.g. PDO_MYSQL) have to be installed.

Yii DAO mainly consists of the following four classes:

In the following, we introduce the usage of Yii DAO in different scenarios.

Establishing Database Connection

To establish a database connection, create a CDbConnection instance and activate it. A data source name (DSN) is needed to specify the information required to connect to the database. A username and password may also be needed to establish the connection. An exception will be raised in case an error occurs during establishing the connection (e.g. bad DSN or invalid username/password).




The format of DSN depends on the PDO database driver in use. In general, a
DSN consists of the PDO driver name, followed by a colon, followed by the
driver-specific connection syntax. See PDO
documentation for complete
information. Below is a list of commonly used DSN formats:



Because CDbConnection extends from CApplicationComponent, we can also
use it as an application
component. To do so, configure
in a db (or other name) application component in the application
configuration as follows,






We can then access the DB connection via Yii::app()->db which is already
activated automatically, unless we explictly configure
CDbConnection::autoConnect to be false. Using this approach, the single
DB connection can be shared in multiple places in our code.



Executing SQL Statements



Once a database connection is established, SQL statements can be executed
using CDbCommand. One creates a CDbCommand instance by calling
CDbConnection::createCommand() with the specified SQL statement:






A SQL statement is executed via CDbCommand in one of the following two
ways:



An exception will be raised if an error occurs during the execution of SQL
statements.






Fetching Query Results



After CDbCommand::query() generates the CDbDataReader instance, one
can retrieve rows of resulting data by calling CDbDataReader::read()
repeatedly. One can also use CDbDataReader in PHP's foreach language
construct to retrieve row by row.








Note: Unlike query(), all queryXXX() methods
  return data directly. For example, queryRow()
  returns an array representing the first row of the querying result.





Using Transactions



When an application executes a few queries, each reading and/or writing
information in the database, it is important to be sure that the database
is not left with only some of the queries carried out. A transaction,
represented as a CDbTransaction instance in Yii, may be initiated in this
case:



The above workflow can be implemented using the following code:






Binding Parameters



To avoid SQL injection
attacks and to improve
performance of executing repeatedly used SQL statements, one can "prepare"
an SQL statement with optional parameter placeholders that are to be
replaced with the actual parameters during the parameter binding process.



The parameter placeholders can be either named (represented as unique
tokens) or unnamed (represented as question marks). Call
CDbCommand::bindParam() or CDbCommand::bindValue() to replace these
placeholders with the actual parameters. The parameters do not need to be
quoted: the underlying database driver does it for you. Parameter binding
must be done before the SQL statement is executed.






The methods bindParam() and
bindValue() are very similar. The only difference
is that the former binds a parameter with a PHP variable reference while
the latter with a value. For parameters that represent large blocks of data
memory, the former is preferred for performance consideration.



For more details about binding parameters, see the relevant PHP
documentation.



Binding Columns



When fetching query results, one can also bind columns with PHP variables
so that they are automatically populated with the latest data each time a
row is fetched.






Using Table Prefix



Yii provides integrated support for using
table prefix. Table prefix means a string that is prepended to the names of
the tables in the currently connected database. It is mostly used in a shared
hosting environment where multiple applications share a single database and use
different table prefixes to differentiate from each other. For example, one
application could use tbl_ as prefix while the other yii_.



To use table prefix, configure the CDbConnection::tablePrefix property to be
the desired table prefix. Then, in SQL statements use {{TableName}} to refer
to table names, where TableName means the table name without prefix. For example,
if the database contains a table named tbl_user where tbl_ is configured as the
table prefix, then we can use the following code to query about users:






$Id$