^Status|Draft|
^Todo|escaping data, prepared statements when they're finished|

**[[libraries:database|<< Back to Database Main Page]]**

====== 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 ==

<code php>
$query = $db->query('SELECT username FROM users');


</code>

==== last_query() ====

''$db->last_query($sql)'' returns a string containing the last run query.
== for example, in (?) applications/unspecified_path/unspecified_filename.php ==

<code php>
$last_query = $db->last_query();
</code>


==== 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:

<code php>
$db = Database::instance();
$query = $db->Query('SELECT * FROM foo WHERE bar = '.$db->escape($baz));
</code>

===== 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 (?) ==

<code 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>';

        

    }
}
</code>
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 ==

<code>
<h2>SELECT username FROM users</h2>
<ul>
<li>John</li>
<li>Michael</li>
</ul>
</code>


====== 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 ==

<code 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');
</code>

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:

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


====== 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 (?) ==

<code php>
$db = Database::instance();  

// or

$db = Database::instance('groupname');  // "default" is assumed if groupname is not given
</code>


=== Simple Query ===
== applications/unspecified_path/unspecified_filename.php (?) ==

<code php>
$result = $db->query('SELECT username,password,email FROM users');

foreach ($result as $row)
{
    echo $row->username;
    echo $row->password;
    echo $row->email;
}
</code>



===== Complete Example 2 =====

This demonstrates using the query results in a template.

=== Query ===
== applications/controllers/clients.php ==

<code 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);
    }
}
</code>

=== Template ===
== applications/views/clients_content.php ==

<code 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>
</code>



**[[libraries:database:builder|Continue to the next section: Database Query Builder >>]]**