Database - Examples

Here are some "real world" examples of using the database library to construct your queries and use the results.

Examples of Prepared Statements

TODO: 4-6 examples of prepared statements of varying complexity, including a good bind() example.

Pagination and search/filter

In this example, we loop through an array of whitelisted input fields and for each allowed non-empty value we add it to the search query. We make a clone of the query and then execute that query to count the total number of results. The count is then passed to the Pagination class to determine the search offset. The last few lines search with Pagination's items_per_page and offset values to return a page of results based on the current page the user is on.

Database - Queries - Query Builder 4

Database Functions

Eventually you will probably run into a situation where you need to call COUNT or some other database function within your query. The query builder supports these functions in two ways. The first is by using quotes within aliases:

?

$query = DB::select(array('COUNT("username")', 'total_users'))->from('users');

This looks almost exactly the same as a standard AS alias, but note how the column name is wrapped in double quotes. Any time a double-quoted value appears inside of a column name, only the part inside the double quotes will be escaped. This query would generate the following SQL:

Database - Queries - Query Builder 2

echo Kohana::debug((string) $query);
// Should display:
// SELECT `username`, `password` FROM `users` WHERE `username` = 'john'

Notice how the column and table names are automatically escaped, as well as the values? This is one of the key benefits of using the query builder.

Select - AS (column aliases)

It is also possible to create AS aliases when selecting, by passing an array as each parameter to DB::select

Database - Queries - Query Builder 1

Creating queries dynamically using objects and methods allows queries to be written very quickly in an agnostic way. Query building also adds identifier (table and column name) quoting, as well as value quoting.

At this time, it is not possible to combine query building with prepared statements.

Database - Making Queries - Prepared Statements

Using prepared statements allows you to write SQL queries manually while still escaping the query values automatically to prevent SQL injection. Creating a query is simple:

?

$query = DB::query(Database::SELECT, 'SELECT * FROM users WHERE username = :user');

The DB::query method is just a shortcut that creates a new Database_Query class for us, to allow method chaining. The query contains a :user parameter, which we will get to in a second.