生成查询结果

有几种不同方法可以生成查询结果：

结果数组

result() 方法

该方法以对象数组形式返回查询结果，如果查询失败返回空数组。一般情况下，你会像下面这样在一个 foreach 循环中使用它:

  1. $query = $this->db->query("YOUR QUERY");
  2.  
  3. foreach ($query->result() as $row)
  4. {
  5.     echo $row->title;
  6.     echo $row->name;
  7.     echo $row->body;
  8. }

该方法是 result_object() 方法的别名。

你还可以传一个字符串参数给 result() 方法，这个字符串参数代表你想要把每个结果转换成某个类的类名（这个类必须已经加载）

  1. $query = $this->db->query("SELECT * FROM users;");
  2.  
  3. foreach ($query->result('User') as $user)
  4. {
  5.     echo $user->name; // access attributes
  6.     echo $user->reverse_name(); // or methods defined on the 'User' class
  7. }

result_array() 方法

这个方法以 一个纯粹的数组 形式返回查询结果，如果无结果，则返回一个空数组。一般情况下，你会像下面这样在一个 foreach 循环中使用它:

  1. $query = $this->db->query("YOUR QUERY");
  2.  
  3. foreach ($query->result_array() as $row)
  4. {
  5.     echo $row['title'];
  6.     echo $row['name'];
  7.     echo $row['body'];
  8. }

结果行

row() 方法

这个方法返回单独一行结果。如果你的查询不止一行结果，它只返回第一行。返回的结果是 对象 形式，这里是用例:

  1. $query = $this->db->query("YOUR QUERY");
  2.  
  3. $row = $query->row();
  4.  
  5. if (isset($row))
  6. {
  7.     echo $row->title;
  8.     echo $row->name;
  9.     echo $row->body;
  10. }

如果你要返回特定行的数据，你可以将行号作为第一个参数传给这个方法:

  1. $row = $query->row(5);

你还可以加上第二个参数，该参数为字符串类型，代表你想要把结果转换成某个类的类名:

  1. $query = $this->db->query("SELECT * FROM users LIMIT 1;");
  2. $row = $query->row(0, 'User');
  3.  
  4. echo $row->name; // access attributes
  5. echo $row->reverse_name(); // or methods defined on the 'User' class

row_array() 方法

这个方法除了返回结果是一个数组而不是一个对象之外，其他的和上面的 row() 方法完全一样。举例:

  1. $query = $this->db->query("YOUR QUERY");
  2.  
  3. $row = $query->row_array();
  4.  
  5. if (isset($row))
  6. {
  7.     echo $row['title'];
  8.     echo $row['name'];
  9.     echo $row['body'];
  10. }

如果你要返回特定行的数据，你可以将行号作为第一个参数传给这个方法:

  1. $row = $query->row_array(5);

另外，你可以使用下面这些方法从你的结果集中获取前一个、后一个、第一个或者最后一个结果：

$row = $query->first_row()
$row = $query->last_row()
$row = $query->next_row()
$row = $query->previous_row()

这些方法默认返回对象，如果需要返回数组形式，将单词 "array" 作为参数传入方法即可：

$row = $query->first_row('array')
$row = $query->last_row('array')
$row = $query->next_row('array')
$row = $query->previous_row('array')

注解

上面所有的这些方法都会把所有的结果加载到内存里（预读取），当处理大结果集时最好使用 unbuffered_row() 方法。

unbuffered_row() 方法

这个方法和 row() 方法一样返回单独一行结果，但是它不会预读取所有的结果数据到内存中。如果你的查询结果不止一行，它将返回当前一行，并通过内部实现的指针来移动到下一行。

  1. $query = $this->db->query("YOUR QUERY");
  2.  
  3. while ($row = $query->unbuffered_row())
  4. {
  5.     echo $row->title;
  6.     echo $row->name;
  7.     echo $row->body;
  8. }

为了指定返回值的类型，可以传一个字符串参数 'object'（默认值） 或者 'array' 给这个方法:

  1. $query->unbuffered_row();       // object
  2. $query->unbuffered_row('object');   // object
  3. $query->unbuffered_row('array');    // associative array

自定义结果对象

