生成查询结果

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

结果数组

result() 方法

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

$query = $this->db->query("YOUR QUERY");

foreach ($query->result() as $row)
{
    echo $row->title;
    echo $row->name;
    echo $row->body;
}

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

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

$query = $this->db->query("SELECT * FROM users;");

foreach ($query->result('User') as $user)
{
    echo $user->name; // access attributes
    echo $user->reverse_name(); // or methods defined on the 'User' class
}

result_array() 方法

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

$query = $this->db->query("YOUR QUERY");

foreach ($query->result_array() as $row)
{
    echo $row['title'];
    echo $row['name'];
    echo $row['body'];
}

结果行

row() 方法

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

$query = $this->db->query("YOUR QUERY");

$row = $query->row();

if (isset($row))
{
    echo $row->title;
    echo $row->name;
    echo $row->body;
}

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

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

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

$query = $this->db->query("SELECT * FROM users LIMIT 1;");
$row = $query->row(0, 'User');

echo $row->name; // access attributes
echo $row->reverse_name(); // or methods defined on the 'User' class

row_array() 方法

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

$query = $this->db->query("YOUR QUERY");

$row = $query->row_array();

if (isset($row))
{
    echo $row['title'];
    echo $row['name'];
    echo $row['body'];
}

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

$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() 方法一样返回单独一行结果,但是它不会预读取所有的结果数据到内存中。 如果你的查询结果不止一行,它将返回当前一行,并通过内部实现的指针来移动到下一行。

$query = $this->db->query("YOUR QUERY");

while ($row = $query->unbuffered_row())
{
    echo $row->title;
    echo $row->name;
    echo $row->body;
}

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

$query->unbuffered_row();       // object
$query->unbuffered_row('object');   // object
$query->unbuffered_row('array');    // associative array

自定义结果对象

You can have the results returned as an instance of a custom class instead of a stdClass or array, as the result() andresult_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:

class User {

    public $id;
    public $email;
    public $username;

    protected $last_login;

    public function last_login($format)
    {
        return $this->last_login->format($format);
    }

    public function __set($name, $value)
    {
        if ($name === 'last_login')
        {
            $this->last_login = DateTime::createFromFormat('U', $value);
        }
    }

    public function __get($name)
    {
        if (isset($this->$name))
        {
            return $this->$name;
        }
    }
}

In addition to the two methods listed below, the following methods also can take 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:

$query = $this->db->query("YOUR QUERY");

$rows = $query->custom_result_object('User');

foreach ($rows as $row)
{
    echo $row->id;
    echo $row->email;
    echo $row->last_login('Y-m-d');
}

custom_row_object()

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

Example:

$query = $this->db->query("YOUR QUERY");

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

if (isset($row))
{
    echo $row->email;   // access attributes
    echo $row->last_login('Y-m-d');   // access class methods
}

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

Example:

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

结果辅助方法

num_rows() 方法

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

$query = $this->db->query('SELECT * FROM my_table');

echo $query->num_rows();

注解

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

num_fields() 方法

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

$query = $this->db->query('SELECT * FROM my_table');

echo $query->num_fields();

free_result() 方法

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

举例:

$query = $this->db->query('SELECT title FROM my_table');

foreach ($query->result() as $row)
{
    echo $row->title;
}

$query->free_result();  // The $query result object will no longer be available

$query2 = $this->db->query('SELECT name FROM some_table');

$row = $query2->row();
echo $row->name;
$query2->free_result(); // The $query2 result object will no longer be available

data_seek() 方法

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

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

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

注解

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

Class Reference

classCI_DB_result
result([$type = 'object'])
参数:
  • $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 each row 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 each row is an object of type stdClass.

Usage: see 结果数组.

custom_result_object($class_name)
参数:
  • $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 each row is an instance of the specified class.

row([$n = 0[$type = 'object']])
参数:
  • $n (int) -- Index of the query results row to be returned
  • $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 结果行.

unbuffered_row([$type = 'object'])
参数:
  • $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 the requested form.

Usage: see 结果行.

row_array([$n = 0])
参数:
  • $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 结果行.

row_object([$n = 0])
参数:
  • $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 type stdClass.

Usage: see 结果行.

custom_row_object($n$type)
参数:
  • $n (int) -- Index of the results row to return
  • $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 the requested class.

data_seek([$n = 0])
参数:
  • $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 结果辅助方法.

set_row($key[$value = NULL])
参数:
  • $key (mixed) -- Column name or array of key/value pairs
  • $value (mixed) -- Value to assign to the column, $key is a single field name
返回类型:

void

Assigns a value to a particular column.

next_row([$type = 'object'])
参数:
  • $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.

previous_row([$type = 'object'])
参数:
  • $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.

first_row([$type = 'object'])
参数:
  • $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.

last_row([$type = 'object'])
参数:
  • $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 containing field 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 the result set.