This is documentation for Kohana v2.3.x. For v3.x documentation, see .

Table of Contents
Todoescaping data, prepared statements when they're finished

<< Back to Database Main Page

Querying a database


$db→query($sql) carries out the sql given. Will also connect to the database if it wasn't connected before. Returns a result object. Does not escape table names or anything.

for example, in (?) applications/unspecified_path/unspecified_filename.php
$query = $db->query('SELECT username FROM users');


$db→last_query($sql) returns a string containing the last run query.

for example, in (?) applications/unspecified_path/unspecified_filename.php
$last_query = $db->last_query();


$db→escape( $value ) returns a string which is the escaped version of the $value. This escaped string is suitable (and safe) to be used in an SQL statement.

Basic Example

In the following example we'll show how to query a database and retrieve all usernames from the users table.

for example, in (?) applications/controllers/users.php (?)
class User_Controller extends Controller {
    public function listusers(){
        $db=new Database;
        $result= $db->query('SELECT username FROM users');
        echo '<h2>'.$db->last_query().'</h2>';
        echo '<ul>';
        foreach($result as $row)
            echo '<li>'.$row->username.'</li>';
        echo '</ul>';

Now if you enter you'll see a list of users with a heading of the query above it.

for example, in (?) applications/unspecified_path/unspecified_filename.php
<h2>SELECT username FROM users</h2>

Query Binding

The database library has support for query binding. It allows you to create custom built queries and have the library escape your input values for you.

for example, in (?) applications/unspecified_path/unspecified_filename.php
$query = $db->query('SELECT `username` FROM `users` where `id` = ?', array(12));
// OR
$query = $db->query('SELECT `username` FROM `users` where `id` = ? and `foo` = ?', 12, 'bar');

In addition you can use the Query Builder portion of the database library to create database agnostic access.

After you perform your query, you get a Query Result object back.

Database Expression

In case you need to do a where or join (or other) clause to be taken literally, you can use a database expression. For example:

$query = $db->set('number', new Database_Expression('number+1'));

Complete Examples

This section contains some queries along with other supporting code you might need in your application.

Complete Example 1

Initializing the database

applications/unspecified_path/unspecified_filename.php (?)
$db = Database::instance();
// or
$db = Database::instance('groupname');  // "default" is assumed if groupname is not given

Simple Query

applications/unspecified_path/unspecified_filename.php (?)
$result = $db->query('SELECT username,password,email FROM users');
foreach ($result as $row)
    echo $row->username;
    echo $row->password;
    echo $row->email;

Complete Example 2

This demonstrates using the query results in a template.


class Clients_Controller extends Controller {
  public function index()
    $db = Database::instance();
    $result = $db->query('SELECT name, code FROM clients');
    ->bind('result', $result)


 * Zebra rows: When CSS3 is done we could simply use:
 *   tr :nth-child(odd) { background-color: #D0D0D0; }
 * but for now we use PHP and CSS
table.db tr { background-color: #F0F0F0; }
table.db tr.odd { background-color: #D0D0D0; }
table.db th { color: #f0f0f0; background-color: #303030; }
<h2>Client List</h2>
<table class="db">
<?php foreach( $result as $row ):?>
<tr <?= text::alternate( '', ' class="odd"' ) ?>>
	<td><?= $row->name ?> </td>
	<td><?= $row->code ?> </td>
<?php endforeach; ?>

Continue to the next section: Database Query Builder >>

libraries/database/query.txt · Last modified: 2010/06/20 17:51 by jordanlev