| Status | Draft |
|---|---|
| Todo | escaping data, prepared statements when they're finished |
Querying a database
query()
$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');
last_query()
$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();
escape()
$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.
- [string] a value to escape.
UWAGA!
Jeśli metoda wykryje, że zmienna jest stringiem to automatycznie doda apostrofy. Należy więc budować zapytania SQL bez apostrofów, np:
$db = Database::instance(); $query = $db->Query('SELECT * FROM foo WHERE bar = '.$db->escape($baz));
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 www.yoursite.com/user/listusers 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> <ul> <li>John</li> <li>Michael</li> </ul>
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.
Query
applications/controllers/clients.php
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); } }
Template
applications/views/clients_content.php
<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>
