insert
$sql = "insert into {{user}} ('username','pass') values ('{$username}','{$pass}')";
yii::app()->db->createCommand($sql)->execute();
$model = new user();
$model->attributes=array(
'username'=>$_post['username'] 数据库的值和你接收到的值
)
若果表单名称和数据库相同的话
直接$model->attributes=$_post;
$model->save();
delete
$sql = "delete from {{user}} where id = $id";
yii::app()->db->createCommand($sql)->execute();
$model = user::model()->findByPk($id);
$model->delete();
update
$sql = "update {{user}} set username={$username}";
yii::app()->db->createCommand($sql)->execute();
$model = user::model()->findByPk($id);
$model-update();
$model = user::model()->findByPk($id);
$model->attibute=array(
'字段'=> 新值
)
$model->save();
select
$sql = "SELECT * FROM USER";
$data = Yii::app()->db->createCommand($sql)->queryAll();
如果分页的话
$sql2 = "SELECT count(*) FROM USER";
$count = Yii::app()->db->createCommand($sql2)->queryScalar();
或者
$count = yii::app()->db->createCommand($sql)->query()->rowCount;
$pager = new CPagination($count);
$pager->pageSize = 10;
$res = yii::app()->db_feiling->createCommand($sql . " LIMIT :offset,:limit");
$res->bindParam(':limit', $pager->limit);
$res->bindParam(':offset', $pager->offset);
//分页结束
$list = $res->queryAll();
二
$criteria = new CDbCriteria;
$criteria->condition = "com_id=".Yii::app()->user->com_id." AND user_id=".Yii::app()->user->user_id."";
$criteria->order = 'kuaihui_id DESC';
$criteria->select = '*';
$count = $model->count($criteria);
$pager = new CPagination($count);
$pager->pageSize = 10;
$pager->applyLimit($criteria);
$kuaihuis = $model->findAll($criteria);
$criteria->condition = "com_id=".Yii::app()->user->com_id." AND user_id=".Yii::app()->user->user_id."";
等同于
$criteria->condition = "com_id=:com_id AND user_id=:user_id";
$criteria->params=array(':com_id'=>$cid,':user_id'=>$uid);
调用单条可以用 queryRow执行sql
也可以
$criteria = new CDbCriteria;
$criteria->condition = "com_id=".Yii::app()->user->com_id." AND user_id=".Yii::app()->user->user_id."";
$criteria->order = 'kuaihui_id DESC';
$criteria->select = '*';
$kuaihuis = $model->find($criteria);
或者
$one = $model->find('com_id=:com_id AND dept_pid > :dept_pid',array(':com_id'=>$com_id,':dept_pid'=>0));
model 中的relations
两张表之间的关系无非三种:一对多;一对一;多对多; 在AR中,定义了四种关系:
关系 | 定义 | 例子 |
---|---|---|
BELONGS_TO | A和B的关系是一对多,那么B属于A | Post属于User |
HAS_MANY | A和B之间的关系是一对多,那么A有多个B | User有多个Post |
HAS_ONE | 这是HAS_MANY的一种特殊情况,A至多有一个B | User至多有一个Profile |
MANY_MANY | 这个对应多对多的情况,在AR里会将多对多以BELONGS_TO和HAS_MANY的组合来解释 | Post和Category |
在AR中通过重写CActiveRecord类的relations()方法来申明关系;这个方法返回一个关系配置的数组;一个数组无素代表一个单独的关系,格式如下:
'VarName'=>array('RelationType','ClassName','ForeignKey', ...additional options)
Var Name | 关系名 |
---|---|
Relation Type | 四种关系:self::BELONGS_TO, self::HAS_ONE, self::HAS_MANY, self::MANY_MANY |
Class Name | 代表当前AR类要关联的那个AR类名 |
Foreign Key | 实现关系的外键, 有可能有多个,即列名 |
例如现有一个表 train培训表 还有一个 train_people表
那么 Train表中的 relation为
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'people'=>array(self::HAS_MANY, 'TrainPeople', 'train_id'),
);
}
调用的时候
$lists = $model->with('people')->findAll();
当需要people表里面的数据的时候,可用foreach再次循环
foreach($list as $val){
foreach($val->people as $vals){
echo $vals->peopleo_name;
等等...
}
}
如果需要查询的时候调用people表中的条件 加上分页可
$model = new Train();
$model_user = new User();
$user_id = yii::app()->user->user_id;
$criteria = new CDbCriteria;
$criteria->condition = "people.train_user_id=".$user_id;
$criteria->order = 'add_time DESC';
$criteria->select = '*';
$count = $model->with('people')->count($criteria);
$pager = new CPagination($count);
$pager->pageSize = 10;
$pager->applyLimit($criteria);
$lists = $model->with('people')->findAll($criteria);
当两个表想链接主键外间的名称不一致的时候
可以使用 on 来关联
'user'=>array(self::HAS_ONE, 'UserPriv', '','on'=>'t.duties_user_id=user.user_id'),
查询小技巧
当两个表之间的设置relations时
a 与 b的关系为 HAS_ONE
而我们查询的时候恰好又需要 a 表 和 b表的条件可以这样查询
$list = User::model->with('priv')->findAll('duties_id=:id AND priv.setting_source=:tid',array(':id'=>$duties_id,':tid'=>'2'));