You can have the results returned as an instance of a custom class insteadof a stdClass or array, as the result() and result_array()methods allow. This requires that the class is already loaded into memory.The object will have all values returned from the database set as properties.If these have been declared and are non-public then you should provide a__set() method to allow them to be set.

Example:

  1. class User {
  2.  
  3.     public $id;
  4.     public $email;
  5.     public $username;
  6.  
  7.     protected $last_login;
  8.  
  9.     public function last_login($format)
  10.     {
  11.         return $this->last_login->format($format);
  12.     }
  13.  
  14.     public function __set($name, $value)
  15.     {
  16.         if ($name === 'last_login')
  17.         {
  18.             $this->last_login = DateTime::createFromFormat('U', $value);
  19.         }
  20.     }
  21.  
  22.     public function __get($name)
  23.     {
  24.         if (isset($this->$name))
  25.         {
  26.             return $this->$name;
  27.         }
  28.     }
  29. }

In addition to the two methods listed below, the following methods also cantake a class name to return the results as: first_row(), last_row(),next_row(), and previous_row().

custom_result_object()

Returns the entire result set as an array of instances of the class requested.The only parameter is the name of the class to instantiate.

Example:

  1. $query = $this->db->query("YOUR QUERY");
  2.  
  3. $rows = $query->custom_result_object('User');
  4.  
  5. foreach ($rows as $row)
  6. {
  7.     echo $row->id;
  8.     echo $row->email;
  9.     echo $row->last_login('Y-m-d');
  10. }

custom_row_object()

Returns a single row from your query results. The first parameter is the rownumber of the results. The second parameter is the class name to instantiate.

Example:

  1. $query = $this->db->query("YOUR QUERY");
  2.  
  3. $row = $query->custom_row_object(0, 'User');
  4.  
  5. if (isset($row))
  6. {
  7.     echo $row->email;   // access attributes
  8.     echo $row->last_login('Y-m-d');   // access class methods
  9. }

You can also use the row() method in exactly the same way.

Example:

  1. $row = $query->custom_row_object(0, 'User');

结果辅助方法

num_rows() 方法

该方法返回查询结果的行数。注意：在这个例子中，$query 变量为查询结果对象:

  1. $query = $this->db->query('SELECT * FROM my_table');
  2.  
  3. echo $query->num_rows();

注解

并不是所有的数据库驱动器都有原生的方法来获取查询结果的总行数。当遇到这种情况时，所有的数据会被预读取到内存中，并调用 count() 函数来取得总行数。

num_fields() 方法

该方法返回查询结果的字段数（列数）。在你的查询结果对象上调用该方法:

  1. $query = $this->db->query('SELECT * FROM my_table');
  2.  
  3. echo $query->num_fields();

free_result() 方法

该方法释放掉查询结果所占的内存，并删除结果的资源标识。通常来说，PHP 会在脚本执行结束后自动释放内存。但是，如果你在某个脚本中执行大量的查询，你可能需要在每次查询之后释放掉查询结果，以此来降低内存消耗。

举例:

  1. $query = $this->db->query('SELECT title FROM my_table');
  2.  
  3. foreach ($query->result() as $row)
  4. {
  5.     echo $row->title;
  6. }
  7.  
  8. $query->free_result();  // The $query result object will no longer be available
  9.  
  10. $query2 = $this->db->query('SELECT name FROM some_table');
  11.  
  12. $row = $query2->row();
  13. echo $row->name;
  14. $query2->free_result(); // The $query2 result object will no longer be available

data_seek() 方法

这个方法用来设置下一个结果行的内部指针，它只有在和 unbuffered_row() 方法一起使用才有效果。

它接受一个正整数参数（默认值为0）表示想要读取的下一行，返回值为 TRUE 或 FALSE 表示成功或失败。

  1. $query = $this->db->query('SELECT `field_name` FROM `table_name`');
  2. $query->data_seek(5); // Skip the first 5 rows
  3. $row = $query->unbuffered_row();

注解

并不是所有的数据库驱动器都支持这一特性，调用这个方法将会返回 FALSE，例如你无法在 PDO 上使用它。

Class Reference

  • _class _CI_DB_result
    • result([$type = 'object'])

参数:

  1. - **$type** (_string_) -- Type of requested results - array, object, or class name返回:

Array containing the fetched rows返回类型:array

