Dbal.sql query limit

From phpBB Development Wiki

dbal::sql_query_limit –– Run an SQL Query with a LIMIT

1 Description
2 Parameters
3 Return Values
4 Examples
5 Notes
- 5.1 Caching the Query
- 5.2 Error Handling
6 See Also

Description

resource dbal::sql_query_limit ( string $query , int $total [, int $offset [, int $cache_ttl ]])

Performs a query against the database.

We do not add limit statements to the sql query, but instead use $db->sql_query_limit(). You basically pass the query, the total number of lines to retrieve and the offset.

Note: Since Oracle handles limits differently and because of how we implemented this handling you need to take special care if you use sql_query_limit with an sql query retrieving data from more than one table.

Make sure when using something like "SELECT x.*, y.jars" that there is not a column named jars in x; make sure that there is no overlap between an implicit column and the explicit columns.

Functionally, using this function is identical to calling dbal::sql_query() except that this function supports LIMIT clause.

Parameters

Parameter	Required/Default	Usage
query	Required	Contains the SQL query string which shall be executed
total	Required	The total number of runs you wish to return with this query. "row_count". e.g.: 10 has the same functionality as LIMIT 0,10 and will retrieve rows 1-10
offset	No - default 0	The offset / start of rows to query. e.g.: 5 has the same functionality as LIMIT 5,10 (offset 5, total 10) and will retrieve rows 6-15
cache_ttl	No - default 0	Either 0 to avoid caching or the time in seconds which the result shall be kept in cache

Return Values

$db->sql_query_limit() will return a result object or FALSE on failure.

The returned result resource should be passed to $db->sql_fetchrow(), $db->sql_fetchrowset() or other functions for dealing with result tables, to access the returned data.

Examples

Example #1

$post_id = 25;

// Start on row 10
$start = 10;

// Select 5 rows (limit/offset)
$limit = 5

$sql = 'SELECT *
    FROM ' . POSTS_TABLE . ' 
    WHERE post_id = ' . (int) $post_id;
$result = $db->sql_query_limit($sql, $limit, $start);

Example #2

$start = request_var('start', 0);

$sql = 'SELECT post_text
    FROM ' . POSTS_TABLE . '
    WHERE forum_id = ' . (int) $forum_id;
// return 100 rows
$result = $db->sql_query_limit($sql, 100, $start);

Example #3 Building a query with sql_build_query

$sql_array = array(
    'SELECT'    => 'f.*, ft.mark_time',
    'FROM'      => array(
        FORUMS_WATCH_TABLE  => 'fw',
        FORUMS_TABLE        => 'f'
    ),
    'LEFT_JOIN' => array(
        array(
            'FROM'  => array(FORUMS_TRACK_TABLE => 'ft'),
            'ON'    => 'ft.user_id = ' . $user->data['user_id'] . ' AND ft.forum_id = f.forum_id'
        )
    ),
    'WHERE'     => 'fw.user_id = ' . $user->data['user_id'] . '
        AND f.forum_id = fw.forum_id',
    'ORDER_BY'  => 'left_id'
);

$sql = $db->sql_build_query('SELECT', $sql_array);

// now run the query returning only the first 10 results
$result = $db->sql_query_limit($sql, 10);

Notes

Caching the Query

Note: You can cache the results of the query by utilising the fourth parameter of the $db->sql_query_limit method. You may define this as the time (In seconds) to cache the data returning from that query. You should only use this cache on queries that will be returning mostly static data. Do not cache the results from a query that are expected to be different each time the query runs.

Error Handling

Note: phpBB3 automatically handles SQL Exceptions and SQL Errors, therefore manual error handling for database queries is not needed in phpBB3, unlike phpBB2.

Development Wiki