View source

From phpBB Development Wiki

for Dbal.sql query limit
You do not have permission to edit this page, for the following reason:
The action you have requested is limited to users in the group: Users.
You can view and copy the source of this page:
dbal::sql_query_limit –– Run an SQL Query with a LIMIT

==Description==
#<pre>resource dbal::sql_query_limit ( string $query , int $total [, int $offset [, int $cache_ttl ]])</pre>
Performs a query against the database.<br>

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.<br>

<div id="preferences" style="background-color:#FFF;">'''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.</div>
<br>
Functionally, using this function is identical to calling [[dbal.sql_query|dbal::sql_query()]] except that this function supports LIMIT clause.

==Parameters==
{|border="1" cellspacing="0" cellpadding="3"
! 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
|}<br>

==Return Values==
$db->sql_query_limit() will return a result object or FALSE on failure.<br>

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

==Examples==
===Example #1===
<php>$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);</php><br>

===Example #2===
<php>$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);</php><br>

===Example #3 Building a query with [[dbal.sql_build_query|sql_build_query]]===
<php>$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);</php>

==Notes==
===Caching the Query===
: <div class="toc" style="padding:10px;">'''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.</div>


===Error Handling===
: <div class="toc" style="padding:10px;">'''Note''': phpBB3 automatically handles SQL Exceptions and SQL Errors, therefore manual error handling for database queries is not needed in phpBB3, unlike phpBB2.</div>

==See Also==
* [[dbal.sql_build_array]]
* [[dbal.sql_build_query]]
* [[dbal.sql_query]]
* [[Database Abstraction Layer]]
* [[dbal.sql_fetchrow]]
* [[dbal.sql_fetchrowset]]
* [[dbal.sql_fetchfield]]<br>
<br>
[[Category:Database]]
[[Category:dbal]]

[[ja:Dbal.sql_query_limit]]
Return to Dbal.sql query limit.
Retrieved from "http://wiki.phpbb.com/Dbal.sql_query_limit"