A wrapper for the result_array(), result_object()and custom_result_object() methods.

Usage: see 结果数组.

  • result_array()

返回:Array containing the fetched rows返回类型:array

Returns the query results as an array of rows, where eachrow is itself an associative array.

Usage: see 结果数组.

  • result_object()

返回:Array containing the fetched rows返回类型:array

Returns the query results as an array of rows, where eachrow is an object of type stdClass.

Usage: see 结果数组.

  • customresult_object($classname)

参数:

  1. - **$class_name** (_string_) -- Class name for the resulting rows返回:

Array containing the fetched rows返回类型:array

Returns the query results as an array of rows, where eachrow is an instance of the specified class.

  • row([$n = 0[, $type = 'object']])

参数:

  1. - **$n** (_int_) -- Index of the query results row to be returned
  2. - **$type** (_string_) -- Type of the requested result - array, object, or class name返回:

The requested row or NULL if it doesn't exist返回类型:mixed

A wrapper for the row_array(), row_object()and``custom_row_object() methods.

Usage: see 结果行.

  • unbufferedrow([$type = 'object'_])

参数:

  1. - **$type** (_string_) -- Type of the requested result - array, object, or class name返回:

Next row from the result set or NULL if it doesn't exist返回类型:mixed

Fetches the next result row and returns it in therequested form.

Usage: see 结果行.

  • rowarray([$n = 0_])

参数:

  1. - **$n** (_int_) -- Index of the query results row to be returned返回:

The requested row or NULL if it doesn't exist返回类型:array

Returns the requested result row as an associative array.

Usage: see 结果行.

  • rowobject([$n = 0_])

参数:

  1. - **$n** (_int_) -- Index of the query results row to be returned:returns: The requested row or NULL if it doesn't exist返回类型:

stdClass

Returns the requested result row as an object of typestdClass.

Usage: see 结果行.

  • customrow_object($n, $type_)

参数:

  1. - **$n** (_int_) -- Index of the results row to return
  2. - **$class_name** (_string_) -- Class name for the resulting row返回:

The requested row or NULL if it doesn't exist返回类型:$type

Returns the requested result row as an instance of therequested class.

  • dataseek([$n = 0_])

参数:

  1. - **$n** (_int_) -- Index of the results row to be returned next返回:

TRUE on success, FALSE on failure返回类型:bool

Moves the internal results row pointer to the desired offset.

Usage: see 结果辅助方法.

  • setrow($key[, $value = NULL_])

参数:

  1. - **$key** (_mixed_) -- Column name or array of key/value pairs
  2. - **$value** (_mixed_) -- Value to assign to the column, $key is a single field name返回类型:

void

Assigns a value to a particular column.

  • nextrow([$type = 'object'_])

参数:

  1. - **$type** (_string_) -- Type of the requested result - array, object, or class name返回:

Next row of result set, or NULL if it doesn't exist返回类型:mixed

Returns the next row from the result set.

  • previousrow([$type = 'object'_])

参数:

  1. - **$type** (_string_) -- Type of the requested result - array, object, or class name返回:

Previous row of result set, or NULL if it doesn't exist返回类型:mixed

Returns the previous row from the result set.

  • firstrow([$type = 'object'_])

参数:

  1. - **$type** (_string_) -- Type of the requested result - array, object, or class name返回:

First row of result set, or NULL if it doesn't exist返回类型:mixed

Returns the first row from the result set.

  • lastrow([$type = 'object'_])

参数:

  1. - **$type** (_string_) -- Type of the requested result - array, object, or class name返回:

Last row of result set, or NULL if it doesn't exist返回类型:mixed

Returns the last row from the result set.

  • num_rows()

返回:Number of rows in the result set返回类型:int

Returns the number of rows in the result set.

Usage: see 结果辅助方法.

  • num_fields()

返回:Number of fields in the result set返回类型:int

Returns the number of fields in the result set.

Usage: see 结果辅助方法.

  • field_data()

返回:Array containing field meta-data返回类型:array

Generates an array of stdClass objects containingfield meta-data.

  • free_result()

返回类型:void

Frees a result set.

Usage: see 结果辅助方法.

  • list_fields()

返回:Array of column names返回类型:array

Returns an array containing the field names in theresult set.