Quick reference – Yii inbuilt functions – DB related
\n—————————————————-<\/p>\n
###### Active Record functions: ############
\nDifferent ways to query
\n\/\/ 1st way
\n$criteria=new CDbCriteria;
\n$criteria->select=’title’; \/\/ only select the ‘title’ column
\n$criteria->condition=’postID=:postID’;
\n$criteria->params=array(‘:postID’=>10);
\n$post=Post::model()->find($criteria);<\/p>\n
\/\/ 2nd way
\n$post=Post::model()->find(array(
\n ‘select’=>’title’,
\n ‘condition’=>’postID=:postID’,
\n ‘params’=>array(‘:postID’=>10),
\n));<\/p>\n
\/\/Other ways
\n\/\/ Find All
\n\/\/ find all rows satisfying the specified condition
\n$posts=Post::model()->findAll($condition,$params);
\n\/\/ find all rows with the specified primary keys
\n$posts=Post::model()->findAllByPk($postIDs,$condition,$params);
\n\/\/ find all rows with the specified attribute values
\n$posts=Post::model()->findAllByAttributes($attributes,$condition,$params);
\n\/\/ find all rows using the specified SQL statement
\n$posts=Post::model()->findAllBySql($sql,$params);<\/p>\n
User::model()->findAll(‘first_name=? AND last_name=?’, array(‘Paul’, ‘Smith’));
\nUser::model()->findAllByAttributes(array(‘first_name’=>’Paul’, ‘last_name’=>’Smith’)); <\/p>\n
\/\/ find one
\n$post=Post::model()->find($condition,$params);
\n$post=Post::model()->find(‘postID=:postID’, array(‘:postID’=>10));<\/p>\n
\/\/ find by primary key
\n$post=Post::model()->findByPk($postID,$condition,$params);<\/p>\n
\/\/ find by attributes
\n$post=Post::model()->findByAttributes($attributes,$condition,$params);
\n$post=Post::model()->findByAttributes();<\/p>\n
\/\/ find by specified SQL statement
\n$post=Post::model()->findBySql($sql,$params);<\/p>\n
\/\/ count
\n$n=Post::model()->count($condition,$params);<\/p>\n
\/\/ CREATE : creating record
\n$post=new Post;
\n$post->title=’sample post’;
\n$post->content=’content for the sample post’;
\n$post->create_time=time(); \/\/ $post->create_time=new CDbExpression(‘NOW()’);
\n$post->save();<\/p>\n
\/\/ UPDATE: Updating Record
\n$post=Post::model()->findByPk(10);
\n$post->title=’new post title’;
\n$post->save(); \/\/ save the change to database<\/p>\n
\/\/ DELETE: Delete record
\n$post=Post::model()->findByPk(10); \/\/ assuming there is a post whose ID is 10
\n$post->delete();<\/p>\n
\/\/ Scopes
\nhow to make use of scopes
\nIn your model make a entry as below
\n\/\/ scope to get the 5 records of status=1 and recently updated
\npublic function scopes()
\n{
\n return array(
\n ‘published’=>array(
\n ‘condition’=>’status=1’,
\n ),
\n ‘recently’=>array(
\n ‘order’=>’create_time DESC’,
\n ‘limit’=>5,
\n ),
\n );
\n}<\/p>\n
And then use the below statement to query.
\n$posts=Post::model()->published()->recently()->findAll();<\/p>\n
\/\/ write in model class to to some ops before saving
\npublic function beforeSave() {
\n if ($this->isNewRecord)
\n $this->created = new CDbExpression(‘NOW()’);
\n else
\n $this->modified = new CDbExpression(‘NOW()’);
\n \/\/ anything else.. like, date modification, time modification, etc..<\/p>\n
return parent::beforeSave();
\n}<\/p>\n
######## Query builder functions: ############
\n\/\/ NEXT : sql command writing
\n$command = Yii::app()->db->createCommand(‘SELECT * FROM tbl_user’);<\/p>\n
\/\/ fetch row
\n$user = Yii::app()->db->createCommand()
\n ->select(‘id, username, profile’) \/\/ to fetch all use ->select(‘*’)
\n ->from(‘tbl_user’) \/\/ for multiple table use as ->from(array(‘tbl_user’, ‘tbl_profile’))
\n ->where(‘id=:id’, array(‘:id’=>$id))
\n ->queryRow();
\nOR
\n$row = Yii::app()->db->createCommand(array(
\n ‘select’ => array(‘id’, ‘username’),
\n ‘from’ => ‘tbl_user’,
\n ‘where’ => ‘id=:id’,
\n ‘params’ => array(‘:id’=>1),
\n))->queryRow();<\/p>\n
\/\/ Fetch row using join
\n$user = Yii::app()->db->createCommand()
\n ->select(‘id, username, profile’)
\n ->from(‘tbl_user u’)
\n ->join(‘tbl_profile p’, ‘u.id=p.user_id’) \/\/ join, leftJoin, rightJoin, crossJoin, naturalJoin
\n ->where(‘id=:id’, array(‘:id’=>$id))
\n ->queryRow();<\/p>\n
\/\/ fetch all
\n$user = Yii::app()->db->createCommand()
\n ->select(‘id, username, profile’)
\n ->from(‘tbl_user’)
\n ->where(‘id=:id’, array(‘:id’=>$id))
\n ->limit(10, 20) \/\/ limit 10 and offset 20
\n ->queryAll();<\/p>\n
$users = Yii::app()->db->createCommand()
\n ->select(‘*’)
\n ->from(‘tbl_user’)
\n ->queryAll(); <\/p>\n
\/\/ display SQL statement
\n$sql = Yii::app()->db->createCommand()
\n ->select(‘*’)
\n ->from(‘tbl_user’)
\n ->text; <\/p>\n
\/\/ INSERT
\n\/\/ build and execute the following SQL:
\n\/\/ INSERT INTO `tbl_user` (`name`, `email`) VALUES (:name, :email)
\n$command->insert(‘tbl_user’, array(
\n ‘name’=>’Tester’,
\n ’email’=>’tester@example.com’,
\n));<\/p>\n
\/\/ UPDATE `tbl_user` SET `name`=:name WHERE id=:id
\n$command->update(‘tbl_user’, array(
\n ‘name’=>’Tester’,
\n), ‘id=:id’, array(‘:id’=>1));<\/p>\n
\/\/ DELETE FROM `tbl_user` WHERE id=:id
\n$command->delete(‘tbl_user’, ‘id=:id’, array(‘:id’=>1));<\/p>\n
\/\/ NEXT
\n\/\/ create a new entry with time modification example
\n$post=new Post;
\n$post->title = “Some title”;
\n$post->desciption = “Some description”;
\n$post->create_time=new CDbExpression(‘NOW()’);
\n\/\/ $post->create_time=’NOW()’; will not work because
\n\/\/ ‘NOW()’ will be treated as a string
\n$post->save();<\/p>\n","protected":false},"excerpt":{"rendered":"
Quick reference – Yii inbuilt functions – DB related —————————————————- ###### Active Record functions: ############ Different ways to query \/\/ 1st way $criteria=new CDbCriteria; $criteria->select=’title’; \/\/ only select the ‘title’ column $criteria->condition=’postID=:postID’; $criteria->params=array(‘:postID’=>10); $post=Post::model()->find($criteria); \/\/ 2nd way $post=Post::model()->find(array( ‘select’=>’title’, ‘condition’=>’postID=:postID’, ‘params’=>array(‘:postID’=>10), )); \/\/Other ways \/\/ Find All \/\/ find all rows satisfying the specified condition … Continue reading Yii DB built in functions – Reference<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[],"class_list":["post-91","post","type-post","status-publish","format-standard","hentry","category-php"],"_links":{"self":[{"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/posts\/91","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=91"}],"version-history":[{"count":2,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/posts\/91\/revisions"}],"predecessor-version":[{"id":93,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/posts\/91\/revisions\/93"}],"wp:attachment":[{"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=91"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=91"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=91"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}