SELECT with LIMIT in Codeigniter

Question

I have a site develop in Codeigniter  and in my model I have a function like this   function nationList  limit null   start null        if   this- gt session- gt userdata  language     it        this- gt db- gt select  nation id  nation name it as name        if   this- gt session- gt userdata  language     en        this- gt db- gt select  nation id  nation name en as name         this- gt db- gt from  nation         this- gt db- gt order by  name    asc         this- gt db- gt limit  limit   start        query    this- gt db- gt get         nation   array        foreach   query- gt result   as  row          array push  nation   row        return  nation           And if into my controller I call the function without limit and start doesn t return result like this    data  nationlist      this- gt Nation model- gt nationList      Instead if I set limit and start works  If limit and start are null  Why doesn t return result  I don t want to make a second function or a control if limit and start are null  How can I solve this when limit and start are null without a control or a second function to make useful the code and more efficient

User · Accepted Answer

Try this    function nationList  limit null   start null        if   this- gt session- gt userdata  language       quot it quot              this- gt db- gt select  nation id  nation name it as name               if   this- gt session- gt userdata  language       quot en quot              this- gt db- gt select  nation id  nation name en as name                this- gt db- gt from  nation         this- gt db- gt order by  quot name quot    quot asc quot         if   limit        amp  amp   start                  this- gt db- gt limit  limit   start              query     this- gt db- gt get          nation   array        foreach   query- gt result   as  row            array push  nation   row              return  nation

User · Answer

I don t know what version of CI you were using back in 2013  but I am using CI3 and I just tested with two null parameters passed to limit   and there was no LIMIT or OFFSET in the rendered query  I checked by using get compiled select     This means that -- assuming your have correctly posted your coding attempt -- you don t need to change anything  or at least the old issue is no longer a CI issue   If this was my project  this is how I would write the method to return an indexed array of objects or an empty array if there are no qualifying rows in the result set  function nationList  limit   null   start   null           assuming the language value is sanitized validated whitelisted     return  this- gt db         - gt select  nation id  nation name      this- gt session- gt userdata  language       AS name           - gt from  nation           - gt order by  quot name quot           - gt limit  limit   start          - gt get           - gt result       These refinements remove unnecessary syntax  conditions  and the redundant loop  For reference  here is the CI core code         LIMIT        param   int  value  LIMIT value     param   int  offset OFFSET value     return  CI DB query builder     public function limit  value   offset   0        is null  value  OR  this- gt qb limit    int   value      empty  offset  OR  this- gt qb offset    int   offset       return  this     So the  this- gt qb limit and  this- gt qb offset class objects are not updated because null evaluates as true when fed to is null   or empty

User · Answer

For further visitors      Executes  SELECT   FROM mytable LIMIT 10 OFFSET 20    get   table         limit   NULL    offset   NULL      query    this- gt db- gt get  mytable   10  20       get where sample    query    this- gt db- gt get where  mytable   array  id    gt   id   10  20       Produces  LIMIT 10  this- gt db- gt limit 10         Produces  LIMIT 10 OFFSET 20    limit  value    offset   0    this- gt db- gt limit 10  20

[php] SELECT with LIMIT in Codeigniter

Examples related to php

Examples related to sql

Examples related to codeigniter