CodeIgniter active records offset without limit?

Question

I have two variables, $take (limit) and $skip (offset) which are the values for a limit clause in mysql.

Now Code Igniter does it's limit clauses backwards.

E.g.

$this->db->limit(5,10) would product LIMIT 10, 5 in MYSQL.

Anyway, I am having a hard time getting my head around how to call the limit function based on which of the two values are set.

Basically,

If they are both set I want to call

$this->db->limit($take, $skip)

If only $take is set I want to call

$this->db->limit($take)

But what do I call if only $skip is set, e.g. if$skip = 10` then I want to show all rows, except for the first 10.

Do I call

$this->db->limit(null, $skip) or
$this->db->limit(0, $skip) or
$this->db->limit(false, $skip) or
something completely different?

score 5 · Accepted Answer · answered Dec 05 '12 at 23:20

5

I think you found the answer you were looking for by answering our own question but if you want to use codeigniter to get all rows except for the first 10 then you would need to do something like (I have yet to test it):

$this->db->limit(18446744073709551615, 10)

According to Mysql Documentation

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter.

answered Dec 05 '12 at 23:20

Karan Ashar

1,392
1
10
23

Yeah, that is correct, I looked through the source code :) But, your answer works if you only want to use documented functions. – Hailwood Dec 06 '12 at 07:08
Haha. Yea using documented functions is so from the 2000's ;) . I kid. In all seriousness, maybe in the next CI version they remove access to this undocumented function. There is always that risk – Karan Ashar Dec 06 '12 at 16:51
3

Yeah, also, turns out the offset function is useless :/ The offset function sets the value, but, unless there is actually a limit then the offset is never applied. Contemplating submitting a patch... So, your answer gets the Tick, although I would probably replace `18446744073709551615` with `PHP_INT_MAX` as I reacon it removes the ambiguity of "what does that number mean?!?" for future developers, it is kind of implied that, we don't care what the number is, we just want a big one! – Hailwood Dec 08 '12 at 01:08

score 2 · Answer 2 · answered Dec 05 '12 at 23:14

Turns out Code Igniter has an undocumented offset function.

/**
* Sets the OFFSET value
*
* @param    integer the offset value
* @return   object
*/
public function offset($offset)
{
    $this->ar_offset = $offset;
    return $this;
}

So just calling $this->db->offset($skip) works fine!

score 0 · Answer 3 · edited Nov 11 '18 at 01:03

0

This is the official documentation to offset function in active record of codeigniter 3

offset($offset) Parameters: $offset (int) – Number of rows to skip Returns:
CI_DB_query_builder instance (method chaining)

Return type:
CI_DB_query_builder

Adds an OFFSET clause to a query.

edited Nov 11 '18 at 01:03

Antonino

3,178
3
24
39

answered Nov 10 '18 at 22:02

La Thortilla

31
2

CodeIgniter active records offset without limit?

3 Answers3