我用的sql:
select sum(if(starttime>‘09:00:00’,1,0)) as late,
sum(if(endtime<‘18:00:00’,1,0)) as early
from present where userid=115 and date between ‘2009-12-1’ and ‘2009-12-31’
我用的sql:
select sum(if(starttime>‘09:00:00’,1,0)) as late,
sum(if(endtime<‘18:00:00’,1,0)) as early
from present where userid=115 and date between ‘2009-12-1’ and ‘2009-12-31’
$db = Yii::app()->db; //you have to define db connection in config/main.php
$sql = "select sum(if(starttime>'09:00:00',1,0)) as late,
sum(if(endtime<'18:00:00',1,0)) as early
from present where userid=:userid and date between :date_start and :date_end"
$results = $db->createCommand($sql)->query(array(
':userid' => 115,':date_start'=>'2009-12-1',':date_end'=>'2009-12-31',
));
foreach($results as $result){
echo $result['late']," and ",$result['early']," \n";
}
如果觉得不适应还可以用下面的方式:
$sql = "select sum(if(starttime>'09:00:00',1,0)) as late,
sum(if(endtime<'18:00:00',1,0)) as early
from present where userid=115 and date between '2009-12-1' and '2009-12-31'"
$results = $db->createCommand($sql)->query();
foreach($results as $result){
echo $result['late']," and ",$result['early']," \n";
}
就是不绑定参数,不过还是建议采用楼上的方式,防止SQL注入。
$db = Yii::app()->db;
$sql ="select url_info.id,url_info.url_name,url_info.cid,GROUP_CONCAT(tags_info.tag_name) AS tag_ids,url_info.status,category.category_name from url_info,category,tags_info where 1 ".$condition." group by url_info.id order by url_info.id desc limit 0,50;";
$results = $db->createCommand($sql)->query($params);
foreach($results as $result){
$dataProvider=$result;
}