Select Record from database in yii
There are two way to write select query using create command in Yii framework :
Firstway :
$user = Yii::app()->db->createCommand('SELECT id, username, profile FROM tbl_user u join tbl_profile p ON u.id=p.user_id WHERE id = "'.$id.'"')->queryRow();
OR Second Way :
$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();
Normal Core Php Query :
Select id,username,profile from tbl_user u join tbl_profile p ON u.id=p.user_id where id= $id
Compare core php and yii select query :
Yii Framework Core Php
(1)- select('id, username, profile') -> Select id, username, profile
(2)- from('tbl_user u') -> tbl_user u
(3)- join('tbl_profile p', 'u.id=p.user_id') -> tbl_profile p ON u.id=p.user_id
(4)- where('id=:id', array(':id'=>$id)) -> id = $id
(5)- queryRow() -> mysql_fetch_row()
Query Description :
(1)- select() : select('id, username')
select('tbl_user.id, username as name')
select(array('id', 'username'))
select(array('id', 'count(*) as num'))
(2)- from() : from('tbl_user')
from('tbl_user u, public.tbl_profile p')
from(array('tbl_user', 'tbl_profile'))
from(array('tbl_user', '(select * from tbl_profile) p'))
(3)- join() : join('tbl_profile', 'user_id=id')
leftJoin('pub.tbl_profile p', 'p.user_id=id AND type=:type', array(':type'=>1))
(4)- where() : where('id=1 or id=2')
where('id=:id1 or id=:id2', array(':id1'=>1, ':id2'=>2))
where(array('or', 'id=1', 'id=2'))
where(array('and', 'id=1', array('or', 'type=2', 'type=3')))
where(array('in', 'id', array(1, 2))
where(array('not in', 'id', array(1,2)))
where(array('like', 'name', '%Qiang%'))
where(array('like', 'name', array('%Qiang', '%Xue')))
where(array('or like', 'name', array('%Qiang', '%Xue')))
where(array('not like', 'name', '%Qiang%'))
where(array('or not like', 'name', array('%Qiang%', '%Xue%')))
(5)- queryRow() : Return only single row from database in an array.
queryAll() : Return all matching rows from database in an array.
query() : Its only execute your query.Its not return anything.
(6)- order() : order('name, id desc')
order(array('tbl_profile.name', 'id desc'))
(7)- group() : group('name, id')
group(array('tbl_profile.name', 'id'))
(8)- having() : having('id=1 or id=2')
having(array('or', 'id=1', 'id=2'))
If you like this post please don't forgot to notify me to post a comment .
Happy Coding Guys :)
Tags:
Yii framework