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

一、开启query_builder

在application\config\database.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实现。