CodeIgniter学习笔记二：CI中的query_builder(AR)、连贯操作

一、开启query_builder

在applicationconfigdatabase.php中添加如下代码（默认已开启）：

$query_builder = TRUE;

二、查询数据

//get
$res = $this -> db -> get('test');
$list = $res -> result();
var_dump($list);
/*
array (size=7)
  0 => 
    object(stdClass)[18]
      public 'id' => string '1' (length=1)
      public 'name' => string 'lu' (length=5)
      public 'title' => string 'ci learn' (length=8)
  1 => 
    object(stdClass)[19]
      public 'id' => string '2' (length=1)
      public 'name' => string 'jim' (length=3)
      public 'title' => string 'jim learn ci' (length=12)
*/

三、插入数据

//insert
$data = array(
  'name' => 'mary',
  'title' => 'mary learn ci'
);
$bool = $this -> db -> insert('test', $data);
if ($bool) {
  //受影响行数
  echo $this -> db -> affected_rows();
  //自增id
  echo $this -> db -> insert_id();
}

四、更新数据

//update
$data = array(
    'name' => 'cilover',
    'title' => 'cilover learn ci'
);
//第1个参数是表名，第2个是更新后的数据，第3个是条件
$bool = $this -> db -> update('test', $data, array('id' => 1));
if ($bool) {
    //受影响行数
    echo $this -> db -> affected_rows();
}

五、删除数据

//delete
$bool = $this -> db -> delete('test', array('id'=>4));
if ($bool) {
    //受影响行数
    echo $this -> db -> affected_rows();
}

六、连贯操作

//链式操作
$res = $this -> db -> select('id,name')
             -> from('test')
             -> where('id >=', 1)
             -> limit(3, 2)//跳过2条取3条数据
             -> order_by('id desc')
             -> get();
var_dump($this -> db -> last_query());
//SELECT `id`, `name` FROM `ci_test` WHERE `id` >= 1 ORDER BY `id` desc  LIMIT 2, 3

var_dump($res -> result());
/*
array (size=3)
  0 =>
    object(stdClass)[17]
      public 'id' => string '12' (length=2)
      public 'name' => string 'mary' (length=4)
  1 =>
    object(stdClass)[16]
      public 'id' => string '11' (length=2)
      public 'name' => string 'mary' (length=4)
  2 =>
    object(stdClass)[28]
      public 'id' => string '10' (length=2)
      public 'name' => string 'mary' (length=4)
*/

特别要注意limit是反的，where中的id与>=之前有空格。

七、where

//where
$this -> db -> where('name', 'jim') -> get('test');
echo $this -> db -> last_query();
//SELECT * FROM `ci_test` WHERE `name` = 'jim'

$this -> db -> where('name !=', 'jim') -> get('test');
echo $this -> db -> last_query();
//SELECT * FROM `ci_test` WHERE `name` != 'jim'

$this -> db -> where(array('name' => 'jim', 'id >' => 2)) -> get('test');
echo $this -> db -> last_query();
//SELECT * FROM `ci_test` WHERE `name` = 'jim' AND `id` > 2

更复杂的查询可以用query实现。