yii1的笔记

$sql = 'SELECT * FROM to8to_worker_item limit 10';
$res = Yii::app()->db->createCommand($sql)->queryAll();

这是yii1中写sql语句来查询数据库的写法，queryRow（）方法只查一条，查询结果是数组，无需toArray（）也不可以调用这个方法；




$cri = new CDbCriteria();
$cri->addCondition('id = 1','AND');//where id = 1 第二个参数默认省略，如果是OR关系时就起作用了
$cri->addInCondition('id',array(1,2,3));//where id in (1 ,2, 3)
$cri->addNotInCondition('id',array(1,2,3));//where id not in (1 ,2 ,3)
$cri->addSearchCondition('name','zhang');//模糊搜索 where name like '%zhang%'
$cri->addBetweenCondition('id',1,5);//where id between 1 and 5

$cri->compare('id',1);//where id = 1
$cri->compare('id',array(1,2));//where id in (1,2)

$cri->addCondition('id = :id');//占位符的使用方法
$cri->params[':id']=1;

$cri->select= 'id,name,age';//查询字段 select id name age,不重写默认是 select *
$cri->jion = 'left jion books on books.id = students.id ';//链表查询
$cri->distinct = FALSE; //是否唯一查询，默认false

student::model()->findAll($cri);

这是yii1中用CDbCriteria对象来收集查询条件的写法，查询结果是对象

$rows = (new yiidbQuery())
->select(['id','name'])
->from('students')
->where(['age'=>10])
->limit(10)
->all();

$rows = Yii::app()->db->createCommand()
->select(['id','name'])
->from('students')
->where(['age'=>10])
->limit(10)
->all();

query builder和Command现都支持链式调用

mysql要先排序在限制条数，一边情况下limit是最后加上去

new CDbCriteria()->compare($column,$value,$partialMatch=false,$operator='AND');
//search时$column匹配$value,false为完全匹配，true为模糊匹配，$operator为操作符

事务的操作
$shiwu = Yii::app()->db->beginTransaction();
try {
$update['coupons_enddate'] = 0;
$update['coupons_money'] = 0;
$res = Yii::app()->db->createCommand()->update('{{owner}}', $update, "coupons_money>0 and coupons_enddate <$timestamp");
$a = Yii::app()->db->createCommand($sql)->execute();
if($a && $res)
{
$shiwu->commit();
echo "<script>alert('成功清除现金券过期用户数据{$num}条')</script>";
Yii::app()->end();
}else
{
throw new Exception('修改不成功');
}
} catch (Exception $exc) {
echo "<script>alert('没找到需要清除的数据或清除失败')</script>";
$shiwu->rollback();
Yii::app()->end();
}