I posted here examples of createCommand Query with properties. createCommand Properties are distinct, from, group, having, join, limit, offset, params, pdoStatement, select and where.
CDbCommand represents an SQL statement to execute against a database. bindParam used to bind a php variable to parameter in SQL query. bindValue is used to bind a value to and SQL Parameter with automatic prepared
Functions
$connection=Yii::app()->db; $command=$connection->createCommand(); $dataReader=$command->query(); // execute a query SQL $rows=$command->queryAll(); // all rows of result $row=$command->queryRow(); // the first row of result $column=$command->queryColumn(); // first column of result $value=$command->queryScalar(); // first field in the first row of result
text, bindParam, execute
$connection=Yii::app()->db; $sql="SELECT * FROM tbl_user WHERE user_id=':uid'"; $command=$connection->createCommand(); $command->text=$sql; $command->bindParam(':uid',$userid, PDO::PARAM_INT); $command->execute();
OUTPUT SQL
SELECT * FROM tbl_user WHERE user_id=':uid'
from, select, where, queryrow
$user = Yii::app()->db->createCommand() ->select('username, password') ->from('tbl_user') ->where('user_id=:id', array(':id'=>1)) //where($condition, $params) ->queryRow();
bindValue
$user = Yii::app()->db->createCommand() ->select('username, password') ->from('tbl_user') ->where('user_id=:id') //where($condition, $params) ->bindValue(":id", 1, PDO::PARAM_INT); ->queryRow();
OUTPUT SQL
SELECT 'user_name', 'password' FROM 'tbl_user' WHERE user_id=:id
limit, queryAll
$user = Yii::app()->db->createCommand() ->select('username, password') ->from('tbl_user') ->where('user_id=:id', array(':id'=>1)) ->limit(1) ->queryAll();
OUTPUT SQL
SELECT `user_name`, `password` FROM `tbl_user` WHERE user_id=:id LIMIT 1
limit, offset, order
$user = Yii::app()->db->createCommand() ->select('user_id,username, user_type') ->from('tbl_user') ->limit(10) ->offset(2) ->order(user_id) ->queryAll();
SELECT `user_id`, `user_name`, `usertype` FROM `tbl_user` ORDER BY `user_id` LIMIT 10 OFFSET 2;
DISTINCT
$user = Yii::app()->db->createCommand() ->select('DISTINCT user_id, user_name') /**(OR) ->->selectDistinct('user_id, user_name') **/ ->from('tbl_user_commants') ->queryAll();
OUTPUT SQL
SELECT DISTINCT `user_id`, `user_name` FROM `tbl_user_commants`
andWhere
<?php $user=Yii::app()->db->createCommand() ->select("username, usertype") ->from("usermaster") ->andWhere("usertype=:id",array(':id'=>1)) ->andWhere("isactive=:status",array(':status'=>1)) ->queryAll(); print_r($user); ?>
OUTPUT SQL
SELECT `username`, `usertype` FROM `usermaster` WHERE (usertype=:id) AND (isactive=:status)
orWhere
<?php $user=Yii::app()->db->createCommand() ->select("username, usertype") ->from("usermaster") ->andWhere("usertype=:id",array(':id'=>1)) ->orWhere("isactive=:status",array(':status'=>1)) ->queryAll(); print_r($user); ?>
OUTPUT SQL
SELECT `username`, `usertype` FROM `usermaster` WHERE (usertype=:id) OR (isactive=:status)
group, having
$user = Yii::app()->db->createCommand() ->select('user_id, user_name,user_type') ->from('tbl_user_commants') ->group('user_type') ->having(count(commants_id)>5) ->queryAll();
join
$user = Yii::app()->db->createCommand() ->join('tbl_user tu') ->select('user_id, tu.user_name,tu.user_type, post_id') ->from('tbl_user_commants') ->group('post_id') ->having(count(commants_id)>2) ->queryAll();
Properties and Type OF CDbCommand
distinct - boolean from - string group - string having - string join - string limit - string offset - string order - string params - array pdoStatement - PDOStatement select - string text - string union - mixed where - string