CodeIgniter 3.0 新手折腾笔记（三） --数据库QB（3月6日更新）

背景

ci 2.2.2中文手册中有关数据库的AR模式介绍

适配器类>数据库类>Active Record 模式

这个模式的解释是这样的：

这种模式是以较少的程序代码来实现信息在数据库中的获取，插入，更改。 有时只用一两行的代码就能完成对数据库的操作。

一个主要的优点是允许你创建独立的数据库应用程序，因为查询语法是由数据库的适配器来产生的。它可以进行更安全的查询，因为系统会自动的对所有的输入值进行转义。

比如你需要进行一条查询，从user表查询所有用户，使用原生SQL语句是这样的：

$query='SELECT * FROM user';

使用AR模式是这样：

$query = $this->db->get('user');

使用前提是在application/config/databaes.php中，49行

$active_record = TRUE;
/*The $active_record variables lets you determine whether or not to load
  the active record class*/

active_record项需要开启

---

跨版本更新日志

在3.0版本中，ar类改名为query builder class,改变有以下：

1. application/config/databaes.php中63行

$query_builder = TRUE;
/*The $query_builder variables lets you determine whether or not to load
  the query builder class.*/

同AR相比，这里仅仅是名称不同

1. 方法的改变，官方文档日志这么说的(本人翻译，如有错误请及时通知我)

• 给insert_batch()新增插入对象的能力.

• 添加新的返回查询SQL字符串的方法，但不执行他们 get_compiled_select(), get_compiled_insert(), get_compiled_update(), get_compiled_delete().

• 添加一个可选参数--允许禁用转义方法（用于自定义范围）join(), order_by(), where_in(), or_where_in(), where_not_in(), or_where_not_in(), insert(), insert_batch().

• 添加join()多个条件的支持.

• 添加join()的USING支持.

• 添加 where()的EXISTS支持.

• 添加order_by(seed, 'RANDOM')随机排序的种子值支持.

• 改变limit()忽略NULL值代替总是转为整型.

• 改变offset()忽略空值代替总是转为整型

• insert_batch()和update_batch()方法现在返回一个整数代表着所影响的行数.
  -where(), or_where(), having()和 or_having()方法现在将 = 和 <>, != SQL运算符 各自转换为 IS NULL 和IS NOT NULL当提交的比较值是NULL的时候

• 添加对reset_query(), start_cache(), stop_cache()和flush_cache()方法链接支持.

• 添加方法count_all_results()可选的第二个参数去禁用重置QB（query builder）的值

案例

###注意###
我已经在config/autoload.php中自动加载以下：

$autoload['libraries'] = array('database');
$autoload['helper'] = array('url');

---

使用QB类实现登陆验证，数据库是这样的

