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. If an SQL statement returns results (such as a SELECT SQL), the results can be accessed via the returned CDbDataReader. CDbCommand supports SQL statement preparation and parameter binding.
Starting from version 1.1.6, CDbCommand can also be used as a query builder that builds a SQL statement from code fragments. $user = Yii::app()->db->createCommand() ->select('username, password') ->from('tbl_user') ->where('id=:id', array(':id'=>1)) ->queryRow(); Public Properties Hide inherited properties Public Methods Hide inherited methods Property Details the connection associated with this command Returns a value indicating whether SELECT DISTINCT should be used. Returns the FROM part in the query. if(!
If(! Select WHERE IS NULL in Query Builder? Query Builder: Multiple calls to select. I figured out a little something that lets you conditionally add to your query as it is being built, avoiding a bunch of if statements. Basically trying to do what the above feature would do. See http ://code.google.com /p/yii/issues/detail ? Id=1856 (remove spaces, sorry first post). Looks like this feature wont be added.
In my case I needed a where clause with multiple and variable numbers of AND comparisons. Set up query normally, omitting the where clause: $command = Yii::app()->db->createCommand();$command->select('v.id as vID,sr.id AS srID') ->from('whatever v') ->join('whateverelse sr','v.id = sr.joinfield'); Use 2 arrays to track the conditions and params what will be passed to the where function of CDBCommand. $whereConditions = array('and');$whereParams = array(); Figure out if you want to add to the where clause: Then later on, add some more conditions: $whereConditions[] = 'v.afield = :somevalue';$whereData[':somevalue'] = $someValue; You end up with a WHERE clause like:
Working with Databases: Database Access Objects | The Definitive Guide to Yii. 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. 1. To establish a database connection, create a CDbConnection instance and activate it. 2. 3. Working with Databases: Query Builder | The Definitive Guide to Yii. 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. Note: Query builder cannot be used to modify an existing query specified as a SQL statement. 1. To start using the Query Builder, we create a new instance of CDbCommand as follows, 2. Select() 3. Working with Databases: Query Builder | The Definitive Guide to Yii.
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.
The main benefits of using the Query Builder include: It is not mandatory to use the Query Builder. Note: Query builder cannot be used to modify an existing query specified as a SQL statement. 1. $command = Yii::app()->db->createCommand(); 2. Where In (...) With Yii Query Builder.