Yii常用执行SQL方法
======================================================
======================================================
//执行SQL:createCommand
query(); //select
queryAll();
queryRow();
queryScalar();
execute(); //delete insert update
//举例
$sql = "SELECT LEFT(region_code,2) as provinceid,region_name";
$sql .= " FROM `dim_luna_region`";
$sql .= " GROUP BY provinceid ORDER BY region_code asc";
$provinces = Yii::app()->db->createCommand($sql)->queryAll();
//举例
Yii::app()->db->createCommand()
->select('name, started_date, finished_date')
->from('customer c')
->leftJoin('accounting a', 'c.id=a.customerid')
->rightJoin('customer_employee ce', 'c.id=ce.customerid')
->join('app_info_list il', 'dl.AppId = il.Id')
->where('ce.employeeid=:id', array(':id'=>2))
->group('AppId')
->order('value desc')
->limit(10)
->queryRow();
//举例
$command = Yii::app()->db->createCommand();
$command->select('count(Id) as countApp, sum(Up) as totalUp');
$command->from('app_info_list');
$command->where('CommitUserId = :CommitUserId', array(':CommitUserId' => $memberID));
$result = $command->queryRow();
//举例???
$userId = Yii::app()->db->createCommand($selectSql)->queryScalar();
//举例???
Yii::app()->db->createCommand($updateSql)->execute();
//执行SQL:CDbCriteria
//举例
$criteria = new CDbCriteria;
$criteria->alias = 'a';
$criteria->select = 'name, started_date, finished_date';
$criteria->join = 'RIGHT JOIN customer_employee ON customer.id=customer_employee.customerid ';
$criteria->join .= 'LEFT JOIN accounting ON customer.id=accounting.customerid';
$criteria->group = ' GROUP BY a.PushId';
$criteria->condition = 'customer_employee.employeeid=:id';
$criteria->params = array(':id'=>2);
$customers = Customers::model()->find($criteria);
//执行SQL:model
find();
findAll();
findByPk();
findAllByAttributes();
count();
delete();
deleteAll();
//举例
$news = AppDownloadLog::model()->find('Hash = :hash', array(':hash' => $hash));
if (! $news instanceof AppDownloadLog) {
throw new THttpException('文章ID有误');
}
//举例
$models = AppInfoList::model()->findAll();
//举例
$yesterdayApps = AppInfoList::model()->findAll('CommitTime > "' . $yesterday . '" and CommitTime < "' . $today . '" and Status = 0');
//举例
$models_user = User::model()->findAllByAttributes(array('Status' => '-1'));
//举例
AppInfoList::model()->findByPk($id);
//举例
User::model()->count( "Status = 0 and CreateTime < '$yesterday' and LastLoginTime>='$yesterday'" );
//举例
$commentModel = AppPushListReviews::model()->findAll(
array(
'select' => array('Id', 'Sort', 'Up'), //可选，默认全部
'distinct' => true, //可选
'condition' => 'Status=0 and Used=0 and PushId=:PushId and Content !=""',
'params' => array(':PushId'=>$pushId),
'order' => 'Id desc', //'order' => new CDbExpression('RAND()'),
'limit' => 1
)
);
//举例
AppPushListDetail::model()->find('PushId=' . $row)->delete();
//举例
$deleteChildrenReplyNum = AppReviews::model()->deleteAll('Pid=:pid', array(':pid' => $reply->Id));
//扩展举例 published
$product = CoinExchangeProduct::model()->published()->findByPk($productID);
public function scopes()
{
return array(
'published' => array(
'condition' => 'status = 0'
)
);
}
//扩展举例 together
AppReviews::model()->with('author_icon')->together()->findAll(
array(
'select'=> array('Id', 'Pid', 'Content', 'UpdateTime', 'AuthorId', 'ToAuthorId'),
'order' => 't.Pid asc, t.UpdateTime desc',
'condition' => 'AppId = :AppId',
'params' => array(':AppId' => $appID)
)
);
adp代码学习
======================================================
======================================================
多语言。加载messages/[module]
Yii::t($this->getModule()->ID, $this->ID . '_' . $field)
权限控制
components/controller.php function filters(){}
components/WebUser.php $this->perm;登录的时候存入的
权限设置数据表
module_adm
Yii框架设置
======================================================
======================================================
//打开gii工具
后台打开 protected/config/main.php 搜索gii
访问gii http://localhost/shop/index.php?r=模块名字（gii）
//创建模块成功配置
位置：protectd/config/main.php
'modules'=>array(
'admin' // 模块名称
),
//默认控制器
前台默认控制器：SiteController.php
后台默认控制器：DefaultController.php
//controller位置
protected/components
//定义常量位置
protected/assets/default
引入已经定义好的系统常量 index.php
require_once(dirname(__FILE__).'/protected/config/constant.php');
//数据库
数据库配置：protected/config/database.php
注意：php.ini 开启扩展
extension=php_pdo_mysql.dll
extension=php_mysql.dll
检测是否连接上数据库：var_dump(Yii::app()->db);
framework/web/CWebApplication.php
framework/base/CApplication.php
famework/YiiBase.php
framework/db/CDbConnection.php
//全部代码
yii有10000行，全部在framework/yiilite.php 有体现
//表单小物件
$form = $this -> beginWidget('CActiveForm');
//form submit
注册：
给模型收集表单信息
foreach($_POST['User'] as $_k => $_v){
$user_model -> $_k = $_v;
}
//上边的foreach，在yii框架里边有优化，使用模型属性attributes来进行优化
//attributes 属性已经把foreach集成好了，我们可以直接使用
$user_model -> attributes = $_POST['User'];
input radio:(yiilist.php)
<?php echo $form->radioButtonList($user_model,'user_sex',$sex,array('separator'=>' ')); ?>
input select:
<?php echo $form -> dropDownList($user_model,'user_xueli',$xueli); ?>
input checkbox:
<?php echo $form -> checkBoxList($user_model,'user_hobby',$hobby,array('separator'=>' ')); ?>
表单显示错误信息：
<?php echo $form->textField($user_model,'username',array('class'=>'inputBg','id'=>'User_username')); ?>
<!--表单验证失败显示错误信息-->
<?php echo $form ->error($user_model,'username'); ?>
//验证：
framework/validators/cValidator.php
举例：
array('username','required','message'=>'用户名必填'),
//用户名不能重复(与数据库比较)
array('username', 'unique', 'message'=>'用户名已经占用'),
array('password','required','message'=>'密码必填'),
//验证确认密码password2 要与密码的信息一致
array('password2','compare','compareAttribute'=>'password','message'=>'两次密码必须一致'),
//邮箱默认不能为空
array('user_email','email','allowEmpty'=>false, 'message'=>'邮箱格式不正确'),
//验证qq号码(都是数字组成，5到12位之间，开始为非0信息，使用正则表达式验证)
array('user_qq','match','pattern'=>'/^[1-9]\d{4,11}$/','message'=>'qq格式不正确'),
//验证手机号码(都是数字，13开始，一共有11位)
array('user_tel','match','pattern'=>'/^1[3,4,5,6,7,8]{1}\d{9}$/','message'=>'手机号码格式不正确'),
//验证学历(信息在2、3、4、5之间则表示有选择，否则没有)，1正则；2范围限制
//范围限制
array('user_xueli','in','range'=>array(2,3,4,5),'message'=>'学历必须选择'),
//验证爱好：必选两项以上（自定义方法对爱好进行验证）
array('user_hobby','check_hobby'),
//为没有具体验证规则的属性，设置安全的验证规则，否则attributes不给接收信息
array('user_sex,user_introduce','safe'),
Yii调试
======================================================
======================================================
//让mysql抛出异常信息(js里面也是可以看到的)
new CDbExpression($sql);
//网站底部显示日志信息
位置：protectd/config/main.php
搜索：CWebLogRoute
array(
'class'=>'CWebLogRoute',
),
Yii常用内置方法：
======================================================
======================================================
//获取当前Yii版本
Yii::getVersion();
//获取当前域名
Yii::app()->request->hostInfo
//在控制器中获取控制器名:
$this->id;
$this->ID;
$name = $this->getId();
//在控制器beforeAction()回调函数中获取动作名
$name = $action->id;
//在控制器中获取modules名:
$this->getModule()->ID
//控制器中获取表名
$model->table;
//在视图中获取控制器名:
$name = Yii::app()->controller->id;
//在其他地方获取动作名:
$name = $this->getAction()->getId();
//在模型中获取
$this->getModule()->id
$this->Module->getId()
Yii控制器
======================================================
======================================================
//获取字段属性
$model_new_creative = new LunaCreative();
$attrs = $model_new_creative->attributeNames();
//验证网址
if(!Func::validateIsUseful($model->website)){
throw new THttpException('网址不可用！');
}
Yii定时任务
======================================================
======================================================
准备阶段：
要定时执行的PHP程序存放位置：trunk/protected/commands/***.php
执行阶段：
打开cmd，进入到相应目录，敲入命令yiic，即可看到所定制的任务计划
例如：D:\wamp\www\appgrub\trunk\protected>yiic
执行对应的文件
例如：D:\wamp\www\appgrub\trunk\protected>yiic report
此时已经默认执行了ReportCommand.php中的actionIndex()方法
如果要执行控制里面的其他方法actionshow()方法
例如：D:\wamp\www\appgrub\trunk\protected>yiic report show
如果要执行控制里面的其他方法actionshow($p1,$p2)方法
例如：D:\wamp\www\appgrub\trunk\protected>yiic report show --p1=*** --p2=***
执行成功
Yii Session
======================================================
======================================================
设置：Yii::app()->session['var']='value';
使用：Yii::app()->session['var'];
移除: unset(Yii::app()->session['var']);
最后，当用户退出登录(logout),你需要消除痕迹，可使用：
Yii::app()->session->clear() 移去所有session变量，然后，调用
Yii::app()->session->destroy() 移去存储在服务器端的数据。
