Dbal.sql query limit

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

Description
Performs a query against the database.
 * 1) resource dbal::sql_query_limit ( string $query, int $total [, int $offset [, int $cache_ttl ]])

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.

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.

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);

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.