CREATE TABLE `ci_user` (
  `user_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `user_number` char(8) NOT NULL COMMENT '用户学号',
  `user_password` varchar(20) NOT NULL COMMENT '用户密码',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

视图表单文件可以这样写

<form action="<?=base_url('action/login_verify')?>" method="post" >
...
</form>

这里用到base_url原因是site_url()是url会加上入口文件不美观，我已经把index.php隐藏，会重定向至入口文件，可能这样使用姿势不对，慎学。

---

编写控制器文件，controllers/action.php

public function login_verify() {
        $this->load->model('room_action');//载入模型room_action
        $res = $this->room_action->login_verify();//使用login_verify方法
        if ($res > 0) {
            //成功，动作
        } else {
            //失败动作
        }
    }
/*
模型一旦被载入，你就能通过下面的方法使用它:
$this->load->model('Model_name');
$this->Model_name->function(); 
*/

---

编写模型文件，models/room_action.php

public function login_verify() {
        $username = $this->input->post('username');
        $password = $this->input->post('password');
        $array = array('user_number' => $username, 'user_password' => $password);
        $this->db->select('user_id');//SELECT user_id
        $query = $this->db->get_where('ci_user', $array);//FROM ci_user WHERE user_number=传来的用户名 AND user_password=传来的密码 
        return $query->num_rows();//返回当前请求的行数

    }

这里不使用过滤的原因的，使用QB类系统会自动的对所有的输入值进行转义，进行安全查询

It also allows for safer queries, since the values are escaped automatically by the system.

3-6 更新

个人使用经验

• 书写规范&逻辑

//如传入$username及$password,查询主键id
$sql = "SELECT id FROM user WHERE username = ? and passsword = ?";
$query = $this->db->query($sql,array($username,$password));
if($query->num_rows() > 0){ //处理查询结果前先判断数据是否在
    //****执行成功
    $id = $query->row()->id;
    //如果结果只有一行，row()返回对象，row_array()返回结果数组，按需选择
    //如果多行就用$query()->result_array()
}

• 查询辅助函数

1. $this->db->insert_id()//返回最新插入行的id,实用
2. $this->db->last_query(),//该方法返回上一次执行的查询语句,调试很有用

• 插入多条

//如数据如下
$data = array(
    array(
        'title' => 'My title',
        'name' => 'My Name',
        'date' => 'My date'
    ),
    array(
        'title' => 'Another title',
        'name' => 'Another Name',
        'date' => 'Another date'
    )
);
/*不要尝试进行如
    foreach($data as $key => $value){
        ...
        ...
        $this->db->insert('xxx',$someData)
    }
    100条数据就要执行100次，时间很长
*/
//批量插入函数insert_batch(),数据如上拼成理想数组格式
$this->db->insert_batch('xxx',$data);

• 更新多条

$data = array(
   array(
      'title' => 'My title' ,
      'name' => 'My Name 2' ,
      'date' => 'My date 2'
   ),
   array(
      'title' => 'Another title' ,
      'name' => 'Another Name 2' ,
      'date' => 'Another date 2'
   )
);

$this->db->update_batch('mytable', $data, 'title');
/*语句：
 UPDATE `mytable` SET `name` = CASE
 WHEN `title` = 'My title' THEN 'My Name 2'
 WHEN `title` = 'Another title' THEN 'Another Name 2'
 ELSE `name` END,
 `date` = CASE
 WHEN `title` = 'My title' THEN 'My date 2'
 WHEN `title` = 'Another title' THEN 'Another date 2'
 ELSE `date` END
 WHERE `title` IN ('My title','Another title')
*/

这里update_batch第三个参数是指定WHERE语句的键，追源码看第三个参数说明

   /**
     * Update_Batch
     *
     * Compiles an update string and runs the query
     *
     * @param    string    the table to retrieve the results from
     * @param    array    an associative array of update values
     * @param    string    the where key
     * @return    int    number of rows affected or FALSE on failure
     */
    public function update_batch($table = '', $set = NULL, $index = NULL)
    {}

参数为字符串类型，问题来了，更新语句where条件不止一个，怎么办，没看源码前对照文档简单传入array，果断报错，暂时没找到QB解决方法，但找到了update语句中case的用法解决

UPDATE `someTable` SET `需更新的字段` = 
 CASE
    WHEN `条件字段1` = xx  and `条件字段2`= yy 
    THEN '要设置的值'
    WHEN `条件字段1` = xx1  and `条件字段2`= yy1 
    THEN '要设置的值'
    WHEN `条件字段1` = xx2  and `条件字段2`= yy2 
    THEN '要设置的值'
 ELSE `需更新的字段` 
END
//测试foreach循环更新180条数据5s
//此方法180条数据0.078s

• 删除多条

/*同更新多条一样不可循环去处理删除
  如多条数据主键id都可得，循拼接成字符串
*/
 $string = '';//需先定义
foreach($someData as $key => $value){
  $string .= "'" . $value['id']. "'," ;
}
$string = rtrim($string,',');
$sql = "DELETE FROM xx WHERE id in ? ";
$this->db->query($sql,$string);