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

Execute

Once you have a query object built, either through a prepared statement or through the builder, you must then execute() the query and retrieve the results. Depending on the query type used, the results returned will vary.

Select

DB::select will return a Database_Result object which you can then iterate over. This example shows how you can iterate through the Database_Result using a foreach.

?

$results = DB::select()->from('users')->where('verified', '=', 0)->execute();
foreach($results as $user)
{
    // Send reminder email to $user['email']
    echo $user['email']." needs to verify his/her account\n";
}

Select - as_object() and as_assoc()

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: