Database Query Result

Provides methods for handling database query results. There are several ways to do this.

It is possible to iterate through the rows in a result set using a foreach loop.

$res = $this->db->query("SELECT name, email FROM users");
 
foreach ($res as $row)
{
   echo "<p>{$row->name} = {$row->email}</p>";
}

By default, the rows will be returned in object format, but they can be returned in array format with the $query→result() method.

You can also retrieve an individual row:

$res = $this->db->query("SELECT name, email FROM users");
 
$row = $res[2];
echo "<p>{$row->name} = {$row->email}</p>";
// third record

$query→result() is available automatically with default values when you execute a database query with $this→db→get() or $this→db→query(). As such, it is generally not needed to call this function unless you want to change the result set for your configuration defaults.

If you do need to change your defaults, simply run $query→result() with the following parameters:

• The first parameter is to use Objects (TRUE) or arrays (FALSE) in your result set.
• The second parameter is what type of object/array to use: For arrays, you can use MYSQL_ASSOC, MYSQL_NUM, or MYSQL_BOTH. For objects, you can specify a class name, and the library will create the specified object, if it exists, otherwise it will create stdObjects.

$query = $this->db->query("SELECT `first_name`, `last_name`, `age` FROM `users`");
 
foreach ($query as $row)
{
   echo $row->first_name;
   echo $row->last_name;
   echo $row->age;
}
 
$query->result(FALSE);
 
foreach ($query as $row)
{
   echo $row['first_name'];
   echo $row['last_name'];
   echo $row['age'];
}

class CircleObject
{
	function area()
	{
		return $this->radius * $this->radius * 3.14;
	}
}
 
----
 
$query = $this->db->query("SELECT `radius` FROM `circles`");
$query->result(TRUE, 'CircleObject');
foreach ($query as $row)
{
   echo '<p>'.$row->area().'</p>';
}

Note that the preferred way to iterate through result sets is with the result object. This is not an array, but an object with an internal pointer to return the current row. If you need a physical array of results, you can use the following method:

$query→result_array() the query result is returned as an array of results. You can then loop through them.

The parameters are the same as result().

$query = $this->db->query("SELECT `first_name`, `last_name`, `age` FROM `users`");
 
foreach ($query->result_array(FALSE) as $row)
{
   echo $row['first_name'];
   echo $row['last_name'];
   echo $row['age'];
}

$query→insert_id() returns the id of an INSERT statement.

$query = $this->db->query("INSERT");
 
echo $query->insert_id();
// 15

$query→count() counts the number of results from a query.

$query = $this->db->query("SELECT * FROM table");
 
echo $query->count();
// 12

If the query does not return a result set (e.g. UPDATE and DELETE queries), the number of affected rows is returned.

$query = $this->db->query("DELETE FROM table WHERE id = 3");
 
echo $query->count();
// 1

You can also manually move the result pointer around in the result object with the following chainable iterator methods.

Moves the result pointer ahead one.

Moves the result pointer back one.

Moves the result pointer to the beginning.

Determine if the current pointer location is a valid result pointer.

Returns the row of the current pointer position.

$query = $this->db->select('title')->from($table)->get();
echo 'First:'.Kohana::debug($query->current()).'<br />';
 
$query->next();
echo 'Second:'.Kohana::debug($query->current()).'<br />';
 
$query->next();
echo 'Third:'.Kohana::debug($query->current());
 
echo '<h3>And we can reset it to the beginning:</h3>';
$query->rewind();
echo 'Rewound:'.Kohana::debug($query->current());
 
// Chain methods
echo $query->next()->next()->next()->current()->title;

The above example will print out single rows according to where the result pointer is located.

Continue to the next section: Database Metadata >>