Database Access Objects. 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: CDbConnection: represents a connection to a database.CDbCommand: represents an SQL statement to execute against a database.CDbDataReader: represents a forward-only stream of rows from a query result set.CDbTransaction: represents a DB transaction.
In the following, we introduce the usage of Yii DAO in different scenarios. Query Builder. The Yii Query Builder provides an object-oriented way of writing SQL statements.
It allows developers to use class methods and properties to specify individual parts of a SQL statement. It then assembles different parts into a valid SQL statement that can be further executed by calling the DAO methods as described in Data Access Objects. The following shows a typical usage of the Query Builder to build a SELECT SQL statement: $user = Yii::app()->db->createCommand() ->select('id, username, profile') ->from('tbl_user u') ->join('tbl_profile p', 'u.id=p.user_id') ->where('id=:id', array(':id'=>$id)) ->queryRow(); The Query Builder is best used when you need to assemble a SQL statement procedurally, or based on some conditional logic in your application.
It is not mandatory to use the Query Builder. CDbCriteria. Look up a class, method, property or event CDbCriteria represents a query criteria, such as conditions, ordering by, limit/offset.
It can be used in AR query methods such as CActiveRecord::find and CActiveRecord::findAll. $criteria=new CDbCriteria(); $criteria->compare('status',Post::STATUS_ACTIVE); $criteria->addInCondition('id',array(1,2,3,4,5,6)); $posts = Post::model()->findAll($criteria); Public Properties Hide inherited properties Property Details public string $alias; the alias name of the table. Public string $condition; query condition. Public boolean $distinct; whether to select distinct rows of data only. Public string $group; how to group the query results. Public string $having; the condition to be applied with GROUP-BY clause. Public string $index; the name of the AR attribute whose value should be used as index of the query result array. CDbConnection. Look up a class, method, property or event CDbConnection represents a connection to a database.
CDbConnection works together with CDbCommand, CDbDataReader and CDbTransaction to provide data access to various DBMS in a common set of APIs. They are a thin wrapper of the PDO PHP extension. To establish a connection, set active to true after specifying connectionString, username and password. The following example shows how to create a CDbConnection instance and establish the actual connection: $connection=new CDbConnection($dsn,$username,$password); $connection->active=true; After the DB connection is established, one can execute an SQL statement like the following: CDbCommand. Look up a class, method, property or event CDbCommand represents an SQL statement to execute against a database.
It is usually created by calling CDbConnection::createCommand. The SQL statement to be executed may be set via Text. To execute a non-query SQL (such as insert, delete, update), call execute. To execute an SQL statement that returns result data set (such as SELECT), use query or its convenient versions queryRow, queryColumn, or queryScalar. If an SQL statement returns results (such as a SELECT SQL), the results can be accessed via the returned CDbDataReader. CDbTransaction. Look up a class, method, property or event CDbTransaction represents a DB transaction.
It is usually created by calling CDbConnection::beginTransaction. The following code is a common scenario of using transactions: