970_90

Monday, 7 November 2016

ThinkPHP SQL queries with examples

ThinkPHP SQL queries with examples

Question: How to create Model Object?
$userObj= D("Common/Users");



Question: How to add simple AND Query?
$map=array();
$userObj= D("Common/Users");
$map['user_type'] = 2;
$map['city_id'] = 10;
 $lists = $userObj
                ->alias("u")                
                ->where($map)
                ->order("u.id DESC")
                ->select();



Question: How to add simple OR Query?
$map=array();
$userObj= D("Common/Users");
$map['u.username|u.email'] = [email protected]';  //username OR email is [email protected]
 $lists = $userObj
                ->alias("u")                
                ->where($map)
                ->order("u.id DESC")
                ->select();



Question: How to use =, >, < in Query?
$map['id']  = array('eq',1000); //equal to 1000

$map['id']  = array('neq',1000); //Not equal to 1000

$map['id']  = array('gt',1000);//Greater than 1000

$map['id']  = array('egt',1000);//Greater than OR EQual 1000

$map['id']  = array('between','1,8'); //Between 1-8




Question: How to use like Query?
$map=array();
$userObj= D("Common/Users");
$map['name'] = array('like','test%'); //like test%
 $lists = $userObj
                ->alias("u")                
                ->where($map)
                ->order("u.id DESC")
                ->select();



Question: How to use like Query with NOT?
$map=array();
$userObj= D("Common/Users");
$map['b'] =array('notlike',array('%test%','%tp'),'AND'); //Not like %test% and %tp
 $lists = $userObj
                ->alias("u")                
                ->where($map)
                ->order("u.id DESC")
                ->select();



Question: How to use Inner JOIN ?
$map=array();
$userObj= D("Common/Users");
$map['name'] = array('like','test%'); //like test%
 $lists = $userObj
                ->alias("u") 
                ->join(C('DB_PREFIX') . "profile as p ON u.id = p.uid")               
                ->where($map)
                ->order("u.id DESC")
                ->select();



Question: How to use LEFT JOIN ?
$map=array();
$userObj= D("Common/Users");
$map['name'] = array('like','test%'); //like test%
 $lists = $userObj
                ->alias("u") 
                ->join('LEFT JOIN '.C('DB_PREFIX') . "profile as p ON u.id = p.uid")               
                ->where($map)
                ->order("u.id DESC")
                ->select();



Question: How to use RIGHT JOIN ?
$map=array();
$userObj= D("Common/Users");
$map['name'] = array('like','test%'); //like test%
 $lists = $userObj
                ->alias("u") 
                ->join('RIGHT JOIN '.C('DB_PREFIX') . "profile as p ON u.id = p.uid")               
                ->where($map)
                ->order("u.id DESC")
                ->select();



Question: How to use group by and having?
$map=array();
$userObj= D("Common/Users");
$map['name'] = array('like','test%'); //like test%
 $lists = $userObj
                ->alias("u") 
                ->join('RIGHT JOIN '.C('DB_PREFIX') . "profile as p ON u.id = p.uid")               
                ->where($map)
                ->order("u.id DESC")
                 ->group('u.id')                
                 ->having('count(p.id) >0')
                ->select();



Question: How to use count(for total records)?
$map=array();
$userObj= D("Common/Users");
$map['name'] = array('like','test%'); //like test%
 $count = $userObj
                ->alias("u")                 
                ->where($map)                
                ->count();



No comments:

Post a Comment