基于Yii的PDO工具类以及Yii的常用操作

工具类：

<?php/** * webservice 公共数据库类 * @author YunDuan */final class YD_MysqlUtil {/** *  $rowCount=$command->execute();   // 执行无查询 SQL $dataReader=$command->query();   // 执行一个 SQL 查询 $rows=$command->queryAll();      // 查询并返回结果中的所有行 $row=$command->queryRow();       // 查询并返回结果中的第一行 $column=$command->queryColumn(); // 查询并返回结果中的第一列 $value=$command->queryScalar();  // 查询并返回结果中第一行的第一个字段 // 执行SQL中，一般都需要绑定一些用户参数，对于用户参数，需要防止SQL注入攻击 // PDO对象的绑定参数的方法可以防止SQL注入攻击，同样扩展自PDO的DAO也有这样的功能 // 举例说明： // 第一，建立一个连接： $connection = Yii::app()->db; // 第二，写下无敌的SQL语句，比如： $sql="INSERT INTO tbl_user (username, email) VALUES(:username,:email)"; // 第三，创建CDbCommand对象用于执行SQL $command=$connection->createCommand($sql); // 接下来，将SQL语句中的形式参数，替换为实际参数 $command->bindParam(":username",$username,PDO::PARAM_STR);   // 这与PDO有点不同，PDO中不带冒号 $command->bindParam(":email",$email,PDO::PARAM_STR);    // 同样 // 最后，执行 $command->execute(); // 如果还有其他的数据需要插入，可以再次绑定实参。 * * <?php Execute a prepared statement by passing an array of insert values $calories = 150; $colour = 'red'; $sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour'); $sth->execute(array(':calories' => $calories, ':colour' => $colour)); ?> * // 使用CDbDataReader对象的bindColumn()方法将结果集中的列绑定到PHP变量。 // 因此，读取一行记录，列值将自动填充到对应的PHP对象中 // 比如这样： $connection = Yii::app()->db; $sql = "SELECT username, email FROM tbl_user"; $dataReader = $connection->createCommand($sql)->query();    //很赞的方法链, 可惜不能接着.each() $dataReader->bindColumn(1, $username);  //第一列值绑定到$username $dataReader->bindColumn(2, $email);     //第二列值绑定到$email //接着循环读取并操作数据 while( $dataReader->read() !== false ) { ... // 与先前的 while(($row=$dataReader->read())!==false) 有所不同哦！ } */public static function YD_insertInto($table, $data = array()){$keys = '';$values = '';foreach ($data as $key => $value) {if ($value !== ''){$keys .= $key.','; $values .=":".$key.", ";}}$keys = substr($keys, 0, -1);$values = substr($values, 0, -2);$sql = "INSERT INTO $table ($keys) VALUES($values)";return self::YD_execute($sql, $data);}public static function YD_updateSet($table, $data = array(), $where = ''){$set = '';foreach ($data as $key => $value) if ($value !== '') $set .= $key." = :".$key.", ";$set = substr($set, 0, -2);$sql = "UPDATE $table SET $set ";$sql .= empty($where) ? '' : ' WHERE '.$where;return self::YD_execute($sql, $data);}/** * 直接获取 一个字段的值 * @param unknown $sql * @param unknown $param  参数数组   $sth->execute(array(':calories' => $calories, ':colour' => $colour)); * @return unknown */public static function YD_getOne($sql, $data = ''){$sql = trim($sql);if (stripos($sql, 'limit 1') === false) $sql = $sql.' LIMIT 1';return self::YD_parseSql($sql, $data)->queryScalar();}public static function YD_execute($sql, $data = ''){$sql = trim($sql);return self::YD_parseSql($sql, $data)->execute();}public static function YD_getRow($sql, $data = ''){$sql = trim($sql);if (stripos($sql, 'limit 1') === false) $sql = $sql.' LIMIT 1';return self::YD_parseSql($sql, $data)->queryRow();}public static function YD_getColumn($sql, $data = ''){$sql = trim($sql);return self::YD_parseSql($sql, $data)->queryColumn();}public static function YD_getAll($sql, $data = ''){$sql = trim($sql);return self::YD_parseSql($sql, $data)->queryAll();}public static function YD_transaction($sql=array()) {$connection=Yii::app()->db;$transaction=$connection->beginTransaction();try {foreach ($sql as $value) $connection->createCommand($value)->execute();$transaction->commit();} catch(Exception $e) {// 如果有一条查询失败，则会抛出异常$transaction->rollBack();}}public static function YD_parseSql($sql, $data = '') {$connection = Yii::app()->db;$command = $connection->createCommand($sql);if($data !== '' && count($data) > 0) foreach ($data as $key=>&$value) if($value !== '') $command->bindParam(":$key",$value,PDO::PARAM_STR);return $command;}public static function page($count_sql_or_countNum, $findAll_sql, $size=20, $page_list=array(0,2,3,4,5,6,7) , $data = array()) {$total = is_numeric($count_sql_or_countNum) ? $count_sql_or_countNum : YD_MysqlUtil::YD_getOne($count_sql_or_countNum, $data);//获取总条数if (is_array($total)) $total = count($total);if ($total <= 0) return array('data'=>array(), 'page_list'=>'');$pages = new Pagination($total,$size);$sql = $findAll_sql.' '.$pages->limit;$lists_infos = YD_MysqlUtil::YD_getAll($sql, $data);$page_list = $pages->fpage($page_list);return array('data'=>$lists_infos, 'page_list'=>$page_list);}}

yii常用操作：

$this->id 获取当前控制器 || Yii::app()->getController()->id;$this->action->id 获取当前Action || Yii::app()->getController()->getAction()->id;获取ip地址:Yii::app()->request->userHostAddress;yii判断提交方式: Yii::app()->request->isPostRequest;得到当前域名: Yii::app()->request->hostInfo;获得上一页的url以返回: Yii::app()->request->urlReferrer;得到当前url: Yii::app()->request->url; 得到当前home url: Yii::app()->homeUrl;得到当前return url: Yii::app()->user->returnUrl;得到上一页urlReferer: Yii::app()->request->urlReferrer;

设置session过期时间: 'components'=>array( 'session'=>array( 'timeout'=>3600, ),)

附上一张Yii CActiveForm函数表：