| ######################## |
| Generating Query Results |
| ######################## |
| |
| There are several ways to generate query results: |
| |
| .. contents:: |
| :local: |
| :depth: 2 |
| |
| ************* |
| Result Arrays |
| ************* |
| |
| result() |
| ======== |
| |
| This method returns the query result as an array of **objects**, or |
| **an empty array** on failure. Typically you'll use this in a foreach |
| loop, like this:: |
| |
| $query = $this->db->query("YOUR QUERY"); |
| |
| foreach ($query->result() as $row) |
| { |
| echo $row->title; |
| echo $row->name; |
| echo $row->body; |
| } |
| |
| The above method is an alias of result_object(). |
| |
| If you run queries that might **not** produce a result, you are |
| encouraged to test the result first:: |
| |
| $query = $this->db->query("YOUR QUERY"); |
| |
| if ($query->num_rows() > 0) |
| { |
| foreach ($query->result() as $row) |
| { |
| echo $row->title; |
| echo $row->name; |
| echo $row->body; |
| } |
| } |
| |
| You can also pass a string to result() which represents a class to |
| instantiate for each result object (note: this class must be loaded) |
| |
| :: |
| |
| $query = $this->db->query("SELECT * FROM users;"); |
| |
| foreach ($query->result('User') as $user) |
| { |
| echo $user->name; // call attributes |
| echo $user->reverse_name(); // or methods defined on the 'User' class |
| } |
| |
| result_array() |
| =============== |
| |
| This method returns the query result as a pure array, or an empty |
| array when no result is produced. Typically you'll use this in a foreach |
| loop, like this:: |
| |
| $query = $this->db->query("YOUR QUERY"); |
| |
| foreach ($query->result_array() as $row) |
| { |
| echo $row['title']; |
| echo $row['name']; |
| echo $row['body']; |
| } |
| |
| *********** |
| Result Rows |
| *********** |
| |
| row() |
| ===== |
| |
| This method returns a single result row. If your query has more than |
| one row, it returns only the first row. The result is returned as an |
| **object**. Here's a usage example:: |
| |
| $query = $this->db->query("YOUR QUERY"); |
| |
| if ($query->num_rows() > 0) |
| { |
| $row = $query->row(); |
| |
| echo $row->title; |
| echo $row->name; |
| echo $row->body; |
| } |
| |
| If you want a specific row returned you can submit the row number as a |
| digit in the first parameter:: |
| |
| $row = $query->row(5); |
| |
| You can also add a second String parameter, which is the name of a class |
| to instantiate the row with:: |
| |
| $query = $this->db->query("SELECT * FROM users LIMIT 1;"); |
| $query->row(0, 'User'); |
| |
| echo $row->name; // call attributes |
| echo $row->reverse_name(); // or methods defined on the 'User' class |
| |
| row_array() |
| =========== |
| |
| Identical to the above row() method, except it returns an array. |
| Example:: |
| |
| $query = $this->db->query("YOUR QUERY"); |
| |
| if ($query->num_rows() > 0) |
| { |
| $row = $query->row_array(); |
| |
| echo $row['title']; |
| echo $row['name']; |
| echo $row['body']; |
| } |
| |
| If you want a specific row returned you can submit the row number as a |
| digit in the first parameter:: |
| |
| $row = $query->row_array(5); |
| |
| In addition, you can walk forward/backwards/first/last through your |
| results using these variations: |
| |
| | **$row = $query->first_row()** |
| | **$row = $query->last_row()** |
| | **$row = $query->next_row()** |
| | **$row = $query->previous_row()** |
| |
| By default they return an object unless you put the word "array" in the |
| parameter: |
| |
| | **$row = $query->first_row('array')** |
| | **$row = $query->last_row('array')** |
| | **$row = $query->next_row('array')** |
| | **$row = $query->previous_row('array')** |
| |
| .. note:: all the methods above will load the whole result into memory |
| (prefetching) use unbuffered_row() for processing large result sets. |
| |
| unbuffered_row() |
| ================ |
| |
| This method returns a single result row without prefetching the whole |
| result in memory as ``row()`` does. If your query has more than one row, |
| it returns the current row and moves the internal data pointer ahead. |
| |
| :: |
| |
| $query = $this->db->query("YOUR QUERY"); |
| |
| while ($row = $query->unbuffered_row()) |
| { |
| echo $row->title; |
| echo $row->name; |
| echo $row->body; |
| } |
| |
| You can optionally pass 'object' (default) or 'array' in order to specify |
| the returned value's type:: |
| |
| $query->unbuffered_row(); // object |
| $query->unbuffered_row('object'); // object |
| $query->unbuffered_row('array'); // associative array |
| |
| ********************* |
| Result Helper Methods |
| ********************* |
| |
| **$query->num_rows()** |
| |
| The number of rows returned by the query. Note: In this example, $query |
| is the variable that the query result object is assigned to:: |
| |
| $query = $this->db->query('SELECT * FROM my_table'); |
| |
| echo $query->num_rows(); |
| |
| .. note:: |
| Not all database drivers have a native way of getting the total |
| number of rows for a result set. When this is the case, all of |
| the data is prefetched and count() is manually called on the |
| resulting array in order to achieve the same methodality. |
| |
| **$query->num_fields()** |
| |
| The number of FIELDS (columns) returned by the query. Make sure to call |
| the method using your query result object:: |
| |
| $query = $this->db->query('SELECT * FROM my_table'); |
| |
| echo $query->num_fields(); |
| |
| **$query->free_result()** |
| |
| It frees the memory associated with the result and deletes the result |
| resource ID. Normally PHP frees its memory automatically at the end of |
| script execution. However, if you are running a lot of queries in a |
| particular script you might want to free the result after each query |
| result has been generated in order to cut down on memory consumptions. |
| Example:: |
| |
| $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()** |
| |
| This method sets the internal pointer for the next result row to be |
| fetched. It is only useful in combination with ``unbuffered_row()``. |
| |
| It accepts a positive integer value, which defaults to 0 and returns |
| TRUE on success or FALSE on failure. |
| |
| :: |
| |
| $query = $this->db->query('SELECT `field_name` FROM `table_name`'); |
| $query->data_seek(5); // Skip the first 5 rows |
| $row = $query->unbuffered_row(); |
| |
| .. note:: Not all database drivers support this feature and will return FALSE. |
| Most notably - you won't be able to use it with PDO. |
| |
| *************** |
| Class Reference |
| *************** |
| |
| .. class:: CI_DB_result |
| |
| .. method:: custom_result_object($class_name) |
| |
| :param string $class_name: Class name for the results |
| :returns: Array of objects of type $class_name |
| :rtype: array of $class_name |
| |
| Return the query results as an array of the specified class. |
| |
| .. method:: custom_row_object($n, $type) |
| |
| :param int $n: Index of the results row to return |
| :param string $class_name: Class name for the results |
| :returns: Object of type $type |
| :rtype: $type |
| |
| Return a specific row from the query results as an object of |
| the specified class. |
| |
| .. method:: data_seek([$n = 0]) |
| |
| :param int $n: Index of the results row to be returned next |
| :returns: TRUE on success, FALSE otherwise |
| :rtype: bool |
| |
| Moves the internal results row pointer to the desired offset. |
| Usage: see `Result Helper Methods`_. |
| |
| .. method:: field_data() |
| |
| :returns: Array of objects containing field meta-data. |
| :rtype: array |
| |
| Generates an array of objects containing field meta-data. |
| |
| .. method:: first_row([$type = 'object']) |
| |
| :param string $type: Type of result requested - array, object, or class name |
| :returns: First row of result set |
| :rtype: mixed |
| |
| Returns the "first" row, as an array, generic object, or |
| object of a specific class |
| |
| .. method:: free_result() |
| |
| :rtype: void |
| |
| Free the result. |
| Usage: see `Result Helper Methods`_. |
| |
| .. method:: last_row([$type = 'object']) |
| |
| :param string $type: Type of result requested - array, object, or class name |
| :returns: Last row of result set |
| :rtype: mixed |
| |
| Returns the "last" row, as an array, generic object, or |
| object of a specific class |
| |
| .. method:: list_fields() |
| |
| :returns: Array of column names |
| :rtype: array |
| |
| Fetch Field Names |
| |
| .. method:: next_row([$type = 'object']) |
| |
| :param string $type: Type of result requested - array, object, or class name |
| :returns: "Next" row of result set, NULL if there isn't one |
| :rtype: mixed |
| |
| Returns the "next" row, as an array, generic object, or |
| object of a specific class |
| |
| .. method:: num_fields() |
| |
| :returns: Number of fields in the result set |
| :rtype: integer |
| |
| Number of fields in the result set. |
| Usage: see `Result Helper Methods`_. |
| |
| .. method:: num_rows() |
| |
| :returns: Number of rows in the result set |
| :rtype: integer |
| |
| Number of rows in the result set. |
| Usage: see `Result Helper Methods`_. |
| |
| .. method:: previous_row([$type = 'object']) |
| |
| :param string $type: Type of result requested - array, object, or class name |
| :returns: "Previous" row of result set, NULL if there isn't one |
| :rtype: mixed |
| |
| Returns the "previous" row, as an array, generic object, or |
| object of a specific class |
| |
| .. method:: result([$type = 'object']) |
| |
| :param string $type: Type of result requested - array, object, or class name |
| :returns: Query results as the specified type |
| :rtype: mixed |
| |
| Query result. Acts as a wrapper function for the result_array, |
| result_object and custom_result_object methods. |
| Usage: see `Result Arrays`_. |
| |
| .. method:: result_array() |
| |
| :returns: Query results as an associative array |
| :rtype: array |
| |
| Returns the query results as an array of rows, where each |
| row is itself an associative array. |
| Usage: see `Result Arrays`_. |
| |
| .. method:: result_object() |
| |
| :returns: Query results as an array of objects |
| :rtype: array |
| |
| Returns the query results as an array of rows, where each |
| row is an object |
| |
| .. method:: row([$n = 0[, $type = 'object']]) |
| |
| :param integer $n: Index of the query results row to be returned |
| :param string $type: Type of result requested - array, object, or class name |
| :returns: Requested row of result set |
| :rtype: mixed |
| |
| Wrapper for result_row_array, result_row_object, and |
| custom_row_object. |
| Usage: see `Result Rows`_. |
| |
| .. method:: row_array([$n = 0]) |
| |
| :param integer $n: Index of the query results row to be returned |
| :returns: Requested row of result set |
| :rtype: array |
| |
| Returns requested result row as an associative array. |
| Usage: see `Result Rows`_. |
| |
| .. method:: row_object([$n = 0]) |
| |
| :param integer $n: Index of the query results row to be returned |
| :returns: Requested row of result set |
| :rtype: object |
| |
| Returns requested result row as an object |
| |
| .. method:: set_row($key[, $value = NULL]) |
| |
| :param mixed $key: Column index or array of key/value pairs |
| :param mixed $value: Result to assign to a column if the key is an index |
| :rtype: void |
| |
| Assigns an item into a particular column slot |
| |
| .. method:: unbuffered_row([$type = 'object']) |
| |
| :param string $type: Type of result requested - array, object, or class name |
| :returns: Requested row of result set |
| :rtype: mixed |
| |
| Fetches the next result row and returns it in the requested |
| form. |
| Usage: see `Result Rows`_. |