This is documentation for Kohana v2.3.x.
| Status | Draft |
|---|---|
| Todo | escaping data, prepared statements when they're finished |
$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.
$query = $db->query('SELECT username FROM users');
$db→last_query($sql) returns a string containing the last run query.
$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.
In the following example we'll show how to query a database and retrieve all usernames from the users table.
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 www.yoursite.com/user/listusers you'll see a list of users with a heading of the query above it.
<h2>SELECT username FROM users</h2> <ul> <li>John</li> <li>Michael</li> </ul>
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.
$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.
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'));
This section contains some queries along with other supporting code you might need in your application.
$db = Database::instance(); // or $db = Database::instance('groupname'); // "default" is assumed if groupname is not given
$result = $db->query('SELECT username,password,email FROM users'); foreach ($result as $row) { echo $row->username; echo $row->password; echo $row->email; }
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'); View::factory('clients') ->bind('result', $result) ->render(TRUE); } }
<html> <head> <style> /* * 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; } </style> </head> <body> <h2>Client List</h2> <hr/> <table class="db"> <tr> <th>Client</th> <th>ID</th> </tr> <?php foreach( $result as $row ):?> <tr <?= text::alternate( '', ' class="odd"' ) ?>> <td><?= $row->name ?> </td> <td><?= $row->code ?> </td> </tr> <?php endforeach; ?> </table> </body>
