What is a query offset?

Question

I saw this at the Kohana documentation:

$content = new View('pages/items');
$items = new Items_Model;

$content->items = $items->get_items($page_no, 10); // page to get starting at offset, number of items to get

As you can see, we can assume that we have get_items method for Items model that receives 2 parameters, $page_no and 10 (as shown here). I know that 10 is the number of items to get and $page_no is the page to get starting at offset

I can probably implement a limit sql statement for the 10 parameter, but what sql statement will correspond to $page_no? What does Kohana mean regarding "page to get starting at offset"

Answer 1

It corresponds to a LIMIT statement:

SELECT something FROM table LIMIT $limit OFFSET $offset;

//or alternatively
SELECT something FROM table LIMIT $offset,$limit;

In other words, select something from table, but only give me $limit records starting from record $offset.

$offset = ($page_no - 1) * $limit
_{$page_no is 1 based.}

More information in the MySQL documentation:

12.2.8 SELECT Syntax

DISCLAMER: $limit and $offset is used in this question only to aid understanding. Of course, you do not want to build a query without proper value escaping.